public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bharani SV-forum <[email protected]>
To: Greg Sabino Mullane <[email protected]>
To: [email protected] <[email protected]>
To: [email protected] <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: Bharani SV esteembsv-forum <[email protected]>
Subject: Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X
Date: Tue, 31 Dec 2024 16:15:03 +0000 (UTC)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKAnmmKZdhnhdNRd3OgDyEco9OPkT=qA_TeWMFMRvUM9pXauKg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CANzqJaAda4nZbkG=c+72ry5GdaxrJ6Jzr6fz1quhq8tTfaX1Pg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CANzqJaC1Uk4H=55vV_jbFYMuD1f9Bb_4Y9WKvkZA3bt92bEUnw@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKAnmmKZdhnhdNRd3OgDyEco9OPkT=qA_TeWMFMRvUM9pXauKg@mail.gmail.com>
Team
I followed Greg suggested steps .One of big had only one table and around four million recordsi am doing dev env restoration into new vmthe target VM env is an POC server and took 3 hrs to restore four million records.Now it is doing process of lo_open / lo_close / lowrite etci.e pg-dump-creates-a-lot-of-pg-catalog-statements
is there any alternate way , to speedup this process.
i can see in the select count(*) record count is matching (target and source)
Regards
On Wednesday, December 4, 2024 at 10:47:26 AM EST, Greg Sabino Mullane <[email protected]> wrote:
On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum <[email protected]> wrote:
a) is the above said steps is correct with the given existing and proposed setup
No. Here are some steps:
* Install Postgres on the new VMHowever 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 clusterOn the new VM, use the initdb command to create a new data directory.Use the --data-checksums option
* Start it upAdjust your postgresql.conf as neededAdjust your pg_hba.conf as neededInstall any extensions used on the old VMStart the cluster using the pg_ctl command (or systemctl)
* Test connection to the old vm from the new vmOn the new vm, see if you can connect to the old one:psql -h oldvm -p 5432 --listYou may need to adjust firewalls and pg_hba.conf on the old vm.
* Copy the dataRun 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 vacuumOn 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
view thread (61+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox