MIME-Version: 1.0
References: <DU2PR04MB8631002517CF55E7E48B6FB1E3A6A@DU2PR04MB8631.eurprd04.prod.outlook.com>
In-Reply-To: <DU2PR04MB8631002517CF55E7E48B6FB1E3A6A@DU2PR04MB8631.eurprd04.prod.outlook.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 4 Dec 2025 08:57:30 -0500
Message-ID: <CANzqJaA7NOamDoVLpc478BuxXMMEjiCAJUvHHdwUY5P63LQPSw@mail.gmail.com>
Subject: Re: Upgrade Failover Cluster
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000693eb6064520b6e4"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaA7NOamDoVLpc478BuxXMMEjiCAJUvHHdwUY5P63LQPSw%40mail.gmail.com>
Precedence: bulk

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

On Thu, Dec 4, 2025 at 2:00=E2=80=AFAM BUBACZ Martin <martin.bubacz@axa.com=
> wrote:

> Hi All,
>
>
>
> I=E2=80=99m fairly new to postgresql and I have a question concerning upg=
rading a
> 2 node failover physical replication cluster. I=E2=80=99m automating the =
cluster
> installation and maintenance by using Puppet. All runs good so far.
>
>
>
> When it comes to an upgrade scenario, let=E2=80=99s say upgrading from re=
l. 16.x
> to 17.x, I=E2=80=99m unsure if it is required to upgrade primary first an=
d then
> replica or if order doesn=E2=80=99t matter. I=E2=80=99ve read different s=
tatements in some
> articles on internet about it. One said it=E2=80=99s possible to upgrade =
replica
> first by using pg_upgrade or rebuilding replica from primary by
> pg_basebackup and others are telling me primary first.
>
>
>
> What is the recommended/most stable path to upgrade a cluster? I would
> appreciate if you could shed some light on this? Thanks beforehand.
>

One standard low-downtime practice is:to do *logical* replication from the
Primary to the Secondary.  Then promote the Secondary to be the New
Primary, and do a pg_basebackup from it back to the Old Primary.

The other low- (but not as low) downtime method is to stop replication, and
pg_upgrade the Primary.  (It's fast with the --link option, but there's no
going back once you start the upgraded instance.)  Then pg_basebackup to
the Secondary as normal.

Last is to stop replication and then do a normal pg_upgrade.  It's still
fast, but copies all data to a new directory, but that "fast" is relative
to how big your data is.

Which you choose depends on how much downtime you can get and (since LR
doesn't replicate DDL and sequences) how often the DDL changes.

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Dec 4, 2025 at 2:00=E2=80=AFAM BU=
BACZ Martin &lt;<a href=3D"mailto:martin.bubacz@axa.com">martin.bubacz@axa.=
com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"msg-562905486=
8765335439">


<div lang=3D"DE" style=3D"overflow-wrap: break-word;">
<div class=3D"m_7858899684668291466WordSection1">
<p class=3D"MsoNormal">Hi All,<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">I=E2=80=99m fairly new to postg=
resql and I have a question concerning upgrading a 2 node failover physical=
 replication cluster. I=E2=80=99m automating the cluster installation and m=
aintenance by using Puppet. All runs good so far.<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">When it comes to an upgrade sce=
nario, let=E2=80=99s say upgrading from rel. 16.x to 17.x, I=E2=80=99m unsu=
re if it is required to upgrade primary first and then replica or if order =
doesn=E2=80=99t matter. I=E2=80=99ve read different statements in some
 articles on internet about it. One said it=E2=80=99s possible to upgrade r=
eplica first by using pg_upgrade or rebuilding replica from primary by pg_b=
asebackup and others are telling me primary first.<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">What is the recommended/most st=
able path to upgrade a cluster? I would appreciate if you could shed some l=
ight on this? Thanks beforehand.</span></p></div></div></div></blockquote><=
div><br></div><div>One standard low-downtime practice is:to do=C2=A0<b>logi=
cal</b> replication from the Primary to the Secondary.=C2=A0 Then promote t=
he Secondary to be the New Primary, and do a pg_basebackup from it back to =
the Old Primary.</div><div><br></div><div>The other low- (but not as low) d=
owntime method is to stop replication, and pg_upgrade the Primary.=C2=A0 (I=
t&#39;s fast with the --link option, but there&#39;s no going back once you=
 start the upgraded instance.)=C2=A0 Then pg_basebackup to the Secondary as=
 normal.</div><div><br></div><div>Last is to stop replication and then do a=
 normal pg_upgrade.=C2=A0 It&#39;s still fast, but copies all data to a new=
 directory, but that &quot;fast&quot; is relative to how big your data is.<=
/div><div><br></div><div>Which you choose depends on how much downtime you =
can get and (since LR doesn&#39;t replicate DDL and sequences) how often th=
e DDL changes.</div><div>=C2=A0</div></div><span class=3D"gmail_signature_p=
refix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D=
"ltr">Death 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>

--000000000000693eb6064520b6e4--