MIME-Version: 1.0
References: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
In-Reply-To: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 24 Nov 2024 11:47:32 -0500
Message-ID: <CANzqJaC34D0nboV+j_sEniyaxsH2-A7fH9QwVSE9WMu=t_y93w@mail.gmail.com>
Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting
 Up Streaming Replication
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fa401d0627ab5f4c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaC34D0nboV%2Bj_sEniyaxsH2-A7fH9QwVSE9WMu%3Dt_y93w%40mail.gmail.com>
Precedence: bulk

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

On Sun, Nov 24, 2024 at 11:05=E2=80=AFAM Subhash Udata <subhashudata@gmail.=
com>
wrote:

> Dear PostgreSQL Community,
>
> I have a production database setup with a primary server and a standby
> server. The database is currently running on *PostgreSQL 15.0*, and I
> plan to upgrade both servers to *15.9*.
>
> I have the following questions regarding the upgrade and replication
> process:
>
>    1.
>
>    *Upgrade and Replication Compatibility*:
>    - My plan is to perform a failover, promote the standby server
>       (currently 15.0) to primary, and then upgrade the old primary serve=
r to
>       version 15.9.
>
>
Try to replicate from old->new version, because bug fixes in newer versions
might have broken something in new->old replication.

If you really can't tolerate any downtime, then shutdown and upgrade the
Secondary server from 15.0 to 15.10.  Once you start it back up,
replication from the still-15.0 primary will catch back up to the
now-patched Secondary.

Fail over to the Secondary (now new-Primary), and then patch old-Primary to
15.10.


>
>    1.
>       - After upgrading the old primary server to version 15.9, I want to
>       configure it as a standby server and set up streaming replication w=
ith the
>       new primary server, which will still be running version 15.0.
>       - Is it possible to establish streaming replication between these
>       two versions (*15.0* as primary and *15.9* as standby)?
>    2.
>
>    *Efficient Replication Setup*:
>    - The production database is around *1TB in size*, and creating
>       replication using pg_basebackup is taking more than 2=E2=80=933 hou=
rs to
>       complete.
>       - Is there an alternative method to set up replication without
>       taking a full backup of the entire cluster but instead using only t=
he WAL
>       files that have changed on both servers?
>
>
pg_rewind is probably what you want.  I've never used it, though.

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Nov 24, 2024 at 11:05=E2=80=AFAM =
Subhash Udata &lt;<a href=3D"mailto:subhashudata@gmail.com">subhashudata@gm=
ail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"ltr"><p>Dear PostgreSQL Community=
,</p><p>I have a production database setup with a primary server and a stan=
dby server. The database is currently running on <strong>PostgreSQL 15.0</s=
trong>, and I plan to upgrade both servers to <strong>15.9</strong>.</p><p>=
I have the following questions regarding the upgrade and replication proces=
s:</p><ol><li><p><strong>Upgrade and Replication Compatibility</strong>:</p=
><ul><li>My plan is to perform a failover, promote the standby server (curr=
ently 15.0) to primary, and then upgrade the old primary server to version =
15.9.</li></ul></li></ol></div></blockquote><div><br></div><div>Try to repl=
icate from old-&gt;new version, because bug fixes in newer versions might h=
ave broken something in new-&gt;old replication.</div><div><br></div><div>I=
f you really can&#39;t tolerate any downtime, then shutdown and upgrade the=
 Secondary server from 15.0 to 15.10.=C2=A0 Once you start it back up, repl=
ication from the still-15.0 primary will catch back up to the now-patched S=
econdary.</div><div><br></div><div>Fail over to the Secondary (now new-Prim=
ary), and then=C2=A0patch old-Primary to 15.10.</div><div>=C2=A0</div><bloc=
kquote 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"><ol><li><ul><=
li>After upgrading the old primary server to version 15.9, I want to config=
ure it as a standby server and set up streaming replication with the new pr=
imary server, which will still be running version 15.0.</li><li>Is it possi=
ble to establish streaming replication between these two versions (<strong>=
15.0</strong> as primary and <strong>15.9</strong> as standby)?</li></ul></=
li><li><p><strong>Efficient Replication Setup</strong>:</p><ul><li>The prod=
uction database is around <strong>1TB in size</strong>, and creating replic=
ation using <code>pg_basebackup</code> is taking more than 2=E2=80=933 hour=
s to complete.</li><li>Is there an alternative method to set up replication=
 without taking a full backup of the entire cluster but instead using only =
the WAL files that have changed on both servers?</li></ul></li></ol></div><=
/blockquote><div><br></div><div>pg_rewind is probably what you want.=C2=A0 =
I&#39;ve never used it, though.</div></div><div><br></div><span class=3D"gm=
ail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signatu=
re"><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>

--000000000000fa401d0627ab5f4c--