Subject: Re: Index (primary key) corrupt?

From: Adrian Klaver <[email-masked]>
Date: Fri, 19 Sep 2025 06:04:04 -0700

On 9/19/25 02:40, Wim Rouquart wrote:
> Internal
> 
>>> Internal
>>>
>>>
>>> Hello,
>>>
>>> When doing a pg_dump of one of our databases one of the tables primary
>>> keys doesn’t get exported. Pg_dump just skips this index, without any
>>> warning whatsoever (verbose mode was used to doublecheck).
> 
>> What is the complete table definition?
> 
> CREATE TABLE bcf_work_type (
>          id bigserial NOT NULL,
>          aml_score int8 NOT NULL,
>          CONSTRAINT idx_376814_primary PRIMARY KEY (id)
> );

The table was defined in one step using the above definition?

Or was the the PK added later?

What is the result for the query?:

select * from pg_index where indexrelid  = 'idx_376814_primary'::regclass;

How is the table populated with data?

> 
>> What is the complete pg_dump command being given?
> 
> pg_dump --verbose -p <xxx> -U <xxx> -d <xxx> > dumpverbose.sql 2> dumpverbose.log
> 
>> Is the PK definition in the pg_dump file? For plain text format can you grep/find it?
> 
> It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is.
> 
>> How is the dump file being restored?
> 
> As the code to generate the index is not in the dumpfile this seems irrelevant to me.
> 

-- 
Adrian Klaver
[email-masked]

---

From: Wim Rouquart <[email-masked]>
Date: Fri, 19 Sep 2025 13:23:08 +0000

Internal

>>>> Hello,
>>>>
>>>> When doing a pg_dump of one of our databases one of the tables
>>>> primary keys doesn’t get exported. Pg_dump just skips this index,
>>>> without any warning whatsoever (verbose mode was used to doublecheck).
>>
>>          CONSTRAINT idx_376814_primary PRIMARY KEY (id) );

>The table was defined in one step using the above definition? Or was the the PK added later?

I'm not the developer of the application, can't really answer these ones I'm afraid.

> select * from pg_index where indexrelid  = 'idx_376814_primary'::regclass;

Name               |Value  |
-------------------+-------+
indexrelid         |2006873|
indrelid           |1998823|
indnatts           |1      |
indnkeyatts        |1      |
indisunique        |true   |
indnullsnotdistinct|false  |
indisprimary       |true   |
indisexclusion     |false  |
indimmediate       |true   |
indisclustered     |false  |
indisvalid         |true   |
indcheckxmin       |false  |
indisready         |true   |
indislive          |true   |
indisreplident     |false  |
indkey             |{}     |
indcollation       |{}     |
indclass           |{}     |
indoption          |{}     |
indexprs           |       |
indpred            |       |

> How is the table populated with data?

Same answer as above.

>> As the code to generate the index is not in the dumpfile this seems irrelevant to me.

Disclaimer <https://www.kbc.com/KBCmailDisclaimer>

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 19 Sep 2025 08:50:51 -0700

>> select * from pg_index where indexrelid  = 'idx_376814_primary'::regclass;

Hmm, when I do the above on 15.14 I get:

-[ RECORD 1 ]-------+-------
indexrelid          | 242209
indrelid            | 242205
indnatts            | 1
indnkeyatts         | 1
indisunique         | t
indnullsnotdistinct | f
indisprimary        | t
indisexclusion      | f
indimmediate        | t
indisclustered      | f
indisvalid          | t
indcheckxmin        | f
indisready          | t
indislive           | t
indisreplident      | f
indkey              | 1
indcollation        | 0
indclass            | 3124
indoption           | 0
indexprs            | NULL
indpred             | NULL

What is full(15.x) version of Postgres are you using?

Is it the community version or a fork or SaaS?

What do you get for queries below?:

select * from pg_opclass where oid = 3124;

select * from pg_opclass where opcname = 'int8_ops';

>> How is the table populated with data?

Might be a good idea to find out.

Per a comment from Ron, does this lack of export happen every time you 
dump the table?

---

