MIME-Version: 1.0
References: <CAJ4rSwspyYBppQS701C=BVFX2yrP1JErgDCZXbC15BQh2zAS1Q@mail.gmail.com>
 <CAKAnmmK47O_rYDB4iPe=K3QiqzJcCnnxCoYs_40Q1YWb=x0nDg@mail.gmail.com> <CAJ4rSwvsRJhwfuem0vsws1WwRZPN8Ju2Nw0e0Oc2j7n=MXUhMg@mail.gmail.com>
In-Reply-To: <CAJ4rSwvsRJhwfuem0vsws1WwRZPN8Ju2Nw0e0Oc2j7n=MXUhMg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 23 Oct 2025 11:20:38 -0400
Message-ID: <CAKAnmmK9Fjb5YngH4BSb8Lmk3qh10nw=1M3xdcLatr9-U-f-Ug@mail.gmail.com>
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9
 (PostgreSQL 11 streaming replication)
To: Bala M <krishna.pgdba@gmail.com>
Cc: "adrian.klaver@aklaver.com" <adrian.klaver@aklaver.com>, chris+google@qwirx.com, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d38fc10641d4fbb6"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmK9Fjb5YngH4BSb8Lmk3qh10nw%3D1M3xdcLatr9-U-f-Ug%40mail.gmail.com>
Precedence: bulk

--000000000000d38fc10641d4fbb6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

>
>
>    -
>
>    *Acceptable downtime:* ~1 day
>    -
>
>    *Logical replication:* Not feasible due to the number of schemas,
>    tables, and overall data volume
>
> I'm not sure why this is not feasible. Can you expand on this?

* For a *15 TB database* with roughly *1 day downtime*, what would be the
> most reliable approach to migrate from *RHEL 7 =E2=86=92 RHEL 9* while av=
oiding
> collation/index corruption issues?


pg_dump is the most reliable, and the slowest. Keep in mind that only the
actual data needs to move over (not the indexes, which get rebuilt after
the data is loaded). You could also mix-n-match pg_logical and pg_dump if
you have a few tables that are super large. Whether either approach fits in
your 24 hour window is hard to say without you running some tests.

* Would using *pg_upgrade* (with --check and --clone options) be safe when
> moving between OS versions with different glibc libraries?


No, you cannot use pg_upgrade for this. It can move your system across
Postgres versions, but across servers/operating systems.

* If we temporarily remain on PostgreSQL 11, is it *mandatory to rebuild
> all indexes* after restoring the base backup on RHEL 9 to ensure data
> consistency? Would running REINDEX DATABASE across all databases be
> sufficient?


Yes, and yes.

* Are there any *community-tested procedures or best practices* for
> migrating large (15 TB+) environments between RHEL 7 and RHEL 9 with
> minimal downtime?


Yes - logical replication is both battle-tested and best practice for such
an upgrade. But with such a large downtime window, investigate pg_dump to
v18. You can find a large table and dump just that one table to start
getting some measurements, e.g. run from the new server:

pg_dump -h my_rhel7_server -d mydb -t mybigtable | psql -h localhost -d
mydb -f -

Make sure log_min_duration_statement is set on the new server to help you
see how long each step takes.


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--000000000000d38fc10641d4fbb6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">=
<div><ul><li><p><strong>Acceptable downtime:</strong> ~1 day</p>
</li>
<li>
<p><strong>Logical replication:</strong> Not feasible due to the number of =
schemas, tables, and overall data volume</p></li></ul></div></div></div></b=
lockquote><div>I&#39;m not sure why this is not feasible. Can you expand on=
 this?</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
">* For a <strong>15 TB database</strong> with roughly <strong>1 day downti=
me</strong>, what would be the most reliable approach to migrate from <stro=
ng>RHEL 7 =E2=86=92 RHEL 9</strong> while avoiding collation/index corrupti=
on issues?</blockquote><div><br></div><div>pg_dump is the most reliable, an=
d the slowest. Keep in mind that only the actual data needs to move over (n=
ot the indexes, which get rebuilt after the data is loaded). You could also=
 mix-n-match pg_logical and pg_dump if you have a few tables that are super=
 large. Whether either approach fits in your 24 hour window is hard to say =
