From: Wim Rouquart <wim.rouquart@kbc.be>
To: Adrian Klaver <adrian.klaver@aklaver.com>, Greg Sabino Mullane
	<htamfids@gmail.com>
CC: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: RE: Index (primary key) corrupt?
Thread-Topic: Index (primary key) corrupt?
Thread-Index:  Adwol1a+rcvHuOuQQceM5FEl8oW9YQAHiEkAACShEdAAB3jzAAAAjjoABGxqcgADNfAWkAALH2WAEdn63xAACBjIgAAnIcgwAAutSwAAITAcAA==
Date: Wed, 11 Feb 2026 09:05:08 +0000
Message-ID:  <AS2PR05MB107541E760AECB8639BB968B5EF63A@AS2PR05MB10754.eurprd05.prod.outlook.com>
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>
 <58221272-d684-4799-a113-d5a5031a0b05@aklaver.com>
In-Reply-To: <58221272-d684-4799-a113-d5a5031a0b05@aklaver.com>
Accept-Language: nl-BE, en-US
Content-Language: en-US
msip_labels:  MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_Enabled=True;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_SiteId=64af2aee-7d6c-49ac-a409-192d3fee73b8;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_SetDate=2026-01-29T08:07:22.0000000Z;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_Name=d44a7eb9-e308-4cb8-ad88-b50d70445f3a;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_ContentBits=3;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_Method=Standard
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=kbc.be;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: AS2PR05MB10754:EE_|PAWPR05MB11010:EE_
x-ms-office365-filtering-correlation-id: 1d4f2809-4aec-4ad7-249c-08de694ca7d4
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam:  BCL:0;ARA:13230040|376014|19092799006|366016|1800799024|7053199007|38070700021;
x-microsoft-antispam-message-info:  =?us-ascii?Q?jeI16+cB0ezwf5ebxIwXhh+c3LB4zz4fIZeu27V756oezm4hwWTB7KQc6u2w?=
 =?us-ascii?Q?qZZA2WV3KVvtuhwLwBlRtDjOdANhhrKqhUDEtdYA47PMaIPN2K+fn/jIUISh?=
 =?us-ascii?Q?NSPV0h/HiKA8iFZ4UZRzcclxHXk9jYq8sJA74wsSon79m2Qak40Ok4cs5YiC?=
 =?us-ascii?Q?sGwTa+3/w8aMIRd4PPYBPeeqvkGiyQPT/pX1KJPJYzmuf0efpPoxc1LgPIiW?=
 =?us-ascii?Q?avRLR//BLTIYX6eusmX1hVXomF81q6PIA3qIAEa74HQrF2KA+Anqwc2ZRGr0?=
 =?us-ascii?Q?AY4oBhfbrDMDbkmKd0ChuzOV7/NWkeu89sfBbvUccYbRHtuTxhFrRuPY1Kf6?=
 =?us-ascii?Q?9tUywCVKKWzFlh/tL4/JaQbCfKLe+SuMLTdLo0d1im1GCsygXeM845TTrPyn?=
 =?us-ascii?Q?8xuzJFmx5XrBIymeGo+/ZAcQMt1BvnvLYAO5zLhaLAHprEv1nMzos6rvPKUN?=
 =?us-ascii?Q?rhbIYUeLWzEmVgxpS9uae7wVVgbxrhaGQvFDMBn4b4soKQd+M96QVdN7geqN?=
 =?us-ascii?Q?4e4y+IFOkdpwOGvHC0d3JPjYPoEkp0nElSgYHCoQX2j5FUL3sGf5vRj85ugS?=
 =?us-ascii?Q?CdJZfRF4Bh3tAEb7UBtwHPTMMDr9sjAnMbzWMePWIrMDISV0cfjZKS67z+zV?=
 =?us-ascii?Q?0531WeajTYeFlV1g9wmiKOtB4q6xbEG7+dQ0Q07BKYf4p7wfn60kUwhtzYI0?=
 =?us-ascii?Q?7h4LyQYhsUs/S9rnDuci3ixoKH4CeePlbOHDU68zH1AproX38UwCV9MIkEYs?=
 =?us-ascii?Q?EL0p3R2l4t+f/pcxvNbOGHlxlSiPTLIKPONV3Av2RCCtH76riLmcHmdsgh3E?=
 =?us-ascii?Q?beLJwjM4n5i64bvpSHUqhRhPOGiefYey761brSkVh1HHfNjhrwBmLKMyxQRD?=
 =?us-ascii?Q?v0MpW2cBUmHOP1FXl1tRBVHNG2cb8UwLd/OG5pt4Jzr76J9V8vCQ8iW0FBVT?=
 =?us-ascii?Q?LbIR44hI9bxuCvGeTsskBq7PKpo/oE8yWNOZrBP6boe58JZjLVPsLUvNu6L3?=
 =?us-ascii?Q?/uzPLuwoV7B1sc4yKCaBx15yVVNIE1aPm8GBprIIbV7rCX3dxO9Z9PHtMcXh?=
 =?us-ascii?Q?7J05Mv1gAMbg+hkN2lil7VJRu5zeKV/8T3lySFCIkbtuZ1xxh2M0YEIi9dHg?=
 =?us-ascii?Q?KWK5vTgJ41DCmjrqgk9Tfz5WbgjGBIGeYmDn9OmVOthnhNRDgx5xUVM5yYa4?=
 =?us-ascii?Q?pHQUTrkwcB4ONtkwo8FOAJJ5xJy4n4Gi7X6tb1x+iMiFC2lL1onTwXaXvoV+?=
 =?us-ascii?Q?XojIwadq/vQIMrFRuOWZWJSrPtDC7ge7c0eCz+pVk7dIXI6CKZuuowRRe2bh?=
 =?us-ascii?Q?OpSF8UXdXOGCSPodMrJ6b9SBBIdtb6UWsVw2MInwusvbrkYcgJvy5DaS9Hx7?=
 =?us-ascii?Q?w7NwvDaE8Fj9NX4HE3UUesHFbFIHiFBl8U93CZutmGhCr0VDGNukyQWUdeky?=
 =?us-ascii?Q?dXXcxoQCFclQRMmFJsEofqgskFTu5VM6/KXrE/48jl5j2eraL3sBqins61np?=
 =?us-ascii?Q?Z2Qs4r1qJFFgxJNys4OxiZSsNccdlQDnzsr2myRlUvMPWB/fHtb1o0Xom5N/?=
 =?us-ascii?Q?Nmaj7Tw+RZ8pC+hzxqFSaoGERQVa5F1+5SRwBoqahPNbfSsSHpeNd2/+j8Rs?=
 =?us-ascii?Q?uly7zIMkpb0/qiB6H0jDAiA=3D?=