From: Wim Rouquart <[email-masked]>
Date: Fri, 10 Oct 2025 12:28:28 +0000

Internal

Hi,

Apologies for the late response, had other fish to fry...

> What is full(15.x) version of Postgres are you using?

15.14

>Is it the community version or a fork or SaaS?

Standard release indeed, running on RHAT8

>select * from pg_opclass where oid = 3124;

|oid    |opcmethod      |opcname        |opcnamespace|opcowner  |opcfamily      |opcintype      |opcdefault     |opckeytype
|3124   |403            |int8_ops       |11             |10                     |1976           |20                     |true           |0

>select * from pg_opclass where opcname = 'int8_ops';

|oid    |opcmethod      |opcname        |opcnamespace   |opcowner       |opcfamily      |opcintype      |opcdefault     |opckeytype|
|3124   |403            |int8_ops       |11                     |10                     |1976           |20                     |true           |0         |
|10021  |405            |int8_ops       |11                     |10                     |1977           |20                     |true           |0         |

>>Might be a good idea to find out.

Don't know, and apart from that, there will be no data added anymore to this table, seems this db will remain static (it's only a few rows btw)

> Per a comment from Ron, does this lack of export happen every time you dump the table?

Well, yes, until I do the rebuild and then the issue is fixed.

Per a comment from Ron, does this lack of export happen every time you dump the table?

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 10 Oct 2025 08:03:04 -0700

> |10021  |405            |int8_ops       |11                     |10                     |1977           |20                     |true           |0         |

Was the above done before or after you did the reindex?

 From original post:

"
When doing a pg_dump of one of our databases one of the tables primary 
keys doesn't get exported. Pg_dump just skips this index, without any 
warning whatsoever (verbose mode was used to doublecheck).

When doing a REINDEX the issue is fixed.
"

That would imply that after the successful REINDEX and dump some action 
is taken that makes the index disappear.

What is the table used for?

Are there any sort of 'unusual' operations done on it?

---

From: Greg Sabino Mullane <[email-masked]>
Date: Sat, 11 Oct 2025 21:44:34 -0400

> Name               |Value  |

...

indclass           |{}     |

Hold on, that makes no sense at all. The indkey/indclass columns cannot be
empty, especially as indnkeyatts is 1, as it should be. As a matter of
fact, pg_dump would completely choke on a broken table like this and not
even be able to dump it. But that output is clearly not from psql, so I
think whatever client application you are using is not able to reliably
output array columns. Any chance you can run that select command using
psql? As the rest of the columns look sane, I'm going to guess those are as
well, they just don't show up correctly, and the system catalogs are
uncorrupted.

until I do the rebuild and then the issue is fixed

Could you show us exactly the steps that show the index is missing, and
that it is then fixed?

(ponders) Keep in mind that although you declared the primary key in your
create table statement, pg_dump is going to separate the table creation
from the primary key creation by a lot of lines. So you will see in the
pg_dump output:

CREATE TABLE public.bcf_work_type (
    id bigint NOT NULL,
    aml_score bigint NOT NULL
);

and then much later on:

ALTER TABLE ONLY public.bcf_work_type
    ADD CONSTRAINT idx_376814_primary PRIMARY KEY (id);

Also be aware that if you are using the --section argument, the table will
appear in the 'pre-data' section but the primary key will appear in the
'post-data' section.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

---

From: Wim Rouquart <[email-masked]>
Date: Tue, 28 Oct 2025 10:25:01 +0000

Internal

I used the output from DBeaver, guess it acted up. Here’s the output from psql:

select * from pg_index where indexrelid  = 'idx_376814_primary'::regclass;
-[ RECORD 1 ]-------+--------
indexrelid          | 2006873
indrelid            | 1998823
indnatts            | 1
indnkeyatts         | 1
indisunique         | t
indnullsnotdistinct | f
indisprimary        | t
indisexclusion      | f
indimmediate        | t
indisclustered      | f
indisvalid          | t
indcheckxmin        | f
indisready          | t
indislive           | t
indisreplident      | f
indkey              | 1
indcollation        | 0
indclass            | 3124
indoption           | 0
indexprs            |
indpred             |

