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> <CA+bJJbwSZg6fiQ78N-_2NwRBbp=6e5WjnSX9SPa_DOxnaU63Vg@mail.gmail.com>
In-Reply-To: <CA+bJJbwSZg6fiQ78N-_2NwRBbp=6e5WjnSX9SPa_DOxnaU63Vg@mail.gmail.com>
From: Bala M <krishna.pgdba@gmail.com>
Date: Wed, 5 Nov 2025 11:57:22 +0530
Message-ID: <CAJ4rSwstZoVgVjbHeDNVq+7eBWCVZSXjNMRpzB4QFjArZT0Hcg@mail.gmail.com>
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9
 (PostgreSQL 11 streaming replication)
To: Greg Sabino Mullane <htamfids@gmail.com>, Francisco Olarte <folarte@peoplecall.com>
Cc: "adrian.klaver@aklaver.com" <adrian.klaver@aklaver.com>, chris+google@qwirx.com, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000037edc40642d30bc6"
Archived-At: <https://www.postgresql.org/message-id/CAJ4rSwstZoVgVjbHeDNVq%2B7eBWCVZSXjNMRpzB4QFjArZT0Hcg%40mail.gmail.com>
Precedence: bulk

--00000000000037edc40642d30bc6
Content-Type: text/plain; charset="UTF-8"

Thank you all for your suggestions,

Thanks for your quick response and for sharing the details.
After reviewing the options, the logical replication approach seems to be
the most feasible one with minimal downtime.

However, we currently have 7 streaming replication setups running from
production, with a total database size of around 15 TB. Out of this, there
are about 10 large tables ranging from 1 TB (max) to 50 GB (min) each,
along with approximately 150+ sequences.

Could you please confirm if there are any successful case studies or
benchmarks available for a similar setup?
Additionally, please share any recommended parameter tuning or best
practices for handling logical replication at this scale.

Current server configuration:

CPU: 144 cores

RAM: 512 GB


Thanks & Regards
Krishna.


On Fri, 24 Oct 2025 at 21:55, Francisco Olarte <folarte@peoplecall.com>
wrote:

>
> On Thu, 23 Oct 2025 at 17:21, Greg Sabino Mullane <htamfids@gmail.com>
> wrote
>
> 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.
>>
>
> Long time ago I had a similar problem and did a "running with scissors"
> restore. This means:
>
> 1.- Prepare normal configuration, test, etc for the new version.
> 2.- Prepare a restore configuration, with fsync=off, wallevel=minimal,
> whatever option gives you any speed advantage.
>
> As the target was empty, if restore failed we could just clean and restart.
>
> 3.- Dump, boot with the restore configuration, restore, clean shutdown,
> switch to production configuration, boot again and follow on.
>
> Time has passed and I lost my notes, but I remember the restore was much
> faster than doing it with the normal production configuration. Given
> current machine speeds, it maybe doable.
>
>
> Francisco Olarte.
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Thank you all for your suggestions,=
=C2=A0</div><div><br></div><div>Thanks for your quick response and for shar=
ing the details.<br>After reviewing the options, the logical replication ap=
proach seems to be the most feasible one with minimal downtime.<br><br>Howe=
ver, we currently have 7 streaming replication setups running from producti=
on, with a total database size of around 15 TB. Out of this, there are abou=
t 10 large tables ranging from 1 TB (max) to 50 GB (min) each, along with a=
pproximately 150+ sequences.<br><br>Could you please confirm if there are a=
ny successful case studies or benchmarks available for a similar setup?<br>=
Additionally, please share any recommended parameter tuning or best practic=
es for handling logical replication at this scale.<br><br>Current server co=
nfiguration:<br><br>CPU: 144 cores<br><br>RAM: 512 GB</div><div><div dir=3D=
"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><br><div><br></div><div>Th=
anks &amp; Regards</div><div>Krishna.</div></div></div></div><br></div><br>=
<div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"=
gmail_attr">On Fri, 24 Oct 2025 at 21:55, Francisco Olarte &lt;<a href=3D"m=
ailto:folarte@peoplecall.com">folarte@peoplecall.com</a>&gt; wrote:<br></di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><br><d=
iv class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 23 O=
ct 2025 at 17:21, Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.=
com" target=3D"_blank">htamfids@gmail.com</a>&gt; wrote</div><div dir=3D"lt=
r" class=3D"gmail_attr"><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"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>pg_dump is the =
most reliable, and the slowest. Keep in mind that only the actual data need=
s 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 tabl=
es that are super large. Whether either approach fits in your 24 hour windo=
w is hard to say without you running some tests.</div></div></div></blockqu=
ote><div><br></div><div><div style=3D"font-family:monospace,monospace">Long=
 time ago I had a similar problem and did a &quot;running with scissors&quo=
t; restore. This means:</div><div style=3D"font-family:monospace,monospace"=
><br></div><div style=3D"font-family:monospace,monospace">1.- Prepare norma=
l configuration, test, etc for the new version.</div><div style=3D"font-fam=
ily:monospace,monospace">2.- Prepare a restore configuration, with fsync=3D=
off, wallevel=3Dminimal, whatever option gives you any speed advantage.</di=
v><div style=3D"font-family:monospace,monospace"><br></div><div style=3D"fo=
nt-family:monospace,monospace">As the target was empty, if restore failed w=
e could just clean and restart.</div><div style=3D"font-family:monospace,mo=
nospace"><br></div><div style=3D"font-family:monospace,monospace">3.- Dump,=
 boot with the restore configuration, restore, clean shutdown, switch to pr=
oduction configuration, boot again and follow on.</div><div style=3D"font-f=
amily:monospace,monospace"><br></div><div style=3D"font-family:monospace,mo=
nospace">Time has passed and I lost my notes, but I remember the restore wa=
s much faster than doing it with the normal production configuration. Given=
 current machine speeds, it maybe doable.</div><div style=3D"font-family:mo=
nospace,monospace"><br></div><div style=3D"font-family:monospace,monospace"=
><br></div><div style=3D"font-family:monospace,monospace">Francisco Olarte.=
</div></div><br></div></div>
</blockquote></div></div>

--00000000000037edc40642d30bc6--