x-forefront-antispam-report:  CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AS2PR05MB10754.eurprd05.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(19092799006)(366016)(1800799024)(7053199007)(38070700021);DIR:OUT;SFP:1101;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:  =?us-ascii?Q?fKpsV3DZszwzMhAW8ORMBBK+P0VpM/djf24weEInecRJkC0ZjfjdLA21Vi2i?=
 =?us-ascii?Q?TRB6sM6ShC4if/rY1vHbWOtPHykgw0yRJ19ePNqQ1fUnen19RSzv5/z4MYkF?=
 =?us-ascii?Q?1VYvMgOUgSMd4AdCj+KcPRDtDW3zcZ0JFirb5+cb2lKhyiw+IyYK5boRZecm?=
 =?us-ascii?Q?lRQVBOSvXhjTMpJFnxlqj6bh0xhd5ruJRHVoO5/dnchDqEbxt64exTah+P5Z?=
 =?us-ascii?Q?VoaYOJ2BGABAr77e62p9X5Unsq1fe0zBf49fCrTeYQUpYAVjawuDssBbUvPp?=
 =?us-ascii?Q?DPR+JL6PRYqJToxxEQ07rsf+g12kKp9l6adWVtlzX1Nnc/AO+IPShoKpgi3L?=
 =?us-ascii?Q?QEBOT5PPm0GxtxhEOVq3hoBQQWtZmTa4QE5iwrGKkF7FJTbfsi+aUAJ8WBKW?=
 =?us-ascii?Q?Gyllf5ISm1y3XsW999/FIPPAEvhzhl54U7761Irw95GG7gTWRWu6r+GigqjY?=
 =?us-ascii?Q?tV8hCWS9gqYcYfm/nQkBPGWLiNj7Pudz7yxN1hPKIOAxHND9P0AVxaJdu9Xv?=
 =?us-ascii?Q?/4p72yQUtCqECBlmoHgtBZIJ7uAzNMdaPuCINyF3KmRu9sPXCjVGhCR9edt+?=
 =?us-ascii?Q?EsFtblgUUVJV6HARSLXLZJqVdxdbqDoswbhcMFIq1De6kCidpaSIUjPHv0gR?=
 =?us-ascii?Q?MlU3VthvPW/Cy3amV56KCq2SRnNCFydFnHffmfa6E8VEzlaRLG2hAgcOLYav?=
 =?us-ascii?Q?HSoTGWZjtIL0UJKd68s/0iEoSUZBck5Eubv/2jvQFjYB6aTjmGEFjvQ5h4Et?=
 =?us-ascii?Q?NJear4miBqFdTU5NdY9d8I5wZWAkj3vZV6dK/7Vgo2YMmZXoG1mDnnI96jUw?=
 =?us-ascii?Q?3hXYtGku3tapPmEDLzFnspOXOESC83HfMwOddHW5/4Kfma239Xs6Buqo5Cn3?=
 =?us-ascii?Q?27Jh4CZn1yz023xaCUU+K+mNnhPB4a0i++jzeWkpdYyENFzc9KZbpmP/U9I7?=
 =?us-ascii?Q?iCc6DS3QhJmX9+5ctWWMyiuM4ajA2Y5eKPQfNGjngGLKbqKOsHsPMujAm1uI?=
 =?us-ascii?Q?XM7/Cm67ml5KLvEXr7H/PJngPDzMvoA9qaFZQ7C47X+Amya23uEgJCDbECw7?=
 =?us-ascii?Q?M0qoH3ys9dIYEWlhxEjZrtw1ZLE80HFs1Ytx0fSGGA/cRC9oSdH+opKRNrfr?=
 =?us-ascii?Q?fzTjw2WhjF+lu+goP8jbz+kelJrJGFzyDGeVLc4YWqu7jt7j62b59MIm/vVF?=
 =?us-ascii?Q?VxixpMBqFFu4A0CaU3XKGhgK8FCXfVM5VvaGiwInzNQwKEv2DxWLzv70tyFt?=
 =?us-ascii?Q?fItFUrtbSIuyiNgpUW5w0lPdRs9Cx8ZVDqa/zp1ASV76tak/G87+Kp/yEoBS?=
 =?us-ascii?Q?54H9WSYfAvZ9czM7fU4Qh5WFq1zmjF0Q0WKlm0y7YjLgnEB02CVNY/kTq2Fq?=
 =?us-ascii?Q?6SmaJTGl9Q59bMIjlctLrzrR+h9MEh3ixY3S3i07DAkHhfluFOVkwIKVeiCy?=
 =?us-ascii?Q?fbkZQ6hNY6PfFoCDCMIpKTYh8FEfpkEEVytWtH2wSgWtnNu19oxJxNMSiv8B?=
 =?us-ascii?Q?L1yQy6YxO1rE1TKdbuGLx+HB+KrOL6W6yo9r2UywyG4ycWccIvcE2dzltEL1?=
 =?us-ascii?Q?eBGzV8eDHJ52i5ThwLuv34r248lMBc8mkEYhUrtFbnK/4o0eLW2b3+kJbobJ?=
 =?us-ascii?Q?loeiO+wg5HSF3dofHYthhF98GiGNFaTYS/SI7EdPBFsMVk0wXl13JDl88kLz?=
 =?us-ascii?Q?lTiKN4lG3AGpyoeZ7wOOuegdp9yARSZBiqbnIXI4HIyA/OESocJut5BJ2VZO?=
 =?us-ascii?Q?TFmMR7d5eQ=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