The steps that show the index is missing is an export of the database while the index is ‘corrupt’, and then importing it. The import fails on foreign keys that are pointing to this index because it is indeed not created.
Checking the export file shows the create statement from the index is indeed missing (I know it can show up lower in the file, a search was done on the index name, it’s not in there, you’re going to have to trust me on this).
After  doing a reindex like this:

REINDEX INDEX idx_376814_primary;

the export import story works just fine, the index is in there and is created.

Cheers,

Wim.

On Sat, Sep 20, 2025 at 5:07 AM Wim Rouquart <[email-masked]<mailto:[email-masked]>> wrote:
Name               |Value  |
-------------------+-------+
indexrelid         |2006873|
indrelid           |1998823|
indnatts           |1      |
indnkeyatts        |1      |
...
indclass           |{}     |

Hold on, that makes no sense at all. The indkey/indclass columns cannot be empty, especially as indnkeyatts is 1, as it should be. As a matter of fact, pg_dump would completely choke on a broken table like this and not even be able to dump it. But that output is clearly not from psql, so I think whatever client application you are using is not able to reliably output array columns. Any chance you can run that select command using psql? As the rest of the columns look sane, I'm going to guess those are as well, they just don't show up correctly, and the system catalogs are uncorrupted.

Could you show us exactly the steps that show the index is missing, and that it is then fixed?

(ponders) Keep in mind that although you declared the primary key in your create table statement, pg_dump is going to separate the table creation from the primary key creation by a lot of lines. So you will see in the pg_dump output:

and then much later on:

Also be aware that if you are using the --section argument, the table will appear in the 'pre-data' section but the primary key will appear in the 'post-data' section.

Cheers,
Greg

--
Crunchy Data - https://altered.secure4u.kbc.be/https://www.crunchydata.com<https://altered.secure4u.kbc.be/https:/www.crunchydata.com>
Enterprise Postgres Software Products & Tech Support

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 28 Oct 2025 10:46:39 -0400

Could you run the SELECT on pg_index after the REINDEX to see if there is
any difference?

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 28 Oct 2025 08:17:41 -0700

> I used the output from DBeaver, guess it acted up. Here’s the output 

1) As side note, find a different client to use then DBeaver. I have 
seen many Postgres questions on Stack Overflow where the answer was, use 
something other then DBeaver.

2) Where and when was the query below run, on the original instance 
before the pg_dump or on the new instance after the restore and index 
rebuild?

3) From this post:

https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A%40AS2PR05MB10754.eurprd05.prod.outlook.com

"
It is in neither, that’s why I'm sure it doesn't get exported. After a 
REINDEX statement it is."

 From here:

https://www.postgresql.org/docs/current/sql-reindex.html

"REINDEX rebuilds an index using the data stored in the index's table, 
replacing the old copy of the index."

The question then is, how  does a REINDEX work on an index that 
supposedly does not exist?

When you do the original restore and before the REINDEX, in psql, what 
does the below return?:

\d bcf_work_type

> The steps that show the index is missing is an export of the database 
> while the index is ‘corrupt’, and then importing it. The import fails on 
> foreign keys that are pointing to this index because it is indeed not 
> created.
> 
> Checking the export file shows the create statement from the index is 
> indeed missing (I know it can show up lower in the file, a search was 
> done on the index name, it’s not in there, you’re going to have to trust 
> me on this).
> 
> Hold on, that makes no sense at all. The indkey/indclass columns cannot 
> be empty, especially as indnkeyatts is 1, as it should be. As a matter 
> of fact, pg_dump would completely choke on a broken table like this and 
> not even be able to dump it. But that output is clearly not from psql, 
> so I think whatever client application you are using is not able to 
> reliably output array columns. Any chance you can run that select 
> command using psql? As the rest of the columns look sane, I'm going to 
> guess those are as well, they just don't show up correctly, and the 
> system catalogs are uncorrupted.
> 
> (ponders) Keep in mind that although you declared the primary key in 
> your create table statement, pg_dump is going to separate the table 
> creation from the primary key creation by a lot of lines. So you will 
> see in the pg_dump output:
> 
> Also be aware that if you are using the --section argument, the table 
> will appear in the 'pre-data' section but the primary key will appear in 
> the 'post-data' section.
> 
> Cheers,
> 
> Greg
> 
> --
> 
> Crunchy Data - https://altered.secure4u.kbc.be/https:// 
> www.crunchydata.com <https://altered.secure4u.kbc.be/https:/ 
> www.crunchydata.com>
>