without you running some tests.</div><div><br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">* Would using <strong>pg_upgrade</strong> (with =
<code>--check</code> and <code>--clone</code> options) be safe when moving =
between OS versions with different glibc libraries?</blockquote><div><br></=
div><div>No, you cannot use pg_upgrade for this. It can move your system ac=
ross Postgres versions, but across servers/operating systems.</div><div><br=
></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex">* If we temporaril=
y remain on PostgreSQL 11, is it <strong>mandatory to rebuild all indexes</=
strong> after restoring the base backup on RHEL 9 to ensure data consistenc=
y? Would running <code>REINDEX DATABASE</code> across all databases be suff=
icient?</blockquote><div><br></div><div>Yes, and yes.=C2=A0</div><div><br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">* Are there any <str=
ong>community-tested procedures or best practices</strong> for migrating la=
rge (15 TB+) environments between RHEL 7 and RHEL 9 with minimal downtime?<=
/blockquote><div><br></div><div>Yes - logical replication is both battle-te=
sted and best practice for such an upgrade. But with such a large downtime =
window, investigate pg_dump to v18. You can find a large table and dump jus=
t that one table to start getting some measurements, e.g. run from the new =
server:</div><div><br></div><div>pg_dump -h my_rhel7_server -d mydb -t mybi=
gtable | psql -h localhost -d mydb -f -</div><div><br></div><div>Make sure =
log_min_duration_statement is set on the new server to help you see how lon=
g each step takes.</div><div><br></div><div><br></div></div><div dir=3D"ltr=
" class=3D"gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><div>Greg</d=
iv><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.c=
runchydata.com" target=3D"_blank">https://www.crunchydata.com</a></div><div=
>Enterprise Postgres Software Products &amp; Tech Support</div><div><br></d=
iv></div></div></div>

--000000000000d38fc10641d4fbb6--


Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vDkyn-00F9YE-F1
	for pgsql-general@arkaria.postgresql.org; Tue, 28 Oct 2025 14:47:20 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vDkyl-00DRcI-B9
	for pgsql-general@arkaria.postgresql.org; Tue, 28 Oct 2025 14:47:18 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <htamfids@gmail.com>)
	id 1vDkyk-00DRcA-VL
	for pgsql-general@lists.postgresql.org; Tue, 28 Oct 2025 14:47:17 +0000
Received: from mail-io1-xd36.google.com ([2607:f8b0:4864:20::d36])
	by makus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
	(Exim 4.96)
	(envelope-from <htamfids@gmail.com>)
	id 1vDkyi-004DrV-1K
	for pgsql-general@lists.postgresql.org;
	Tue, 28 Oct 2025 14:47:17 +0000
Received: by mail-io1-xd36.google.com with SMTP id ca18e2360f4ac-9444425cedcso378923439f.1
        for <pgsql-general@lists.postgresql.org>; Tue, 28 Oct 2025 07:47:16 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20230601; t=1761662835; x=1762267635; darn=lists.postgresql.org;
        h=cc:to:subject:message-id:date:from:in-reply-to:references
         :mime-version:from:to:cc:subject:date:message-id:reply-to;
        bh=jupzQKtUPUSbW+jyeIpZ4la1wk8yN8C6pzrpMVXH6VQ=;
        b=XkqgmlZzvibHhq7KhFR/Kg43qw5n0J3Tyy38YOxAYyjd53bcLb+TtsV8Eo3hDOsqMh
         IHsWqA8fJRFkNK8+5HNmbGs22ygAxwGgAC2O5wu8q56oQfObLvKQpO+NB29OMNPKvFDV
         YP694jRmdwEBUHCMwot9yAUEIuthIjQzg6zSKUaQRewbID3UiFa+cVy68uPqZpWy7OyR
         pGOgjC2swzViLPyD2f11GH26jO/yK0TGzz8VMGp5XnZJoY6BCkAEwV03X11juQkJXXwV
         tCxZX4/bZa4vF0GHmLzlV1d5W32d/Fbsna2zPusSI6Rcxjp5egZRvrUs0vqG3jke/T/m
         kfYw==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20230601; t=1761662835; x=1762267635;
        h=cc:to:subject:message-id:date:from:in-reply-to:references
         :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id
         :reply-to;
        bh=jupzQKtUPUSbW+jyeIpZ4la1wk8yN8C6pzrpMVXH6VQ=;
        b=xOu3Tr6IIfYkqOZ+E9Pgti11UtLnXXCLkICYJAFJnt3cRtfrQYfx21uVAo8/SPUH2n
         KGeBVn+iyZafuzH4Xss60ThxW6Z6ZiTyQkDqztwukMYlkohaZ6bIhNpUFGMdmJtOb/Tx
         ulhdN6tfRuJgccBev4LQNScarr33npVXqgeTzyoU955fwMn7E1T0nCmIAQkOiO19BD3x
         ePMG2Z+CoUvQXIAP+o1cKe4aLSrgtEeBCEViOGjC1+vdvhZ2om356fKq7TbQgi2mqyKZ
         2EAwGq75G8UAQwMbrrFr9SQsBOeJBTRONsjg6llYb6b4xEHjt13vSP1XiVOTv0NUM+MG
         pTyQ==
