Index (primary key) corrupt?

Started by Wim Rouquart7 months ago45 messagesgeneral
Jump to latest
#1Wim Rouquart
wim.rouquart@kbc.be

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&gt;

#2Ron
ronljohnsonjr@gmail.com
In reply to: Wim Rouquart (#1)
Re: Index (primary key) corrupt?

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!

#3Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#1)
Re: Index (primary key) corrupt?

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&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

#4Wim Rouquart
wim.rouquart@kbc.be
In reply to: Ron (#2)
RE: Index (primary key) corrupt?

Internal

Hello,

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

When doing a REINDEX the issue is fixed.

As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).

I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?

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&gt;

#5Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#3)
RE: Index (primary key) corrupt?

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&gt;

#6Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#5)
Re: Index (primary key) corrupt?

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

#7Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#6)
RE: Index (primary key) corrupt?

Internal

Hello,

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

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&gt;

#8Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#7)
Re: Index (primary key) corrupt?

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

#9Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#4)
Re: Index (primary key) corrupt?

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&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

#10David G. Johnston
david.g.johnston@gmail.com
In reply to: Wim Rouquart (#4)
Re: Index (primary key) corrupt?

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.

#11Rob Sargent
robjsargent@gmail.com
In reply to: David G. Johnston (#10)
Re: Index (primary key) corrupt?

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?

Show quoted text
#12Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Rob Sargent (#11)
Re: Index (primary key) corrupt?

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:

/messages/by-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A@AS2PR05MB10754.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

#13Rob Sargent
robjsargent@gmail.com
In reply to: Adrian Klaver (#12)
Re: Index (primary key) corrupt?

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:

/messages/by-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A@AS2PR05MB10754.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.

#14Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#8)
RE: Index (primary key) corrupt?

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&gt;

#15Wim Rouquart
wim.rouquart@kbc.be
In reply to: Rob Sargent (#11)
RE: Index (primary key) corrupt?

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&gt;

#16Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#14)
Re: Index (primary key) corrupt?

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

#17Greg Sabino Mullane
greg@turnstep.com
In reply to: Wim Rouquart (#7)
Re: Index (primary key) corrupt?

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

#18Wim Rouquart
wim.rouquart@kbc.be
In reply to: Greg Sabino Mullane (#17)
RE: Index (primary key) corrupt?

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&lt;https://altered.secure4u.kbc.be/https:/www.crunchydata.com&gt;
Enterprise Postgres Software Products & Tech Support

Disclaimer <https://www.kbc.com/KBCmailDisclaimer&gt;

#19Greg Sabino Mullane
greg@turnstep.com
In reply to: Wim Rouquart (#18)
Re: Index (primary key) corrupt?

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

#20Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#18)
Re: Index (primary key) corrupt?

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:

/messages/by-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A@AS2PR05MB10754.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&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

#21Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#20)
#22Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#21)
#23Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#22)
#24Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#23)
#25Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#23)
#26Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#24)
#27Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#26)
#28Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#27)
#29Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#28)
#30Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#29)
#31Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#30)
#32Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#31)
#33Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#32)
#34Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#33)
#35Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#34)
#36Wim Rouquart
wim.rouquart@kbc.be
In reply to: Adrian Klaver (#35)
#37Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Wim Rouquart (#36)
#38Greg Sabino Mullane
greg@turnstep.com
In reply to: Wim Rouquart (#36)
#39Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Greg Sabino Mullane (#38)
#40Greg Sabino Mullane
greg@turnstep.com
In reply to: Adrian Klaver (#39)
#41Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Greg Sabino Mullane (#40)
#42Greg Sabino Mullane
greg@turnstep.com
In reply to: Adrian Klaver (#41)
#43Wim Rouquart
wim.rouquart@kbc.be
In reply to: Greg Sabino Mullane (#42)
#44Greg Sabino Mullane
greg@turnstep.com
In reply to: Wim Rouquart (#43)
#45Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Greg Sabino Mullane (#44)