---

From: Wim Rouquart <[email-masked]>
Date: Tue, 27 Jan 2026 12:18:20 +0000

Internal

Again, sorry for the late response. More pressing things tend to get in the way.

So the output of these queries before the reindex is:

db_name_hidden=# select * from pg_index where indexrelid  = 'idx_376814_primary'::regclass;
-[ RECORD 1 ]-------+--------
indexrelid          | 2006873
indrelid            | 1998823
indnatts            | 1
indnkeyatts         | 1
indisunique         | t
indnullsnotdistinct | f
indisprimary        | t
indisexclusion      | f
indimmediate        | t
indisclustered      | f
indisvalid          | t
indcheckxmin        | f
indisready          | t
indislive           | t
indisreplident      | f
indkey              | 1
indcollation        | 0
indclass            | 3124
indoption           | 0
indexprs            |
indpred             |

db_name_hidden =# \d bcf_work_type

                     Table "name_hidden.bcf_work_type"
  Column   |  Type  | Collation | Nullable |                  Default
-----------+--------+-----------+----------+-------------------------------------------
 id        | bigint |           | not null | nextval('bcf_work_type_id_seq'::regclass)
 aml_score | bigint |           | not null |
Referenced by:
    TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT

After the REINDEX command (REINDEX INDEX idx_376814_primary; ) this becomes:

db_name_hidden=# select * from pg_index where indexrelid  = 'idx_376814_primary'::regclass;
-[ RECORD 1 ]-------+--------
indexrelid          | 2006873
indrelid            | 1998823
indnatts            | 1
indnkeyatts         | 1
indisunique         | t
indnullsnotdistinct | f
indisprimary        | t
indisexclusion      | f
indimmediate        | t
indisclustered      | f
indisvalid          | t
indcheckxmin        | f
indisready          | t
indislive           | t
indisreplident      | f
indkey              | 1
indcollation        | 0
indclass            | 3124
indoption           | 0
indexprs            |
indpred             |

db_name_hidden =# \d bcf_work_type
                     Table "name_hidden.bcf_work_type"
  Column   |  Type  | Collation | Nullable |                  Default
-----------+--------+-----------+----------+-------------------------------------------
 id        | bigint |           | not null | nextval('bcf_work_type_id_seq'::regclass)
 aml_score | bigint |           | not null |
Indexes:
    "idx_376814_primary" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT
    TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT

So the first result stays the same, in the description of the table now the index shows up...

-----Original Message-----
From: Adrian Klaver <[email-masked]>
Sent: dinsdag 28 oktober 2025 16:18
To: Wim Rouquart <[email-masked]>; Greg Sabino Mullane <[email-masked]>
Cc: [email-masked]
Subject: Re: Index (primary key) corrupt?

The real sender of this external email is [email-masked]

> I used the output from DBeaver, guess it acted up. Here’s the output

1) As side note, find a different client to use then DBeaver. I have seen many Postgres questions on Stack Overflow where the answer was, use something other then DBeaver.

2) Where and when was the query below run, on the original instance before the pg_dump or on the new instance after the restore and index rebuild?

3) From this post:

"
It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is."

 From here:

\d bcf_work_type

> https://altered.secure4u.kbc.be/www.crunchydata.com <https://altered.secure4u.kbc.be/https:/
> https://altered.secure4u.kbc.be/www.crunchydata.com>
>

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 27 Jan 2026 08:02:17 -0800

> Again, sorry for the late response. More pressing things tend to get in the way.

Bottom line the index exists, it is just not being applied.

Questions:

1) What is the restore command being used?

2) From this post:
https://www.postgresql.org/message-id/AS2PR05MB10754BFE319E2594C9E076EE2EFFDA%40AS2PR05MB10754.eurprd05.prod.outlook.com

