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 <[email protected]> Sent: woensdag 28 januari 2026 17:17 To: Wim Rouquart <[email protected]>; Greg Sabino Mullane <[email protected]> Cc: [email protected] Subject: Re: Index (primary key) corrupt? The real sender of this external email is [email protected] 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 [email protected] Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
