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: Francisco Olarte <folarte@peoplecall.com>
Date: Fri, 24 Oct 2025 18:25:04 +0200
Message-ID: <CA+bJJbwSZg6fiQ78N-_2NwRBbp=6e5WjnSX9SPa_DOxnaU63Vg@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>
Cc: Bala M <krishna.pgdba@gmail.com>, 
	"adrian.klaver@aklaver.com" <adrian.klaver@aklaver.com>, chris+google@qwirx.com, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000029e04e0641ea0079"
Archived-At: <https://www.postgresql.org/message-id/CA%2BbJJbwSZg6fiQ78N-_2NwRBbp%3D6e5WjnSX9SPa_DOxnaU63Vg%40mail.gmail.com>
Precedence: bulk

--00000000000029e04e0641ea0079
Content-Type: text/plain; charset="UTF-8"

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.

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

<div dir=3D"ltr"><br><div class=3D"gmail_quote gmail_quote_container"><div =
dir=3D"ltr" class=3D"gmail_attr">On Thu, 23 Oct 2025 at 17:21, Greg Sabino =
Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.com</a>&gt=
; wrote</div><div dir=3D"ltr" class=3D"gmail_attr"><br></div><blockquote cl=
ass=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_qu=
ote"><div>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_du=
mp if you have a few tables that are super large. Whether either approach f=
its in your 24 hour window is hard to say without you running some tests.</=
div></div></div></blockquote><div><br></div><div><div style=3D"font-family:=
monospace,monospace" class=3D"gmail_default">Long time ago I had a similar =
problem and did a &quot;running with scissors&quot; restore. This means:</d=
iv><div style=3D"font-family:monospace,monospace" class=3D"gmail_default"><=
br></div><div style=3D"font-family:monospace,monospace" class=3D"gmail_defa=
ult">1.- Prepare normal configuration, test, etc for the new version.</div>=
<div style=3D"font-family:monospace,monospace" class=3D"gmail_default">2.- =
Prepare a restore configuration, with fsync=3Doff, wallevel=3Dminimal, what=
ever option gives you any speed advantage.</div><div style=3D"font-family:m=
onospace,monospace" class=3D"gmail_default"><br></div><div style=3D"font-fa=
mily:monospace,monospace" class=3D"gmail_default">As the target was empty, =
if restore failed we could just clean and restart.</div><div style=3D"font-=
family:monospace,monospace" class=3D"gmail_default"><br></div><div style=3D=
"font-family:monospace,monospace" class=3D"gmail_default">3.- Dump, boot wi=
th the restore configuration, restore, clean shutdown, switch to production=
 configuration, boot again and follow on.</div><div style=3D"font-family:mo=
nospace,monospace" class=3D"gmail_default"><br></div><div style=3D"font-fam=
ily:monospace,monospace" class=3D"gmail_default">Time has passed and I lost=
 my notes, but I remember the restore was much faster than doing it with th=
e normal production configuration. Given current machine speeds, it maybe d=
oable.</div><div style=3D"font-family:monospace,monospace" class=3D"gmail_d=
efault"><br></div><div style=3D"font-family:monospace,monospace" class=3D"g=
mail_default"><br></div><div style=3D"font-family:monospace,monospace" clas=
s=3D"gmail_default">Francisco Olarte.</div></div><br></div></div>

--00000000000029e04e0641ea0079--