What does this:

"The steps that show the index is missing is an export of the database 
while the index is ‘corrupt’, and then importing it. The import fails on 
foreign keys that are pointing to this index because it is indeed not 
created."

mean?

If you REINDEX before the export is the index attached to the table on 
import?

Define 'corrupt'.

3) The field the index points at, id, has:

bigint nextval('bcf_work_type_id_seq'::regclass).

Is that coming from a bigserial definition or a DEFAULT setting?

4) What happens if you create a test database and restore bcf_work_type 
by itself, with and without data?

---

From: Wim Rouquart <[email-masked]>
Date: Wed, 28 Jan 2026 11:02:06 +0000

Internal

-> It seems to exist indeed, but not visible for pg_dump and some other catalog queries...

Questions:

-> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was done from the production db so I could have a playground for this situation. It's clear the situation is the same on the original and the backup copy.

2) From this post:

What does this:

"The steps that show the index is missing is an export of the database while the index is ‘corrupt’, and then importing it. The import fails on foreign keys that are pointing to this index because it is indeed not created."

mean?

-> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primary key index fail to create because it's not created, which makes sense. It's not created because it's not exported.

If you REINDEX before the export is the index attached to the table on import?

-> Yes, then it gets exported and hence imported

Define 'corrupt'.

-> Well, it's not behaving as it should, let's keep it at that.

-> I talked to the devs. There is no packaged code for this as it turns out this was originally a mysql/mariadb db which got converted to Postgresql using pgloader. But this issue has nothing to do with that migration, we did datarefreshes successfully (after the migration) before it started failing.

4) What happens if you create a test database and restore bcf_work_type by itself, with and without data?

-> I could test this, how would you suggest to do the backup/restore part, also pg_dump?

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 28 Jan 2026 08:17:06 -0800

> -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was done from the production db so I could have a playground for this situation. It's…

Whoa, pg_basebackup does not involve pg_dump. They are two different 
beasts, where pg_basebackup is a file based binary method and 
pg_dump/pg_restore is a logical method of issuing commands. So the 
restore method is definitely something that needs to be looked at. Even 
if in the production scenario pg_basebackup is not being used how the 
schema and data are being restored is important as that seems to be the 
step where information goes missing.

> -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primary key index fail to create because it's not created, which makes sense. It's…

If you are using pg_dump on one end of the process and pg_basebackup on 
the other end I can see where there are issues, though I would expect 
more problems.

In a pg_dump/pg_restore cycle I don't know how a user created index 
could be present in the system catalog without also being present in the 
pg_dump commands or throwing some sort of error.

> -> I could test this, how would you suggest to do the backup/restore part, also pg_dump?

For table w/data:

pg_dump -d some_db -U some_user -t name_hidden.bcf_work_type -f 
bcf_work_type.sql

with table schema only:

pg_dump -d some_db -U some_user -s -t name_hidden.bcf_work_type -f 
bcf_work_type.sql

This will produce a plain text SQL script.

To restore:

psql -d some_other_db -U some_user -f bcf_work_type.sql

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 28 Jan 2026 08:31:30 -0800

> -> Well, it's not behaving as it should, let's keep it at that.

I don't we can, the heart of the issue is something happens to the index 
that causes it not to transfer properly. What causes the initial 
corruption is relevant.

---

From: Wim Rouquart <[email-masked]>
Date: Wed, 11 Feb 2026 09:05:08 +0000

Internal

I know the initial thread was started a while ago, but as was explained there the restore was done purely to have a playground db for this specific issue. I know the difference between pg_dump and pg_restore.
The issue is with pg_dump, not with pg_basebackup (as is proven as pg_basebackup and then the restore perfectly transfers the 'situation' as is between the production database and the playground database).

I just did the dumps as requested, neither of them are showing the index create as expected.

-----Original Message-----
From: Adrian Klaver <[email-masked]>
Sent: woensdag 28 januari 2026 17:17
To: Wim Rouquart <[email-masked]>; Greg Sabino Mullane <[email-masked]>
Cc: [email-masked]
Subject: Re: Index (primary key) corrupt?

