public inbox for [email protected]help / color / mirror / Atom feed
Index (primary key) corrupt? 4+ messages / 3 participants [nested] [flat]
* Index (primary key) corrupt? @ 2025-09-18 12:25 Wim Rouquart <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Wim Rouquart @ 2025-09-18 12:25 UTC (permalink / raw) To: [email protected] <[email protected]> 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; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index (primary key) corrupt? @ 2025-09-18 15:50 Ron Johnson <[email protected]> parent: Wim Rouquart <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Ron Johnson @ 2025-09-18 15:50 UTC (permalink / raw) To: [email protected] <[email protected]> On Thu, Sep 18, 2025 at 10:58 AM Wim Rouquart <[email protected]> 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index (primary key) corrupt? @ 2025-09-18 16:01 Adrian Klaver <[email protected]> parent: Wim Rouquart <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2025-09-18 16:01 UTC (permalink / raw) To: Wim Rouquart <[email protected]>; [email protected] <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Index (primary key) corrupt? @ 2025-09-19 09:40 Wim Rouquart <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Wim Rouquart @ 2025-09-19 09:40 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; [email protected] <[email protected]> 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; ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-09-19 09:40 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-09-18 12:25 Index (primary key) corrupt? Wim Rouquart <[email protected]> 2025-09-18 15:50 ` Ron Johnson <[email protected]> 2025-09-18 16:01 ` Adrian Klaver <[email protected]> 2025-09-19 09:40 ` Wim Rouquart <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox