MIME-Version: 1.0
References: <CAJ4rSwspyYBppQS701C=BVFX2yrP1JErgDCZXbC15BQh2zAS1Q@mail.gmail.com>
 <CAKAnmmK47O_rYDB4iPe=K3QiqzJcCnnxCoYs_40Q1YWb=x0nDg@mail.gmail.com>
 <CAJ4rSwvsRJhwfuem0vsws1WwRZPN8Ju2Nw0e0Oc2j7n=MXUhMg@mail.gmail.com> <CAKAnmmK9Fjb5YngH4BSb8Lmk3qh10nw=1M3xdcLatr9-U-f-Ug@mail.gmail.com>
In-Reply-To: <CAKAnmmK9Fjb5YngH4BSb8Lmk3qh10nw=1M3xdcLatr9-U-f-Ug@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 23 Oct 2025 13:56:58 -0400
Message-ID: <CANzqJaCiDH16CRjMPAY6eOVcrWtc91RWdZcZOyanapNDpyeOKw@mail.gmail.com>
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9
 (PostgreSQL 11 streaming replication)
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007d07460641d72980"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCiDH16CRjMPAY6eOVcrWtc91RWdZcZOyanapNDpyeOKw%40mail.gmail.com>
Precedence: bulk

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

On Thu, Oct 23, 2025 at 11:21=E2=80=AFAM Greg Sabino Mullane <htamfids@gmai=
l.com>
wrote:

>
>>    -
>>
>>    *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 a=
voiding
>> 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.
>

Last year. I did a dump/restore of a 4.3TB (inclusive of indices; heavy on
poorly-compressible BYTEA) database from RHEL6 + 9.6.24 to RHEL 8 +
14.latest.  It took just under 11 hours.

Gzip Level =3D 1
Remote database size: 4307406 MB
RemoteThreads: 16
LocalThreads: 24
SharedBuffs: 32 GB
MaintWorkMem: 3 GB
CheckPoint: 30 min
MaxWalSize: 36 GB
WalBuffs: 128 MB

Both systems were SAN-attached ESX VMs on the same virtual network

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Oct 23, 2025 at 11:21=E2=80=AFAM =
Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmai=
l.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container">=
<blockquote 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 cla=
ss=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr"><div dir=3D"ltr"><div><ul><li><p><strong>Acceptable downtime:</s=
trong> ~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></div></blockquote><div><br></di=
v><div>Last year. I did a dump/restore of a 4.3TB (inclusive of indices; he=
avy on poorly-compressible BYTEA) database from RHEL6=C2=A0+ 9.6.24 to RHEL=
 8=C2=A0+ 14.latest.=C2=A0 It took just under 11 hours.</div><div><br></div=
><div>Gzip Level =3D 1</div><div>Remote database size: 4307406 MB<br>Remote=
Threads: 16<br>LocalThreads: 24<br>SharedBuffs: 32 GB<br>MaintWorkMem: 3 GB=
<br>CheckPoint: 30 min<br>MaxWalSize: 36 GB<br>WalBuffs: 128 MB<br></div><d=
iv><br></div><div>Both systems were SAN-attached ESX VMs on the same virtua=
l network</div><div><br></div></div><span class=3D"gmail_signature_prefix">=
-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">D=
eath to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m =
still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div>=
</div></div>

--0000000000007d07460641d72980--