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
ofthe 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_basebackupis 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
statementof the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of
thetable).
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
playgrounddb 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
transfersthe '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
usedthat 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
iterationsof 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>