> -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was done from the production db so I could have a playground for this situation. It's…

Whoa, pg_basebackup does not involve pg_dump. They are two different beasts, where pg_basebackup is a file based binary method and pg_dump/pg_restore is a logical method of issuing commands. So the restore method is definitely something that needs to be looked at. Even if in the production scenario pg_basebackup is not being used how the schema and data are being restored is important as that seems to be the step where information goes missing.

> -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primary key index fail to create because it's not created, which makes sense. It's…

If you are using pg_dump on one end of the process and pg_basebackup on the other end I can see where there are issues, though I would expect more problems.

In a pg_dump/pg_restore cycle I don't know how a user created index could be present in the system catalog without also being present in the pg_dump commands or throwing some sort of error.

For table w/data:

pg_dump -d some_db -U some_user -t name_hidden.bcf_work_type -f bcf_work_type.sql

with table schema only:

pg_dump -d some_db -U some_user -s -t name_hidden.bcf_work_type -f bcf_work_type.sql

To restore:

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 12 Feb 2026 08:25:28 -0800

> The issue is with pg_dump, not with pg_basebackup (as is proven as pg_basebackup and then the restore perfectly transfers the 'situation' as is between the production database and the playground da…

Are you saying that you used pg_basebackp to create a test instance and 
then did a pg_dump from the test instance and used that output in a 
pg_restore to another database?

> I just did the dumps as requested, neither of them are showing the index create as expected.

As requested being?:

For table w/data:

with table schema only:

To restore:

Was this with the index in the originating database being in a 
functional state?

As a general note you need to provide more supporting information when 
replying. This thread has gone through so many iterations of conditions 
it helps to know the exact conditions you are currently working under.

---

From: Wim Rouquart <[email-masked]>
Date: Fri, 13 Feb 2026 08:08:29 +0000

Internal

Ok, to do a small recap because indeed this thread has been extended for a while now.

- The issue with the specific index was noted on a production database (after a datarefresh that partly failed because of the missing index).

- To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a test instance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackup is no longer involved, everything is pg_dump based from here on onwards.

- So this means the test pg_dumps where done with the index in a 'non-fuctional state'. As expected, the create statement of the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of the table).

I hope this clears out any confusion.

-----Original Message-----
From: Adrian Klaver <[email-masked]>
Sent: donderdag 12 februari 2026 17:25
To: Wim Rouquart <[email-masked]>; Greg Sabino Mullane <[email-masked]>
Cc: [email-masked]
Subject: Re: Index (primary key) corrupt?

> The issue is with pg_dump, not with pg_basebackup (as is proven as pg_basebackup and then the restore perfectly transfers the 'situation' as is between the production database and the playground da…

Are you saying that you used pg_basebackp to create a test instance and then did a pg_dump from the test instance and used that output in a pg_restore to another database?

As requested being?:

For table w/data:

with table schema only:

To restore:

Was this with the index in the originating database being in a functional state?

As a general note you need to provide more supporting information when replying. This thread has gone through so many iterations of conditions it helps to know the exact conditions you are currently working under.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Feb 2026 08:09:21 -0800

> - So this means the test pg_dumps where done with the index in a 'non-fuctional state'. As expected, the create statement of the index does NOT show up in the generated .sql scripts (neither 'loose…

1) It won't be included with the CREATE TABLE statement per:-

https://www.postgresql.org/docs/current/app-pgdump.html

"--section=sectionname

     Only dump the named section. The section name can be pre-data, 
data, or post-data. This option can be specified more than once to 
select multiple sections. The default is to dump all sections.

     The data section contains actual table data, large-object contents, 
sequence values, and statistics for tables, materialized views, and 
foreign tables. Post-data items include definitions of indexes, 
triggers, rules, statistics for indexes, and constraints other than 
validated check and not-null constraints. Pre-data items include all 
other data definition items.
"
2) The issue seems to be not the dump, but the non-functional state of 
the index on the source database.

Is there any indication of why that is happening?

Also what error do you get on the source database that tells you the PK 
is not working?

Adrian Klaver