MIME-Version: 1.0
References: 
 <PH7PR01MB8541DF688B87D7B38B2D529DB9372@PH7PR01MB8541.prod.exchangelabs.com>
In-Reply-To: 
 <PH7PR01MB8541DF688B87D7B38B2D529DB9372@PH7PR01MB8541.prod.exchangelabs.com>
From: Steve Midgley <science@misuse.org>
Date: Thu, 5 Dec 2024 10:40:46 -0800
Message-ID: 
 <CAJexoS+=Fv6RQXcKQ0q5MacTvUj1AMhC7aSjgfGOPQEgnJua5w@mail.gmail.com>
Subject: Re: Clarification Needed on PostgreSQL Version Upgrade
To: Hamza Godil <HGodil@realtimeca.com>
Cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003b375f06288a3d69"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoS%2B%3DFv6RQXcKQ0q5MacTvUj1AMhC7aSjgfGOPQEgnJua5w%40mail.gmail.com>
Precedence: bulk

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

On Thu, Dec 5, 2024 at 8:33=E2=80=AFAM Hamza Godil <HGodil@realtimeca.com> =
wrote:

> Hello PostgreSQL Experts,
>
>
>
> I have a question regarding PostgreSQL versions and would appreciate some
> clarification. We have two servers:
>
>
>
>    - CCFMG-apps05 =E2=80=93 Windows Server 2019
>    - CCFMG-VP =E2=80=93 Windows Server 2019
>
>
>
> Both servers are currently running PostgreSQL version 14, while the lates=
t
> version available is 16.1. My question is: if we upgrade to the newest
> version, will it cause any disruptions or errors in our work? Additionall=
y,
> should we change or copy the database before proceeding with the upgrade?
>
>
>
> Please let me know your recommendations.
>
>
>
The answers deeply depend on the nature of the systems and businesses that
depend on these systems. Let's assume these databases are highly sensitive
to downtime and data loss (aka "production" systems). Even then, can you
have a "cut-over" downtime period of 5 min or an hour? Or what's the
window? You'll need to answer questions like that.

Let's assume you can take a comfortable 3 hour maintenance window to
cut-over.

First you have to validate that all the systems riding on top of Pg don't
break when you upgrade. Can you do that in a staging environment? If so,
set up a v16.1 staging system and run and test all your apps on that.

As you're getting comfortable that the apps work, you're going to write
some scripts that can dump, copy, and restore everything. Do that process
over and over from prod to staging to prove you know how to do it reliably.

Then prove you know how to rollback changes from the new system back to the
old system (a reverse data load from 16 back to 14). Don't skip this step.
You hope you never need it, but if you do need it, it is unhealthy to try
to come up with a plan and tech in the moment.

Make an easy way to redirect traffic from one set of servers to the other
(14 to 16).

Then set up a prod v16 server and wait for your window.
Shut off all inbound activity
Wait for all activity to settle down to zero
Dump data from Pg 14 servers with your scripts
Restore data to new Pg 16 servers with your scripts
Verify closely that everything worked (no errors you aren't expecting)
Redirect traffic to 16
Reopen inbound activity

If all goes well, you're done. There are probably ways to simplify this,
but if you are dealing with high value data, this is how I would think
about it. Maybe you can get away with an "in-place upgrade" but I've been
punished (not by Pg!) doing database upgrades in place and then stuff
doesn't work and I can't go back to the old version.

I hope this type of general input is helpful,
Steve

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Dec 5, =
2024 at 8:33=E2=80=AFAM Hamza Godil &lt;<a href=3D"mailto:HGodil@realtimeca=
.com">HGodil@realtimeca.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div class=3D"msg-5872059398357265068">


<div lang=3D"EN-US" style=3D"overflow-wrap: break-word;">
<div class=3D"m_-5872059398357265068WordSection1">
<p class=3D"MsoNormal">Hello PostgreSQL Experts,<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p>
<p class=3D"MsoNormal">I have a question regarding PostgreSQL versions and =
would appreciate some clarification. We have two servers:<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p>
<ul style=3D"margin-top:0in" type=3D"disc">
<li class=3D"m_-5872059398357265068MsoListParagraph" style=3D"margin-left:0=
in">CCFMG-apps05 =E2=80=93 Windows Server 2019<u></u><u></u></li><li class=
=3D"m_-5872059398357265068MsoListParagraph" style=3D"margin-left:0in">CCFMG=
-VP =E2=80=93 Windows Server 2019<u></u><u></u></li></ul>
<p class=3D"m_-5872059398357265068MsoListParagraph"><u></u>=C2=A0<u></u></p=
>
<p class=3D"MsoNormal">Both servers are currently running PostgreSQL versio=
n 14, while the latest version available is 16.1. My question is: if we upg=
rade to the newest version, will it cause any disruptions or errors in our =
work? Additionally, should we change
 or copy the database before proceeding with the upgrade?<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p>
<p class=3D"MsoNormal">Please let me know your recommendations.<u></u><u></=
u></p>
<p class=3D"MsoNormal">=C2=A0</p></div></div></div></blockquote><div>The an=
swers deeply depend on the nature of the systems and businesses that depend=
 on these systems. Let&#39;s assume these databases are highly sensitive to=
 downtime and data loss (aka &quot;production&quot; systems). Even then, ca=
n you have a &quot;cut-over&quot; downtime period of 5 min or an hour? Or w=
hat&#39;s the window? You&#39;ll need to answer questions like that.</div><=
div><br></div><div>Let&#39;s assume you can take a comfortable 3 hour maint=
enance window to cut-over.</div><div><br></div><div>First you have to valid=
ate that all the systems riding on top of Pg don&#39;t break when you upgra=
de. Can you do that in a staging environment? If so, set up a v16.1 staging=
 system and run and test all your apps on that.</div><div><br></div><div>As=
 you&#39;re getting comfortable that the apps work, you&#39;re going to wri=
te some scripts that can dump, copy, and restore everything. Do that proces=
s over and over from prod to staging to prove you know how to do it reliabl=
y.=C2=A0</div><div><br></div><div>Then prove you know how to rollback chang=
es from the new system back to the old system (a reverse data load from 16 =
back to 14). Don&#39;t skip this step. You hope you never need it, but if y=
ou do need it, it is unhealthy to try to come up with a plan and tech in th=
e moment.</div><div><br></div><div>Make an easy way to redirect traffic fro=
m one set of servers to the other (14 to 16).<br></div><div><br></div><div>=
</div><div>Then set up a prod v16 server and wait for your window.=C2=A0</d=
iv><div>Shut off all inbound activity</div><div>Wait for all activity to se=
ttle down to zero</div><div>Dump data from Pg 14 servers with your scripts<=
/div><div>Restore data to new Pg 16 servers with your scripts</div><div>Ver=
ify closely that everything worked (no errors you aren&#39;t expecting)<br>=
</div><div>Redirect traffic to 16</div><div>Reopen inbound activity</div><d=
iv><br></div><div>If all goes well, you&#39;re done. There are probably way=
s to simplify this, but if you are dealing with high value data, this is ho=
w I would think about it. Maybe you can get away with an &quot;in-place upg=
rade&quot; but I&#39;ve been punished (not by Pg!) doing database upgrades =
in place and then stuff doesn&#39;t work and I can&#39;t go back to the old=
 version.</div><div><br></div><div>I hope this type of general input is hel=
pful,</div><div>Steve</div><div><br></div></div></div>

--0000000000003b375f06288a3d69--