RE: Index (primary key) corrupt?

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(dot)klaver(at)aklaver(dot)com>
Sent: woensdag 28 januari 2026 17:17
To: Wim Rouquart <wim(dot)rouquart(at)kbc(dot)be>; Greg Sabino Mullane <htamfids(at)gmail(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Index (primary key) corrupt?

The real sender of this external email is adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com

Disclaimer <https://www.kbc.com/KBCmailDisclaimer>