MIME-Version: 1.0
References: 
 <CA+-Cqf4EoAxwfqvUQiPU--zJ2Qt+wA3239XWE13gKD+PGqZa+Q@mail.gmail.com>
 <CANzqJaB1KNofZTTpx7+FKiecCoeGTWzLeQ2MpJN3WGQLbkOq-g@mail.gmail.com>
In-Reply-To: 
 <CANzqJaB1KNofZTTpx7+FKiecCoeGTWzLeQ2MpJN3WGQLbkOq-g@mail.gmail.com>
From: Motog Plus <mplus7535@gmail.com>
Date: Tue, 17 Dec 2024 20:21:54 +0530
Message-ID: 
 <CAL5GnisP1cykT=hRd_vGbjyNZicr7M2gZwzSMns=_Q1MCGMtQA@mail.gmail.com>
Subject: Re: Postgres upgradation
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c83465062978701a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAL5GnisP1cykT%3DhRd_vGbjyNZicr7M2gZwzSMns%3D_Q1MCGMtQA%40mail.gmail.com>
Precedence: bulk

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

Hi Ron,

Is it advisable to use pg_dumpall to take backup and psql utility to
restore so that along with data(multiple DBs) roles, tablespaces if any are
also copied and then restored.

On Tue, Dec 17, 2024, 20:11 Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Tue, Dec 17, 2024 at 5:21=E2=80=AFAM Narendran .j <jnarendran3@gmail.c=
om>
> wrote:
>
>> Hello All,
>>
>> In our environment, PostgreSQL is currently running on lower versions,
>> ranging from 9 to 11.
>>
>
> PG 9.x is presumably running on an EOL version of the OS, meaning you'll
> migrate to a current version of the OS.  That means you'll use
> pg_dump/pg_restore OR Logical Replication, instead of pg_upgrade.
>
> You should probably do the same on the server running PG 11.
>
>
>> We are planning to upgrade to the latest version, and I have a few
>> questions regarding the process.
>>
>> What are the key considerations or checks we need to perform before the
>> upgrade?
>>
>> Can we directly upgrade from version 9 to 15, or is a step-by-step
>> upgrade required?
>>
>
> Read https://www.postgresql.org/docs/current/app-pgdump.html and
> https://www.postgresql.org/docs/current/app-pgrestore.html
>
>
>
>
>> How should we handle extensions during the upgrade process?
>>
>
> Depends on the extension, and how you install Postgresql.  Since we
> install from RPM packages, it was as simple as installing the latest
> versions of those packages, and then running pg_restore.
>
>
>> What challenges or issues should we anticipate during or after the
>> upgrade?
>>
>
> You might need to update db drivers like JDBC at the same time.
>
>
>> If anyone with experience in upgrading Postgres, please share your
>> guidance.
>>
>
> Since pg_dump can be performed online, test the process beforehand.  I'd
> bet that your app servers are also running on old servers, too.  Upgrade
> them, and you'll have a perfect chance to test the system beforehand.
>
> The only hiccup we faced when moving from PG 9.6 to 14 was that the
> updated JDBC drivers on the application servers choked on some strings wi=
th
> quotes in them.
>
> YMMV.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

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

<div dir=3D"auto">Hi Ron,<div dir=3D"auto"><br></div><div dir=3D"auto">Is i=
t advisable to use pg_dumpall to take backup and psql utility to restore so=
 that along with data(multiple DBs) roles, tablespaces if any are also copi=
ed and then restored.</div></div><br><div class=3D"gmail_quote gmail_quote_=
container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Dec 17, 2024, 20:1=
1 Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@=
gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=
=3D"ltr"><div dir=3D"ltr">On Tue, Dec 17, 2024 at 5:21=E2=80=AFAM Narendran=
 .j &lt;<a href=3D"mailto:jnarendran3@gmail.com" target=3D"_blank" rel=3D"n=
oreferrer">jnarendran3@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_qu=
ote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">He=
llo All,<div dir=3D"auto"><br></div><div dir=3D"auto">In our environment, P=
ostgreSQL is currently running on lower versions, ranging from 9 to 11.</di=
v></div></blockquote><div><br></div><div><div class=3D"gmail_quote"><div>PG=
 9.x is presumably running on an EOL version of the OS, meaning you&#39;ll =
migrate to a current version of the OS.=C2=A0 That means you&#39;ll use pg_=
dump/pg_restore OR Logical Replication, instead of pg_upgrade.</div><div><b=
r></div><div>You should probably do the same on the server running PG 11.</=
div></div><div>=C2=A0</div></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div dir=3D"auto"><div dir=3D"auto"> We are planning to upgrade to =
the latest version, and I have a few questions regarding the process.</div>=
<div dir=3D"auto"><br></div><div dir=3D"auto">What are the key consideratio=
ns or checks we need to perform before the upgrade?</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">Can we directly upgrade from version 9 to 15, o=
r is a step-by-step upgrade required?</div></div></blockquote><div><br></di=
v><div>Read=C2=A0<a href=3D"https://www.postgresql.org/docs/current/app-pgd=
ump.html" target=3D"_blank" rel=3D"noreferrer">https://www.postgresql.org/d=
ocs/current/app-pgdump.html</a> and=C2=A0<a href=3D"https://www.postgresql.=
org/docs/current/app-pgrestore.html" target=3D"_blank" rel=3D"noreferrer">h=
ttps://www.postgresql.org/docs/current/app-pgrestore.html</a></div><div><br=
></div><div><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"auto"><div dir=3D"auto">How should we handle exte=
nsions during the upgrade process?</div></div></blockquote><div><br></div><=
div>Depends on the extension, and how you install Postgresql.=C2=A0 Since w=
e install from RPM packages, it was as simple as installing the latest vers=
ions of those packages, and then running pg_restore.</div><div>=C2=A0</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"auto"><div di=
r=3D"auto">What challenges or issues should we anticipate during or after t=
he upgrade?</div></div></blockquote><div><br></div><div>You might need to u=
pdate db drivers like JDBC at the same time.</div><div>=C2=A0</div><blockqu=
ote 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"auto"><div dir=3D"aut=
o">If anyone with experience in upgrading Postgres, please share your guida=
nce.</div></div></blockquote><div><br></div><div>Since pg_dump can be perfo=
rmed online, test the process beforehand.=C2=A0 I&#39;d bet that your app s=
ervers are also running on old servers, too.=C2=A0 Upgrade them, and you=
9;ll have a perfect chance to test the system beforehand.</div><div><br></d=
iv><div>The only hiccup we faced when moving from PG 9.6 to 14 was that the=
 updated JDBC drivers on the application servers choked on some strings wit=
h quotes in them.</div><div><br></div><div>YMMV.</div><div><br></div></div>=
<span class=3D"gmail_signature_prefix">-- </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;Redact=
ed&gt; lobster!</div></div></div></div></div></div>
</blockquote></div>

--000000000000c83465062978701a--