Обсуждение: Index (primary key) corrupt?
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).
When doing a REINDEX the issue is fixed.
As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).
I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?
Are there any other ways to doublecheck for corruption (without enabling checksum upfront)?
This concerns a PostgreSQL version 15 btw.
Thanks!
Disclaimer
On Thu, Sep 18, 2025 at 10:58 AM Wim Rouquart <wim.rouquart@kbc.be> wrote:
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).
When doing a REINDEX the issue is fixed.
As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).
I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?
Does this problem keep happening, or has it only happened once?
Are there any other ways to doublecheck for corruption (without enabling checksum upfront)?
pg_checksums is available in PG 15.
This concerns a PostgreSQL version 15 btw.
Are you at the current patch level?
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On 9/18/25 05:25, Wim Rouquart wrote:
> 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?
What is the complete pg_dump command being given?
Is the PK definition in the pg_dump file?
For plain text format can you grep/find it?
For custom format does:
pg_restore -s -t <the_table> <dump_file>
show it?
How is the dump file being restored?
>
> When doing a REINDEX the issue is fixed.
>
> As this seems to me to be some form of index corruption, I tried using
> amcheck (bt_index_check and bt_index_parent_check) to verify for
> corruption but both resulted with no issues (the index is a btree).
>
> I would expect the corruption to show up when using amcheck, am I
> hitting some kind of bug here?
>
> Are there any other ways to doublecheck for corruption (without enabling
> checksum upfront)?
>
> This concerns a PostgreSQL version 15 btw.
>
> Thanks!
>
>
> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
--
Adrian Klaver
adrian.klaver@aklaver.com
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).
When doing a REINDEX the issue is fixed.
As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).
I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?
>> Does this problem keep happening, or has it only happened once?
It is consistent on this database/index, haven’t noticed it anywhere else yet luckily. Seems to be a one-off.
Are there any other ways to doublecheck for corruption (without enabling checksum upfront)?
>> pg_checksums is available in PG 15.
It is indeed, so to test I enabled checksums, and did a checksum test, no errors, and yes, the file containing the index is mentioned in the checks…
This concerns a PostgreSQL version 15 btw.
>> Are you at the current patch level?
Yes.
Disclaimer
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)
);
> 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.
>>
>> When doing a REINDEX the issue is fixed.
>>
>> As this seems to me to be some form of index corruption, I tried using
>> amcheck (bt_index_check and bt_index_parent_check) to verify for
>> corruption but both resulted with no issues (the index is a btree).
>>
>> I would expect the corruption to show up when using amcheck, am I
>> hitting some kind of bug here?
>>
>> Are there any other ways to doublecheck for corruption (without
>> enabling checksum upfront)?
>>
>> This concerns a PostgreSQL version 15 btw.
>>
>> Thanks!
>>
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
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 adrian.klaver@aklaver.com
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?
I'm not the developer of the application, can't really answer these ones I'm afraid.
> What is the result for the query?:
> 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.
>>
>>> 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.
>>
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 9/19/25 06:23, Wim Rouquart wrote:
>> What is the result for the query?:
>
>> 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 | |
>
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?
>
> Same answer as above.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 9/19/25 02:29, Wim Rouquart wrote: > Internal > >>> Does this problem keep happening, or has it only happened once? > > It is consistent on this database/index, haven’t noticed it anywhere > else yet luckily. Seems to be a one-off. The above is not clear to me. One-off as for this database/index only and repeatable across dumps or that it only happen in one dump. > This concerns a PostgreSQL version 15 btw. > >>> Are you at the current patch level? > > Yes. Please specify the actual version, current is a relative term. It matters for folks using this thread for researching an issue in the future. > > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer> -- Adrian Klaver adrian.klaver@aklaver.com
On Friday, September 19, 2025, Wim Rouquart <wim.rouquart@kbc.be> wrote:
>> Does this problem keep happening, or has it only happened once?
It is consistent on this database/index, haven’t noticed it anywhere else yet luckily. Seems to be a one-off.
So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem to recur after you’ve done reindex to make it work?
David J.
> So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? > > David I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? >
On 9/20/25 09:26, Rob Sargent wrote: > >> So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? >> >> David > > I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? From this post: https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A%40AS2PR05MB10754.eurprd05.prod.outlook.com " > 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. " Make of that what you will. -- Adrian Klaver adrian.klaver@aklaver.com
> On Sep 20, 2025, at 9:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 9/20/25 09:26, Rob Sargent wrote: >>> So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? >>> David >> I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? > > From this post: > > https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A%40AS2PR05MB10754.eurprd05.prod.outlook.com > > " > > 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. > " > > Make of that what you will. > > > -- > Adrian Klaver > adrian.klaver@aklaver.com I don’t see the complete REINDEX command used but from the output of the query on pg_index it looks like reindex using indexname would succeed, no? Again assuming this was done against the dump which may or may not have matched a grep attempt.Lord knows I’ve had my share of false negatives with grep.
Internal
Hi,
Apologies for the late response, had other fish to fry...
In response to your questions:
> 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
> What do you get for queries below?:
>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 |
>> How is the table populated with data?
>>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'sonly 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.
On 9/19/25 06:23, Wim Rouquart wrote:
>> What is the result for the query?:
>
>> 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 | |
>
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?
>
> Same answer as above.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
Internal Yes indeed, i just restore the database to before the rebuild. > So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? > > David I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? > Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 10/10/25 05:28, Wim Rouquart wrote:
> Internal
>
> Hi,
>
> Apologies for the late response, had other fish to fry...
>
> In response to your questions:
>
>> 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
>
>> What do you get for queries below?:
>
>> 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 |
>
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?
--
Adrian Klaver
adrian.klaver@aklaver.com
On Sat, Sep 20, 2025 at 5:07 AM Wim Rouquart <wim.rouquart@kbc.be> 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.
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
);
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);
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
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 <wim.rouquart@kbc.be> 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.
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://altered.secure4u.kbc.be/https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
Disclaimer
Could you run the SELECT on pg_index after the REINDEX to see if there is any difference?
On 10/28/25 03:25, Wim Rouquart wrote: > Internal > > > I used the output from DBeaver, guess it acted up. Here’s the output > from psql: 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 > > 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 <wim.rouquart@kbc.be > <mailto:wim.rouquart@kbc.be>> 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. > > 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://altered.secure4u.kbc.be/https:// > www.crunchydata.com <https://altered.secure4u.kbc.be/https:/ > www.crunchydata.com> > > Enterprise Postgres Software Products & Tech Support > > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer> -- Adrian Klaver adrian.klaver@aklaver.com
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
UPDATERESTRICT ON DELETE RESTRICT
TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id)
ONUPDATE 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
UPDATERESTRICT ON DELETE RESTRICT
TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id)
ONUPDATE 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 <adrian.klaver@aklaver.com>
Sent: dinsdag 28 oktober 2025 16:18
To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Index (primary key) corrupt?
The real sender of this external email is adrian.klaver@aklaver.com
On 10/28/25 03:25, Wim Rouquart wrote:
> Internal
>
>
> I used the output from DBeaver, guess it acted up. Here’s the output
> from psql:
1) As side note, find a different client to use then DBeaver. I have seen many Postgres questions on Stack Overflow
wherethe 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
restoreand 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
>
> 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 <wim.rouquart@kbc.be
> <mailto:wim.rouquart@kbc.be>> 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.
>
> 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://altered.secure4u.kbc.be/https://
> https://altered.secure4u.kbc.be/www.crunchydata.com <https://altered.secure4u.kbc.be/https:/
> https://altered.secure4u.kbc.be/www.crunchydata.com>
>
> Enterprise Postgres Software Products & Tech Support
>
>
> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
--
Adrian Klaver
adrian.klaver@aklaver.com
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 1/27/26 04:18, Wim Rouquart wrote: > Internal > > 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? > > 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) ONUPDATE 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) ONUPDATE 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... > > > -- Adrian Klaver adrian.klaver@aklaver.com
Internal Bottom line the index exists, it is just not being applied. -> It seems to exist indeed, but not visible for pg_dump and some other catalog queries... Questions: 1) What is the restore command being used? -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was done fromthe production db so I could have a playground for this situation. It's clear the situation is the same on the originaland the backup copy. 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? -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primarykey 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. 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? -> I talked to the devs. There is no packaged code for this as it turns out this was originally a mysql/mariadb db whichgot converted to Postgresql using pgloader. But this issue has nothing to do with that migration, we did datarefreshessuccessfully (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? > > 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) ONUPDATE 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) ONUPDATE 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... > > > -- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 1/28/26 03:02, Wim Rouquart wrote: > Internal > > Bottom line the index exists, it is just not being applied. > > -> It seems to exist indeed, but not visible for pg_dump and some other catalog queries... > > Questions: > > 1) What is the restore command being used? > > -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was donefrom the production db so I could have a playground for this situation. It's clear the situation is the same on the originaland the backup copy. 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 primarykey index fail to create because it's not created, which makes sense. It's not created because it's not exported. 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. > > 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? 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 -- Adrian Klaver adrian.klaver@aklaver.com
On 1/28/26 03:02, Wim Rouquart wrote: > Internal > Define 'corrupt'. > > -> 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. -- Adrian Klaver adrian.klaver@aklaver.com
Internal I know the initial thread was started a while ago, but as was explained there the restore was done purely to have a playgrounddb 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 transfersthe '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 <adrian.klaver@aklaver.com> Sent: woensdag 28 januari 2026 17:17 To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: Index (primary key) corrupt? The real sender of this external email is adrian.klaver@aklaver.com On 1/28/26 03:02, Wim Rouquart wrote: > Internal > > Bottom line the index exists, it is just not being applied. > > -> It seems to exist indeed, but not visible for pg_dump and some other catalog queries... > > Questions: > > 1) What is the restore command being used? > > -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was donefrom the production db so I could have a playground for this situation. It's clear the situation is the same on the originaland the backup copy. Whoa, pg_basebackup does not involve pg_dump. They are two different beasts, where pg_basebackup is a file based binary methodand pg_dump/pg_restore is a logical method of issuing commands. So the restore method is definitely something thatneeds to be looked at. Even if in the production scenario pg_basebackup is not being used how the schema and data arebeing 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 primarykey index fail to create because it's not created, which makes sense. It's not created because it's not exported. 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, thoughI 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 beingpresent in the pg_dump commands or throwing some sort of error. > > 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? 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 -- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 2/11/26 01:05, Wim Rouquart wrote: > Internal > > I know the initial thread was started a while ago, but as was explained there the restore was done purely to have a playgrounddb 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 transfersthe 'situation' as is between the production database and the playground database). 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: 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 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. -- Adrian Klaver adrian.klaver@aklaver.com
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 ofthe 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_basebackupis 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 statementof the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of thetable). I hope this clears out any confusion. -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: donderdag 12 februari 2026 17:25 To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: Index (primary key) corrupt? The real sender of this external email is adrian.klaver@aklaver.com On 2/11/26 01:05, Wim Rouquart wrote: > Internal > > I know the initial thread was started a while ago, but as was explained there the restore was done purely to have a playgrounddb 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 transfersthe 'situation' as is between the production database and the playground database). Are you saying that you used pg_basebackp to create a test instance and then did a pg_dump from the test instance and usedthat 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: 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 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 iterationsof conditions it helps to know the exact conditions you are currently working under. -- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 2/13/26 00:08, Wim Rouquart wrote: > 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 becauseof the missing index). > > - To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a testinstance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackupis 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 statementof the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of thetable). 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? > > I hope this clears out any confusion. > > -----Original Message----- Adrian Klaver adrian.klaver@aklaver.com
Internal 1) ) It won't be included with the CREATE TABLE statement per:- Yes, let's keep it at: it's not in the dumpfile anywhere. > 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? Not as far as I know. > Also what error do you get on the source database that tells you the PK is not working? None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was not createdin the target (because it was not in the dumpfile). -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: vrijdag 13 februari 2026 17:09 To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: Index (primary key) corrupt? The real sender of this external email is adrian.klaver@aklaver.com On 2/13/26 00:08, Wim Rouquart wrote: > 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 becauseof the missing index). > > - To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a testinstance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackupis 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 statementof the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of thetable). 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 morethan 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, materializedviews, 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 definitionitems. " 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? > > I hope this clears out any confusion. > > -----Original Message----- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 2/13/26 8:27 AM, Wim Rouquart wrote: > Internal > > 1) ) It won't be included with the CREATE TABLE statement per:- > > Yes, let's keep it at: it's not in the dumpfile anywhere. > >> 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? > > Not as far as I know. So the REINDEX on the source PK is prompted by it not showing up on the target? > >> Also what error do you get on the source database that tells you the PK is not working? > > None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was notcreated in the target (because it was not in the dumpfile). On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a record with a duplicate id? > >> I hope this clears out any confusion. >> >> -----Original Message----- > Adrian Klaver > adrian.klaver@aklaver.com > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
Internal > So the REINDEX on the source PK is prompted by it not showing up on the target? That's how we noticed the initial issue yes, we got errors during the datarefresh on the target database where foreign keyswanted to reference the non-existing index (because it wasn't imported). > On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT arecord with a duplicate id? Good question, as I kind of expected, it doesn't complain at all when I do an insert with a duplicate id (and the row actuallygets inserted). If I consecutively try to do the reindex, then I get the error that it can't because of doubles... -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: vrijdag 13 februari 2026 18:33 To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: Index (primary key) corrupt? The real sender of this external email is adrian.klaver@aklaver.com On 2/13/26 8:27 AM, Wim Rouquart wrote: > Internal > > 1) ) It won't be included with the CREATE TABLE statement per:- > > Yes, let's keep it at: it's not in the dumpfile anywhere. > >> 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? > > Not as far as I know. So the REINDEX on the source PK is prompted by it not showing up on the target? > >> Also what error do you get on the source database that tells you the PK is not working? > > None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was notcreated in the target (because it was not in the dumpfile). On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a recordwith a duplicate id? > >> I hope this clears out any confusion. >> >> -----Original Message----- > Adrian Klaver > adrian.klaver@aklaver.com > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 3/5/26 5:23 AM, Wim Rouquart wrote:
> Internal
>
>> So the REINDEX on the source PK is prompted by it not showing up on the target?
>
> That's how we noticed the initial issue yes, we got errors during the datarefresh on the target database where
foreignkeys wanted to reference the non-existing index (because it wasn't imported).
>
>> On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to
INSERTa record with a duplicate id?
>
> Good question, as I kind of expected, it doesn't complain at all when I do an insert with a duplicate id (and the row
actuallygets inserted). If I consecutively try to do the reindex, then I get the error that it can't because of
doubles...
>
>
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: vrijdag 13 februari 2026 18:33
> To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com>
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: Index (primary key) corrupt?
>
>
>
> The real sender of this external email is adrian.klaver@aklaver.com
>
>
>
>
>
>
> On 2/13/26 8:27 AM, Wim Rouquart wrote:
>> Internal
>>
>> 1) ) It won't be included with the CREATE TABLE statement per:-
>>
>> Yes, let's keep it at: it's not in the dumpfile anywhere.
>>
>>> 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?
>>
>> Not as far as I know.
>
> So the REINDEX on the source PK is prompted by it not showing up on the target?
>
>>
>>> Also what error do you get on the source database that tells you the PK is not working?
>>
>> None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was
notcreated in the target (because it was not in the dumpfile).
>
> On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT
arecord with a duplicate id?
Alright, so the corrupt index is transferred by the binary
pg_basebackup, but not in logical backups done via pg_dump/pg_restore.
The issue then is on the source database with whatever process is
corrupting the index and causing no error to be thrown when the table is
dumped.
Just to be clear we are talking about this table:
CREATE TABLE bcf_work_type (
id bigserial NOT NULL,
aml_score int8 NOT NULL,
CONSTRAINT idx_376814_primary PRIMARY KEY (id)
);
What is the use pattern for this table?
As I recall this is not a large table, but for completeness what is it's
average size?
What are the Postgres log settings, on the source database, for?:
log_min_messages
log_min_error_statement
log_error_verbosity
log_statement
Are there any entries in the Postgres log that reference this table?
>
>>
>
>>> I hope this clears out any confusion.
>>>
>>> -----Original Message-----
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
>
>
> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
--
Adrian Klaver
adrian.klaver@aklaver.com
Internal
> Alright, so the corrupt index is transferred by the binary pg_basebackup, but not in logical backups done via
pg_dump/pg_restore.
Correct
> The issue then is on the source database with whatever process is corrupting the index and causing no error to be
thrownwhen the table is dumped.
"Whatever process is corrupting the index" -> I think this was more of a one-off incident that happened somewhere in
thepast, this is not a recurring issue. It's not like this index becomes corrupt again after I reindex it, to be
clear.
>Just to be clear we are talking about this table:
>CREATE TABLE bcf_work_type (
> id bigserial NOT NULL,
> aml_score int8 NOT NULL,
> CONSTRAINT idx_376814_primary PRIMARY KEY (id) );
Yes
> What is the use pattern for this table?
Well, I understood from the devs it is basically no longer used currently, probably explaining why no issues have shown
upuntil the datarefresh.
> As I recall this is not a large table, but for completeness what is it's average size?
It has only 12 rows 😊
> What are the Postgres log settings, on the source database, for?:
log_error_verbosity = 'default'
log_min_error_statement = 'fatal'
log_min_messages = 'warning'
log_statement = 'ddl'
> Are there any entries in the Postgres log that reference this table?
Nope, none found, probably because it isn't used anymore (and because of above log settings if it would be)
-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: donderdag 5 maart 2026 17:05
To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Index (primary key) corrupt?
The real sender of this external email is adrian.klaver@aklaver.com
On 3/5/26 5:23 AM, Wim Rouquart wrote:
> Internal
>
>> So the REINDEX on the source PK is prompted by it not showing up on the target?
>
> That's how we noticed the initial issue yes, we got errors during the datarefresh on the target database where
foreignkeys wanted to reference the non-existing index (because it wasn't imported).
>
>> On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to
INSERTa record with a duplicate id?
>
> Good question, as I kind of expected, it doesn't complain at all when I do an insert with a duplicate id (and the row
actuallygets inserted). If I consecutively try to do the reindex, then I get the error that it can't because of
doubles...
>
>
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: vrijdag 13 februari 2026 18:33
> To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane
> <htamfids@gmail.com>
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: Index (primary key) corrupt?
>
>
>
> The real sender of this external email is adrian.klaver@aklaver.com
>
>
>
>
>
>
> On 2/13/26 8:27 AM, Wim Rouquart wrote:
>> Internal
>>
>> 1) ) It won't be included with the CREATE TABLE statement per:-
>>
>> Yes, let's keep it at: it's not in the dumpfile anywhere.
>>
>>> 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?
>>
>> Not as far as I know.
>
> So the REINDEX on the source PK is prompted by it not showing up on the target?
>
>>
>>> Also what error do you get on the source database that tells you the PK is not working?
>>
>> None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was
notcreated in the target (because it was not in the dumpfile).
>
> On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT
arecord with a duplicate id?
Alright, so the corrupt index is transferred by the binary pg_basebackup, but not in logical backups done via
pg_dump/pg_restore.
The issue then is on the source database with whatever process is corrupting the index and causing no error to be
thrownwhen the table is dumped.
Just to be clear we are talking about this table:
CREATE TABLE bcf_work_type (
id bigserial NOT NULL,
aml_score int8 NOT NULL,
CONSTRAINT idx_376814_primary PRIMARY KEY (id) );
What is the use pattern for this table?
As I recall this is not a large table, but for completeness what is it's average size?
What are the Postgres log settings, on the source database, for?:
log_min_messages
log_min_error_statement
log_error_verbosity
log_statement
Are there any entries in the Postgres log that reference this table?
>
>>
>
>>> I hope this clears out any confusion.
>>>
>>> -----Original Message-----
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
>
>
> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
--
Adrian Klaver
adrian.klaver@aklaver.com
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 3/6/26 2:31 AM, Wim Rouquart wrote: > Internal > > >> As I recall this is not a large table, but for completeness what is it's average size? > > It has only 12 rows 😊 > >> What are the Postgres log settings, on the source database, for?: > > log_error_verbosity = 'default' > log_min_error_statement = 'fatal' > log_min_messages = 'warning' > log_statement = 'ddl' > >> Are there any entries in the Postgres log that reference this table? > > Nope, none found, probably because it isn't used anymore (and because of above log settings if it would be) > Well at this point I don't see that it is possible to determine a cause for why the index does not transfer or throw an error in the process. -- Adrian Klaver adrian.klaver@aklaver.com
Internal I already saw finding the actual cause as a 'lost cause' as these things tend to happen, however what bothers me most isthat a tool like amcheck which is supposed to find corruption also shows up with no result. -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: vrijdag 6 maart 2026 17:03 To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: Index (primary key) corrupt? The real sender of this external email is adrian.klaver@aklaver.com On 3/6/26 2:31 AM, Wim Rouquart wrote: > Internal > > >> As I recall this is not a large table, but for completeness what is it's average size? > > It has only 12 rows 😊 > >> What are the Postgres log settings, on the source database, for?: > > log_error_verbosity = 'default' > log_min_error_statement = 'fatal' > log_min_messages = 'warning' > log_statement = 'ddl' > >> Are there any entries in the Postgres log that reference this table? > > Nope, none found, probably because it isn't used anymore (and because > of above log settings if it would be) > Well at this point I don't see that it is possible to determine a cause for why the index does not transfer or throw an errorin the process. -- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 3/9/26 7:12 AM, Wim Rouquart wrote: > Internal > > I already saw finding the actual cause as a 'lost cause' as these things tend to happen, however what bothers me most isthat a tool like amcheck which is supposed to find corruption also shows up with no result. That assumes the corruption is something that amcheck checks for. -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Mar 9, 2026 at 10:12 AM Wim Rouquart <wim.rouquart@kbc.be> wrote:
I already saw finding the actual cause as a 'lost cause' as these things tend to happen, however what bothers me most is that a tool like amcheck which is supposed to find corruption also shows up with no result.
Well, no, these things really should not happen. :)
It may be too late, but it would be real interesting to see this query both before and after the REINDEX:
select * from pg_index where indrelid = 'bcf_work_type'::regclass and indisprimary;
An incorrect indrelid is one way I can think of as to how pg_dump would miss it, but that wouldn't explain why reindex would subsequently fix it.
Cheers,
Greg
On 3/9/26 8:24 AM, Greg Sabino Mullane wrote: > On Mon, Mar 9, 2026 at 10:12 AM Wim Rouquart <wim.rouquart@kbc.be > <mailto:wim.rouquart@kbc.be>> wrote: > > I already saw finding the actual cause as a 'lost cause' as these > things tend to happen, however what bothers me most is that a tool > like amcheck which is supposed to find corruption also shows up with > no result. > > > Well, no, these things really should not happen. :) > > It may be too late, but it would be real interesting to see this query > both before and after the REINDEX: > > select * from pg_index where indrelid = 'bcf_work_type'::regclass and > indisprimary; Déjà vu :) https://www.postgresql.org/message-id/CAKAnmmK9uKAcerhseNg6FSDOnMWmivM5ctUiTAdc1kobq94Dqw%40mail.gmail.com This post in answer: https://www.postgresql.org/message-id/AS2PR05MB107549DDE42DC0B8E31CB52BFEF90A%40AS2PR05MB10754.eurprd05.prod.outlook.com would seem to indicate that is not the issue. > > An incorrect indrelid is one way I can think of as to how pg_dump would > miss it, but that wouldn't explain why reindex would subsequently fix it. > > Cheers, > Greg > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Mar 9, 2026 at 11:37 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
This post in answer:
https://www.postgresql.org/message-id/AS2PR05MB107549DDE42DC0B8E31CB52BFEF90A%40AS2PR05MB10754.eurprd05.prod.outlook.com
would seem to indicate that is not the issue.
Ah, but those are doing the lookup by the index (via indexrelid) but I am curious about looking up by the table (indrelid), as that is how pg_dump is going to get at it.
Cheers,
Greg
On 3/9/26 10:53 AM, Greg Sabino Mullane wrote: > On Mon, Mar 9, 2026 at 11:37 AM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > This post in answer: > > https://www.postgresql.org/message-id/ > AS2PR05MB107549DDE42DC0B8E31CB52BFEF90A%40AS2PR05MB10754.eurprd05.prod.outlook.com <https://www.postgresql.org/message-id/AS2PR05MB107549DDE42DC0B8E31CB52BFEF90A%40AS2PR05MB10754.eurprd05.prod.outlook.com> > > would seem to indicate that is not the issue. > > > Ah, but those are doing the lookup by the index (via indexrelid) but I > am curious about looking up by the table (indrelid), as that is how > pg_dump is going to get at it. Yeah, but the indrelid did not change after the the REINDEX. I guess one could question the cast in: indrelid = 'bcf_work_type'::regclass Though I am not sure how a REINDEX would affect that? > Cheers, > Greg > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Mar 9, 2026 at 3:53 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Yeah, but the indrelid did not change after the the REINDEX.
Agreed, but none of this makes sense. pg_dump grabs pks via a bulk pg_index scan based on table oids, so I wanted to rule out some problem there.
I'm chalking this one up to user error, not database corruption, as the OP has not actually shown us the output of how they are determining the missing index, and then how the reindex fixes it (although I appreciate the pg_index query results). To put another way, user error is a much more likely explanation than anything else given the lack of specific data.
Cheers,
Greg
Internal
Let me get this straight, are you still contesting that the index is actually not part of the dumpfile and I somehow just keep on ‘missing it’?
From: Greg Sabino Mullane <htamfids@gmail.com>
Sent: dinsdag 10 maart 2026 15:15
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Wim Rouquart <wim.rouquart@kbc.be>; pgsql-general@lists.postgresql.org
Subject: Re: Index (primary key) corrupt?
The real sender of this external email is htamfids@gmail.com |
On Mon, Mar 9, 2026 at 3:53 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Yeah, but the indrelid did not change after the the REINDEX.
Agreed, but none of this makes sense. pg_dump grabs pks via a bulk pg_index scan based on table oids, so I wanted to rule out some problem there.
I'm chalking this one up to user error, not database corruption, as the OP has not actually shown us the output of how they are determining the missing index, and then how the reindex fixes it (although I appreciate the pg_index query results). To put another way, user error is a much more likely explanation than anything else given the lack of specific data.
Cheers,
Greg
Disclaimer
On Tue, Mar 10, 2026 at 10:24 AM Wim Rouquart <wim.rouquart@kbc.be> wrote:
Let me get this straight, are you still contesting that the index is actually not part of the dumpfile and I somehow just keep on ‘missing it’?
That is one possibility, yes, but there are others. We just don't have enough data. It would be great to see exactly what pg_dump is doing so we know where the corruption/disconnect is. If you have access, could you try:
psql -c "alter system set log_statement='all' " -c "select pg_reload_conf()"
pg_dump -t bcf_work_type --schema-only > bcf.debug
psql -c "alter system reset log_statement" -c "select pg_reload_conf()"
Then send us bcf.debug as well as the Postgres logs generated during that request?
Cheers,
Greg
On 3/10/26 7:43 AM, Greg Sabino Mullane wrote: > On Tue, Mar 10, 2026 at 10:24 AM Wim Rouquart <wim.rouquart@kbc.be > <mailto:wim.rouquart@kbc.be>> wrote: > > Let me get this straight, are you still contesting that the index is > actually not part of the dumpfile and I somehow just keep on > ‘missing it’? > > That is one possibility, yes, but there are others. We just don't have > enough data. It would be great to see exactly what pg_dump is doing so > we know where the corruption/disconnect is. If you have access, could > you try: I am convinced that the index definition is not in the pg_dump output. The crux of the matter seems to be from here: https://www.postgresql.org/message-id/78328b08-249e-4251-8a10-b5dac183442a%40aklaver.com "Alright, so the corrupt index is transferred by the binary pg_basebackup, but not in logical backups done via pg_dump/pg_restore. The issue then is on the source database with whatever process is corrupting the index and causing no error to be thrown when the table is dumped." Where the pg_basebackup was done from the production database in order to set up a test database and the logical dumps where done from the test database. Hopefully the below will tease that out. > > psql -c "alter system set log_statement='all' " -c "select pg_reload_conf()" > > pg_dump -t bcf_work_type --schema-only > bcf.debug > > psql -c "alter system reset log_statement" -c "select pg_reload_conf()" > > Then send us bcf.debug as well as the Postgres logs generated during > that request? > > Cheers, > Greg > -- Adrian Klaver adrian.klaver@aklaver.com