X-OriginatorOrg: kbc.be
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: AS2PR05MB10754.eurprd05.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 1d4f2809-4aec-4ad7-249c-08de694ca7d4
X-MS-Exchange-CrossTenant-originalarrivaltime: 11 Feb 2026 09:05:08.6949
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 64af2aee-7d6c-49ac-a409-192d3fee73b8
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: YU/fN6HVLBNUQEBhCfyWkCQayyz7OxhsSxS1LW0Yi5xAJ7TEPjb07LMYT03uVOYtonkjxTIVLcvYjFYTDUhn2A==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PAWPR05MB11010
Content-Transfer-Encoding: quoted-printable
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/AS2PR05MB107541E760AECB8639BB968B5EF63A%40AS2PR05MB10754.eurprd05.prod.outlook.com>
Precedence: bulk

Internal

I know the initial thread was started a while ago, but as was explained the=
re the restore was done purely to have a playground db for this specific is=
sue. 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_baseb=
ackup and then the restore perfectly transfers the 'situation' as is betwee=
n the production database and the playground database).

I just did the dumps as requested, neither of them are showing the index cr=
eate as expected.

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: woensdag 28 januari 2026 17:17
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 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 c=
atalog queries...
>
> Questions:
>
> 1) What is the restore command being used?
>
> -> It's just an untar of the full backup created with pg_basebackup. No n=
eed 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 definitel=
y 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 b=
ecause it's not created, which makes sense. It's not created because it's n=
ot 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 pro=
blems.

In a pg_dump/pg_restore cycle I don't know how a user created index could b=
e present in the system catalog without also being present in the pg_dump c=
ommands or throwing some sort of error.


>
> 4) What happens if you create a test database and restore bcf_work_type b=
y 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_ty=
pe.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

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