MIME-Version: 1.0
References: <0558ddd4d71641bdb41fa49b2425f73c@safrangroup.com>
 <A01D7215-2691-4FA6-8B79-E197B7DB9351@gmail.com> <98965993.3138805.1731699978332@mail.yahoo.com>
 <564950518.5117550.1733177884387@mail.yahoo.com> <CANzqJaAda4nZbkG=c+72ry5GdaxrJ6Jzr6fz1quhq8tTfaX1Pg@mail.gmail.com>
 <07ab2d83-ffe5-4bec-9626-22a68f732579@aklaver.com> <273a88dc-4134-47d5-bc19-30ff5f97926c@aklaver.com>
 <CANzqJaC1Uk4H=55vV_jbFYMuD1f9Bb_4Y9WKvkZA3bt92bEUnw@mail.gmail.com>
 <498dfb34-4dd7-4f48-8188-355e1488d7e6@aklaver.com> <1061066336.5835157.1733316137292@mail.yahoo.com>
In-Reply-To: <1061066336.5835157.1733316137292@mail.yahoo.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 4 Dec 2024 10:46:32 -0500
Message-ID: <CAKAnmmKZdhnhdNRd3OgDyEco9OPkT=qA_TeWMFMRvUM9pXauKg@mail.gmail.com>
Subject: Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
 ver 15.X
To: Bharani SV-forum <esteembsv-forum@yahoo.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, pgsql-general <pgsql-general@postgresql.org>, 
	Adrian Klaver <adrian.klaver@aklaver.com>
Content-Type: multipart/alternative; boundary="000000000000c025f0062873b1a8"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmKZdhnhdNRd3OgDyEco9OPkT%3DqA_TeWMFMRvUM9pXauKg%40mail.gmail.com>
Precedence: bulk

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

On Wed, Dec 4, 2024 at 7:42=E2=80=AFAM Bharani SV-forum <esteembsv-forum@ya=
hoo.com>
wrote:

> a) is the above said steps is correct with the given existing and propose=
d
> setup
>

No. Here are some steps:

* Install Postgres on the new VM
However you get it, use the newest version you can. As of this writing, it
is Postgres 17.2. Version 15 is okay, but going to 17 now means a better
Postgres today, and no worrying about replacing v15 in three years.

* Create a new Postgres cluster
On the new VM, use the initdb command to create a new data directory.
Use the --data-checksums option

* Start it up
Adjust your postgresql.conf as needed
Adjust your pg_hba.conf as needed
Install any extensions used on the old VM
Start the cluster using the pg_ctl command (or systemctl)

* Test connection to the old vm from the new vm
On the new vm, see if you can connect to the old one:
psql -h oldvm -p 5432 --list
You may need to adjust firewalls and pg_hba.conf on the old vm.

* Copy the data
Run this on the new VM, adjusting ports as needed:
time pg_dumpall -h oldvm -p 5432 | psql -p 5432

Bonus points for doing this via screen/tmux to prevent interruptions

* Generate new statistics and vacuum
On the new vm, run:
psql -c 'vacuum freeze'
psql -c 'analyze'

* Test your application

* Setup all the other stuff (systemd integration, logrotate, cronjobs,
etc.) as needed

As Peter mentioned earlier, this can be done without disrupting anything,
and is easy to test and debug. The exact steps may vary a little, as I'm
not familiar with how Amazon Linux packages Postgres, but the basics are
the same.

Take it slow. Go through each of these steps one by one. If you get stuck
or run into an issue, stop and solve it, reaching out to this list as
necessary.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">On Wed, Dec 4, 2024 at 7=
:42=E2=80=AFAM Bharani SV-forum &lt;<a href=3D"mailto:esteembsv-forum@yahoo=
.com" target=3D"_blank">esteembsv-forum@yahoo.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(204,204,204);padding-left:1ex"><di=
v style=3D"font-family:&quot;Helvetica Neue&quot;,Helvetica,Arial,sans-seri=
f;font-size:16px"><div></div>
        <div dir=3D"ltr"><span style=3D"color:rgb(38,40,42)">a) is the abov=
e said steps is correct with the given existing and proposed setup</span></=
div></div></blockquote><div><br></div><div>No. Here are some steps:</div><d=
iv><br></div><div>* Install Postgres on the new VM</div><div>However you ge=
t it, use the newest version you can. As of this writing, it is Postgres 17=
.2. Version 15 is okay, but going to 17 now means a better Postgres today, =
and no worrying about replacing v15 in three years.</div><div><br></div><di=
v>* Create a new Postgres cluster</div><div>On the new VM, use the initdb c=
ommand to create a new data directory.</div><div>Use the --data-checksums o=
ption</div><div><br></div><div>* Start it up</div><div>Adjust your postgres=
ql.conf as needed</div><div>Adjust your pg_hba.conf as needed</div><div>Ins=
tall any extensions used on the old VM</div><div>Start the cluster using th=
e pg_ctl command (or systemctl)</div><div><br></div><div>* Test connection =
to the old vm from the new vm</div><div>On the new vm, see if you can conne=
ct to the old one:</div><div>psql -h oldvm -p 5432 --list</div><div><div>Yo=
u may need to adjust firewalls and pg_hba.conf on the old vm.</div></div><d=
iv><br></div><div>* Copy the data</div><div>Run this on the new VM, adjusti=
ng ports as needed:</div><div>time pg_dumpall -h oldvm -p 5432 | psql -p 54=
32</div><div><br></div><div>Bonus points for doing this via screen/tmux to =
prevent interruptions</div><div><br></div><div>* Generate new statistics an=
d vacuum</div><div>On the new vm, run:</div><div>psql -c &#39;vacuum freeze=
&#39;</div><div>psql -c &#39;analyze&#39;</div><div><br></div><div>* Test y=
our application</div><div><br></div><div>* Setup all the other stuff (syste=
md integration, logrotate, cronjobs, etc.) as needed</div><div><br></div><d=
iv>As Peter mentioned earlier, this can be done without disrupting anything=
, and is easy to test and debug. The exact steps may vary a little, as I=
9;m not familiar with how Amazon Linux packages Postgres, but the basics ar=
e the same.</div><div><br></div><div>Take it slow. Go through each of these=
 steps one by one. If you get stuck or run into an issue, stop and solve it=
, reaching out to this list as necessary.</div><div><br></div><div>Cheers,<=
/div><div>Greg</div><div><br></div></div></div>
</div>

--000000000000c025f0062873b1a8--