X-Forwarded-Encrypted: i=1; AJvYcCUXJ67QTVPIfNZIf0xz1dWhgxJnT3TXbYY1tVVDsd9tIEGD16+qxjo7C1yiHImBE0u2GNVxG5BEwmx7IN8+@lists.postgresql.org
X-Gm-Message-State: AOJu0Yy4nXvQYWvXtO1mTByi69bP1qpoGht/0knaeb8M6vMv1W+vBr7T
	r6Xcbdizi3HV1zrF+QfBGR0zGA3UZNGuHVUh8yMfXvP3TSb8PLHT0/vs1wlrMQrzjiZuHkeqFv2
	yUSYT3cmWF0VB54Ir/jJX7WxQv45EiC1uu+qr
X-Gm-Gg: ASbGnct3QsUqpC1aChobc+sqXzhFY1W1Nhyo86AxMV1QA0YbW3mifSQQ3QjLx62W/4O
	Kub/BhYuWinBN32pdBhYESjWNF7CAPJu5U9reG7OIgt6XZBCjdg63s4xBDmYjOkPsGEZnRYfoyf
	caxGXVFfBSamjyVUThS+oA0Dt0n9OniJalqqbci6XwdpOYz0Qw9aQRgG+5nS7DbMI2cvr8KSXxt
	BX1ejCfLUja3/H4S2G92E/NMquLvwnILmqJw0ym6/FeoFOmzVPKvqLA0xSjKIymOxEOWhgcK/Dz
	pUSXsjTEjgtE8E8Hvg==
X-Google-Smtp-Source: AGHT+IHQNqPeu0zvNUpo9TwYjuZdeSakY2xJVQZZF7FaZH0IbI23pKBzJB6t8YuhIoq5RHZthNTmedipP+Lr49Nme5k=
X-Received: by 2002:a05:6e02:1a03:b0:431:b1fe:147f with SMTP id
 e9e14a558f8ab-4320f771042mr63324155ab.18.1761662835319; Tue, 28 Oct 2025
 07:47:15 -0700 (PDT)
MIME-Version: 1.0
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>
In-Reply-To: <AS2PR05MB10754BFE319E2594C9E076EE2EFFDA@AS2PR05MB10754.eurprd05.prod.outlook.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 28 Oct 2025 10:46:39 -0400
X-Gm-Features: AWmQ_bmdxMgbbDKYvffU_VtP9lPAiTk4Gcej2HouJeg2dVIdWgSlE-xffESGOWE
Message-ID: <CAKAnmmK9uKAcerhseNg6FSDOnMWmivM5ctUiTAdc1kobq94Dqw@mail.gmail.com>
Subject: Re: Index (primary key) corrupt?
To: Wim Rouquart <wim.rouquart@kbc.be>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000836a890642391710"
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/CAKAnmmK9uKAcerhseNg6FSDOnMWmivM5ctUiTAdc1kobq94Dqw%40mail.gmail.com>
Precedence: bulk

--000000000000836a890642391710
Content-Type: text/plain; charset="UTF-8"

Could you run the SELECT on pg_index after the REINDEX to see if there is
any difference?

--000000000000836a890642391710
Content-Type: text/html; charset="UTF-8"

<div dir="ltr">Could you run the SELECT on pg_index after the REINDEX to see if there is any difference?</div>

--000000000000836a890642391710--