Thread
-
Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-09-18T12:25:38Z
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 <https://www.kbc.com/KBCmailDisclaimer>
-
Re: Index (primary key) corrupt?
Ron Johnson <ronljohnsonjr@gmail.com> — 2025-09-18T15:50:18Z
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!
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-18T16:01:18Z
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 -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-09-19T09:29:57Z
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 <https://www.kbc.com/KBCmailDisclaimer>
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-09-19T09:40:44Z
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> -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-19T13:04:04Z
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
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-09-19T13:23:08Z
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> -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-19T15:50:51Z
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 -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-20T15:00:37Z
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
-
Re: Index (primary key) corrupt?
David G. Johnston <david.g.johnston@gmail.com> — 2025-09-20T15:23:54Z
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.
-
Re: Index (primary key) corrupt?
Rob Sargent <robjsargent@gmail.com> — 2025-09-20T16:26:55Z
> 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 I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? >
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-20T16:58:02Z
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 to recur 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
-
Re: Index (primary key) corrupt?
Rob Sargent <robjsargent@gmail.com> — 2025-09-20T20:16:40Z
> 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 to recur 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 index name 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.
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-10-10T12:28:28Z
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's only a few rows btw) > Per a comment from Ron, does this lack of export happen every time you dump the table? Well, yes, until I do the rebuild and then the issue is fixed. 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> -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-10-10T12:30:04Z
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 to recur 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>
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-10T15:03:04Z
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
-
Re: Index (primary key) corrupt?
Greg Sabino Mullane <htamfids@gmail.com> — 2025-10-12T01:44:34Z
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://www.crunchydata.com Enterprise Postgres Software Products & Tech Support -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2025-10-28T10:25:01Z
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<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> -
Re: Index (primary key) corrupt?
Greg Sabino Mullane <htamfids@gmail.com> — 2025-10-28T14:46:39Z
Could you run the SELECT on pg_index after the REINDEX to see if there is any difference?
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-28T15:17:41Z
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 -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-01-27T12:18:20Z
Internal Again, sorry for the late response. More pressing things tend to get in the way. So the output of these queries before the reindex is: db_name_hidden=# select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; -[ RECORD 1 ]-------+-------- indexrelid | 2006873 indrelid | 1998823 indnatts | 1 indnkeyatts | 1 indisunique | t indnullsnotdistinct | f indisprimary | t indisexclusion | f indimmediate | t indisclustered | f indisvalid | t indcheckxmin | f indisready | t indislive | t indisreplident | f indkey | 1 indcollation | 0 indclass | 3124 indoption | 0 indexprs | indpred | db_name_hidden =# \d bcf_work_type Table "name_hidden.bcf_work_type" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('bcf_work_type_id_seq'::regclass) aml_score | bigint | | not null | Referenced by: TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT After the REINDEX command (REINDEX INDEX idx_376814_primary; ) this becomes: db_name_hidden=# select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; -[ RECORD 1 ]-------+-------- indexrelid | 2006873 indrelid | 1998823 indnatts | 1 indnkeyatts | 1 indisunique | t indnullsnotdistinct | f indisprimary | t indisexclusion | f indimmediate | t indisclustered | f indisvalid | t indcheckxmin | f indisready | t indislive | t indisreplident | f indkey | 1 indcollation | 0 indclass | 3124 indoption | 0 indexprs | indpred | db_name_hidden =# \d bcf_work_type Table "name_hidden.bcf_work_type" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('bcf_work_type_id_seq'::regclass) aml_score | bigint | | not null | Indexes: "idx_376814_primary" PRIMARY KEY, btree (id) Referenced by: TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT So the first result stays the same, in the description of the table now the index shows up... -----Original Message----- From: Adrian Klaver <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 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:// > 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> -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-01-27T16:02:17Z
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) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > > > After the REINDEX command (REINDEX INDEX idx_376814_primary; ) this becomes: > > db_name_hidden=# select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; > -[ RECORD 1 ]-------+-------- > indexrelid | 2006873 > indrelid | 1998823 > indnatts | 1 > indnkeyatts | 1 > indisunique | t > indnullsnotdistinct | f > indisprimary | t > indisexclusion | f > indimmediate | t > indisclustered | f > indisvalid | t > indcheckxmin | f > indisready | t > indislive | t > indisreplident | f > indkey | 1 > indcollation | 0 > indclass | 3124 > indoption | 0 > indexprs | > indpred | > > db_name_hidden =# \d bcf_work_type > Table "name_hidden.bcf_work_type" > Column | Type | Collation | Nullable | Default > -----------+--------+-----------+----------+------------------------------------------- > id | bigint | | not null | nextval('bcf_work_type_id_seq'::regclass) > aml_score | bigint | | not null | > Indexes: > "idx_376814_primary" PRIMARY KEY, btree (id) > Referenced by: > TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > > So the first result stays the same, in the description of the table now the index shows up... > > > -- Adrian Klaver adrian.klaver@aklaver.com -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-01-28T11:02:06Z
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 from the production db so I could have a playground for this situation. It's clear the situation is the same on the original and the backup copy. 2) From this post: 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 primary key index fail to create because it's not created, which makes sense. It's not created because it's not exported. If you REINDEX before the export is the index attached to the table on import? -> Yes, then it gets exported and hence imported Define 'corrupt'. -> Well, it's not behaving as it should, let's keep it at that. 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 which got converted to Postgresql using pgloader. But this issue has nothing to do with that migration, we did datarefreshes successfully (after the migration) before it started failing. 4) What happens if you create a test database and restore bcf_work_type by itself, with and without data? -> I could test this, how would you suggest to do the backup/restore part, also pg_dump? > > So the output of these queries before the reindex is: > > db_name_hidden=# select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; > -[ RECORD 1 ]-------+-------- > indexrelid | 2006873 > indrelid | 1998823 > indnatts | 1 > indnkeyatts | 1 > indisunique | t > indnullsnotdistinct | f > indisprimary | t > indisexclusion | f > indimmediate | t > indisclustered | f > indisvalid | t > indcheckxmin | f > indisready | t > indislive | t > indisreplident | f > indkey | 1 > indcollation | 0 > indclass | 3124 > indoption | 0 > indexprs | > indpred | > > db_name_hidden =# \d bcf_work_type > > Table "name_hidden.bcf_work_type" > Column | Type | Collation | Nullable | Default > -----------+--------+-----------+----------+------------------------------------------- > id | bigint | | not null | nextval('bcf_work_type_id_seq'::regclass) > aml_score | bigint | | not null | > Referenced by: > TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > > > After the REINDEX command (REINDEX INDEX idx_376814_primary; ) this becomes: > > db_name_hidden=# select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; > -[ RECORD 1 ]-------+-------- > indexrelid | 2006873 > indrelid | 1998823 > indnatts | 1 > indnkeyatts | 1 > indisunique | t > indnullsnotdistinct | f > indisprimary | t > indisexclusion | f > indimmediate | t > indisclustered | f > indisvalid | t > indcheckxmin | f > indisready | t > indislive | t > indisreplident | f > indkey | 1 > indcollation | 0 > indclass | 3124 > indoption | 0 > indexprs | > indpred | > > db_name_hidden =# \d bcf_work_type > Table "name_hidden.bcf_work_type" > Column | Type | Collation | Nullable | Default > -----------+--------+-----------+----------+------------------------------------------- > id | bigint | | not null | nextval('bcf_work_type_id_seq'::regclass) > aml_score | bigint | | not null | > Indexes: > "idx_376814_primary" PRIMARY KEY, btree (id) > Referenced by: > TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT > > So the first result stays the same, in the description of the table now the index shows up... > > > -- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer> -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-01-28T16:17:06Z
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 done from the production db so I could have a playground for this situation. It's clear the situation is the same on the original and the backup copy. Whoa, pg_basebackup does not involve pg_dump. They are two different beasts, where pg_basebackup is a file based binary method and pg_dump/pg_restore is a logical method of issuing commands. So the restore method is definitely something that needs to be looked at. Even if in the production scenario pg_basebackup is not being used how the schema and data are being restored is important as that seems to be the step where information goes missing. > -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primary key index fail to create because it's not created, which makes sense. It's 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
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-01-28T16:31:30Z
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
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-02-11T09:05:08Z
Internal I know the initial thread was started a while ago, but as was explained there the restore was done purely to have a playground db for this specific issue. I know the difference between pg_dump and pg_restore. The issue is with pg_dump, not with pg_basebackup (as is proven as pg_basebackup and then the restore perfectly transfers the 'situation' as is between the production database and the playground database). I just did the dumps as requested, neither of them are showing the index create as expected. -----Original Message----- From: Adrian Klaver <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 done from the production db so I could have a playground for this situation. It's clear the situation is the same on the original and the backup copy. Whoa, pg_basebackup does not involve pg_dump. They are two different beasts, where pg_basebackup is a file based binary method and pg_dump/pg_restore is a logical method of issuing commands. So the restore method is definitely something that needs to be looked at. Even if in the production scenario pg_basebackup is not being used how the schema and data are being restored is important as that seems to be the step where information goes missing. > -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primary key index fail to create because it's not created, which makes sense. It's 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 Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-12T16:25:28Z
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 playground db for this specific issue. I know the difference between pg_dump and pg_restore. > The issue is with pg_dump, not with pg_basebackup (as is proven as pg_basebackup and then the restore perfectly transfers the 'situation' as is between the production database and the playground database). 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
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-02-13T08:08:29Z
Internal Ok, to do a small recap because indeed this thread has been extended for a while now. - The issue with the specific index was noted on a production database (after a datarefresh that partly failed because of the missing index). - To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a test instance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackup is no longer involved, everything is pg_dump based from here on onwards. - So this means the test pg_dumps where done with the index in a 'non-fuctional state'. As expected, the create statement of the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of the table). I hope this clears out any confusion. -----Original Message----- From: Adrian Klaver <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 playground db for this specific issue. I know the difference between pg_dump and pg_restore. > The issue is with pg_dump, not with pg_basebackup (as is proven as pg_basebackup and then the restore perfectly transfers the 'situation' as is between the production database and the playground database). 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 Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-13T16:09:21Z
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 because of the missing index). > > - To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a test instance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackup is no longer involved, everything is pg_dump based from here on onwards. > > - So this means the test pg_dumps where done with the index in a 'non-fuctional state'. As expected, the create statement of the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of the table). 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 -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-02-13T16:27:53Z
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 created in 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 because of the missing index). > > - To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a test instance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackup is no longer involved, everything is pg_dump based from here on onwards. > > - So this means the test pg_dumps where done with the index in a 'non-fuctional state'. As expected, the create statement of the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of the table). 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 Disclaimer <https://www.kbc.com/KBCmailDisclaimer> -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-13T17:32:47Z
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 not created 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>
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-03-05T13:23:57Z
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 keys 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 INSERT a 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 actually gets 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 not created 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> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-05T16:04:36Z
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 foreign keys 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 INSERT a 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 actually gets 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 not created 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? 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 -
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-03-06T10:31:04Z
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 thrown when the table is dumped. "Whatever process is corrupting the index" -> I think this was more of a one-off incident that happened somewhere in the past, 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 up until 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 foreign keys 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 INSERT a 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 actually gets 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 not created 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? 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 Disclaimer <https://www.kbc.com/KBCmailDisclaimer> -
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-06T16:03:07Z
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
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-03-09T14:12:34Z
Internal 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. -----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 error in the process. -- Adrian Klaver adrian.klaver@aklaver.com Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-09T14:52:54Z
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 is that 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
-
Re: Index (primary key) corrupt?
Greg Sabino Mullane <htamfids@gmail.com> — 2026-03-09T15:24:15Z
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
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-09T15:37:26Z
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
-
Re: Index (primary key) corrupt?
Greg Sabino Mullane <htamfids@gmail.com> — 2026-03-09T17:53:39Z
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
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-09T19:52:59Z
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
-
Re: Index (primary key) corrupt?
Greg Sabino Mullane <htamfids@gmail.com> — 2026-03-10T14:15:01Z
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
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-03-10T14:24:43Z
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<mailto:htamfids@gmail.com> On Mon, Mar 9, 2026 at 3:53 PM Adrian Klaver <adrian.klaver@aklaver.com<mailto: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 <https://www.kbc.com/KBCmailDisclaimer>
-
Re: Index (primary key) corrupt?
Greg Sabino Mullane <htamfids@gmail.com> — 2026-03-10T14:43:45Z
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
-
Re: Index (primary key) corrupt?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-10T15:09:34Z
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
-
RE: Index (primary key) corrupt?
Wim Rouquart <wim.rouquart@kbc.be> — 2026-04-09T08:31:25Z
Internal OK, Took the mailing list out of the loop because some logging needed to be provided. Reincluding it now because the actual issue has been identified. Putting the list back in so if anyone bumps into this one in the future, it might prove helpful. See Greg’s answer below (the relhasindex column of the pg_class dictionary table was indeed set to ‘false’ for that index, what caused this situation we will probably never find out). Thanks to Greg and Adrian for staying on top of this one. Cheers, Wim. From: Greg Sabino Mullane <htamfids@gmail.com> Sent: woensdag 18 maart 2026 17:42 To: Wim Rouquart <wim.rouquart@kbc.be> Cc: Adrian Klaver <adrian.klaver@aklaver.com> Subject: Re: Index (primary key) corrupt? The real sender of this external email is htamfids@gmail.com<mailto:htamfids@gmail.com> Ok, I can see the exact problem now in the logs. There is a section of pg_dump that tries to find the indexes, and it builds a list of oids to scan while doing so. In the logs you sent the unnest is an empty value '{}'. It should be '{1998823}'. Indeed, it shows up in other places properly, so there is something unique to that section of code. Looking deeper, that can fail to populated either because the table is "not interesting" (but it surely is by the other queries) or because it has no index. That in turn traces back to the relhasindex column of pg_class. My guess is that something has set that to 'f' when it should be 't'. Not the kind of corruption that can be detected by checksums or by anything like amcheck, as I'm pretty sure the index is there, but pg_class has been corrupted in a way to not allow pg_dump to find it. (New email arrives). Yep - the post reindex one now has the oid in the unnest, which suggests relhasindex was flipped back. Can you verify this theory with a: SELECT * FROM pg_class WHERE oid = 1998823; See if relhasindex is true of false. Is there any chance some tool/person has modified that field, perhaps as some sort of hack to temporarily disable indexes? You can also run this global sanity check. It should return no rows: select relname from pg_class c where relhasindex is false and exists (select 1 from pg_index where c.oid = indrelid); That problem table can be fixed without a REINDEX by simply doing: update pg_class set relhasinex=true where oid = 1998823; Disclaimer <https://www.kbc.com/KBCmailDisclaimer>