Feedback-ID: i76984098:Fastmail
Message-ID: <58221272-d684-4799-a113-d5a5031a0b05@aklaver.com>
Date: Wed, 28 Jan 2026 08:17:06 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Index (primary key) corrupt?
To: Wim Rouquart <wim.rouquart@kbc.be>,
 Greg Sabino Mullane <htamfids@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <AS2PR05MB1075477BA334D4DA262AD75BFEF16A@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <1bfa0b6b-11a8-458f-a3f3-3f86574abc1d@aklaver.com>
 <AS2PR05MB107548567EEDAAB3AF74A6C59EF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <f3ea70b8-c822-4b0f-b564-5e73d34188d4@aklaver.com>
 <AS2PR05MB10754A51AB7FC85161189FF7CEF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <CAKAnmmLNaQ9NnjP=-kMj4t6wjt3+5nmMbQc2+F2Ekv_=zJWs5w@mail.gmail.com>
 <AS2PR05MB10754BFE319E2594C9E076EE2EFFDA@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <b812d3b4-b471-479c-b22a-2380fd3ab984@aklaver.com>
 <AS2PR05MB107549DDE42DC0B8E31CB52BFEF90A@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <5043ff13-2f39-4a80-a8f8-6765746b6a89@aklaver.com>
 <AS2PR05MB10754EE81F34146EE8972713EEF91A@AS2PR05MB10754.eurprd05.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <AS2PR05MB10754EE81F34146EE8972713EEF91A@AS2PR05MB10754.eurprd05.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/58221272-d684-4799-a113-d5a5031a0b05%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.com