Обсуждение: 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
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.
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
>> 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 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
Name |Value |
-------------------+-------+
indexrelid |2006873|
indrelid |1998823|
indnatts |1 |
indnkeyatts |1 |
...
indclass |{} |
until I do the rebuild and then the issue is fixed
id bigint NOT NULL,
aml_score bigint NOT NULL
);
ADD CONSTRAINT idx_376814_primary PRIMARY KEY (id);
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
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