Thread: Guidance Needed for PostgreSQL Upgrade from 12 to 15
Dear Team,
We are in the process of upgrading our PostgreSQL database from version 12 to version 15. I would greatly appreciate your advice on the following:
1. Would it be recommended to use the --link option with the pg_upgrade command, from disk space point of view? Are there any potential downsides to consider?
2. Do you have any suggestions or best practices for performing an efficient upgrade in a replication setup (streaming replication) for both the primary and standby servers?
Thank you in advance for your insights and guidance.
Best regards,
Raman
On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <mplus7535@gmail.com> wrote:
Dear Team,We are in the process of upgrading our PostgreSQL database from version 12 to version 15. I would greatly appreciate your advice on the following:1. Would it be recommended to use the --link option with the pg_upgrade command, from disk space point of view?
Yes.
Are there any potential downsides to consider?
Very much: it's a one-way journey. No rolling back if something goes wrong!
2. Do you have any suggestions or best practices for performing an efficient upgrade in a replication setup (streaming replication) for both the primary and standby servers?
Streaming replication is not an upgrade method. Logical replication is absolutely a valid method of upgrading PG on one server to PG on a different server.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Thanks Ron for your suggestion.
So for pg upgrade in streaming replication setup, rsync will be a better option for standby upgrade if we are using --link option or recreating the standby, once upgrade completes on primary? Any suggestions on this please.
Best Regards,
Raman
On Mon, Nov 18, 2024, 22:41 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <mplus7535@gmail.com> wrote:--Dear Team,We are in the process of upgrading our PostgreSQL database from version 12 to version 15. I would greatly appreciate your advice on the following:1. Would it be recommended to use the --link option with the pg_upgrade command, from disk space point of view?Yes.Are there any potential downsides to consider?Very much: it's a one-way journey. No rolling back if something goes wrong!2. Do you have any suggestions or best practices for performing an efficient upgrade in a replication setup (streaming replication) for both the primary and standby servers?Streaming replication is not an upgrade method. Logical replication is absolutely a valid method of upgrading PG on one server to PG on a different server.Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
No. Neither rsync nor streaming replication will do version upgrades.
The only ways to do version upgrade are:
1. pg_dump + pg_restore
2. pg_upgrade
3. Logical replication
On Mon, Nov 18, 2024 at 12:20 PM Motog Plus <mplus7535@gmail.com> wrote:
Thanks Ron for your suggestion.So for pg upgrade in streaming replication setup, rsync will be a better option for standby upgrade if we are using --link option or recreating the standby, once upgrade completes on primary? Any suggestions on this please.Best Regards,RamanOn Mon, Nov 18, 2024, 22:41 Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <mplus7535@gmail.com> wrote:--Dear Team,We are in the process of upgrading our PostgreSQL database from version 12 to version 15. I would greatly appreciate your advice on the following:1. Would it be recommended to use the --link option with the pg_upgrade command, from disk space point of view?Yes.Are there any potential downsides to consider?Very much: it's a one-way journey. No rolling back if something goes wrong!2. Do you have any suggestions or best practices for performing an efficient upgrade in a replication setup (streaming replication) for both the primary and standby servers?Streaming replication is not an upgrade method. Logical replication is absolutely a valid method of upgrading PG on one server to PG on a different server.Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Στις 18/11/24 19:26, ο/η Ron Johnson έγραψε:
No. Neither rsync nor streaming replication will do version upgrades.The only ways to do version upgrade are:1. pg_dump + pg_restore2. pg_upgrade3. Logical replication
I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap the roles of publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaks beyond repair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (such as sequences).
On Mon, Nov 18, 2024 at 12:20 PM Motog Plus <mplus7535@gmail.com> wrote:Thanks Ron for your suggestion.So for pg upgrade in streaming replication setup, rsync will be a better option for standby upgrade if we are using --link option or recreating the standby, once upgrade completes on primary? Any suggestions on this please.Best Regards,RamanOn Mon, Nov 18, 2024, 22:41 Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <mplus7535@gmail.com> wrote:--Dear Team,We are in the process of upgrading our PostgreSQL database from version 12 to version 15. I would greatly appreciate your advice on the following:1. Would it be recommended to use the --link option with the pg_upgrade command, from disk space point of view?Yes.Are there any potential downsides to consider?Very much: it's a one-way journey. No rolling back if something goes wrong!2. Do you have any suggestions or best practices for performing an efficient upgrade in a replication setup (streaming replication) for both the primary and standby servers?Streaming replication is not an upgrade method. Logical replication is absolutely a valid method of upgrading PG on one server to PG on a different server.Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
> On Nov 18, 2024, at 12:10 PM, Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote: > > > I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap the rolesof publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaks beyondrepair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (such as sequences). In the same vein, if one's file system offers atomic snapshots: 1) shut down PG 2) run pg_upgrade with the hard links option 3) take snapshot 4) start up new PG Now, if there's a failure, you can go back to the snapshot and either start up the old PG, or do some troubleshooting andtry upgrading again.
Scott Ribe wrote: >> On Nov 18, 2024, at 12:10 PM, Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote: >> >> >> I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap theroles of publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaksbeyond repair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (suchas sequences). > > In the same vein, if one's file system offers atomic snapshots: > > 1) shut down PG > 2) run pg_upgrade with the hard links option > 3) take snapshot > 4) start up new PG > > Now, if there's a failure, you can go back to the snapshot and either start up the old PG, or do some troubleshooting andtry upgrading again. Er... I would think that you'd want to do: 1) shut down PG 2) take snapshot 3) run pg_upgrade with the hard links option 4) start up new PG as otherwise the old PG won't start properly, due to the changes made by pg_upgrade. -kgd
> On Nov 19, 2024, at 10:32 AM, Kris Deugau <kdeugau@vianet.ca> wrote: > > as otherwise the old PG won't start properly, due to the changes made by pg_upgrade. Not true, pg_upgrade leaves it in a state where either can be started. By taking the snapshot after, if you roll back toit, you can attempt changes on either the old or new. (Taking care to take additional snapshots as needed, to preservethe ability to roll back to this state.)
Scott Ribe wrote: >> On Nov 19, 2024, at 10:32 AM, Kris Deugau <kdeugau@vianet.ca> wrote: >> >> as otherwise the old PG won't start properly, due to the changes made by pg_upgrade. > > Not true, pg_upgrade leaves it in a state where either can be started. By taking the snapshot after, if you roll back toit, you can attempt changes on either the old or new. (Taking care to take additional snapshots as needed, to preservethe ability to roll back to this state.) I stand corrected. I hadn't read the docs on pg_upgrade for quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html: "If you did not start the new cluster, the old cluster was unmodified except that, when linking started, a .old suffix was appended to $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix from $PGDATA/global/pg_control; you can then restart the old cluster." I see what you mean. -kgd
> On Nov 19, 2024, at 1:40 PM, Kris Deugau <kdeugau@vianet.ca> wrote: > > I stand corrected. I hadn't read the docs on pg_upgrade for quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html: > > "If you did not start the new cluster, the old cluster was unmodified except that, when linking started, a .old suffixwas appended to $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix from $PGDATA/global/pg_control;you can then restart the old cluster." > > I see what you mean. > There's nothing wrong per se about taking the snapshot before, I was just saving the potential time of re-running pg_upgrade.Heck, take a snapshot before *and* after ;-)
Dear Team,
Thank you for your valuable inputs on the PostgreSQL upgrade.
Given the challenges encountered with pg_upgrade in a Kubernetes environment, we're considering a more traditional approach involving pg_dumpall to take backup and then restore the data using psql utility.
Can you please advise if this approach will be fine or you see any issues with it?
A high level overview of the steps:
1. Backup:
* Connect to the existing PostgreSQL 12 pod.
* Execute pg_dumpall to create a complete database dump.
2. New Deployment:
* Create a new PostgreSQL 16 pod.
I think no need to use initidb as it will be autoinitialized .
3. Restore:
* Transfer the backup file to the new pod.
* Use psql utility to restore the database from the dump.
4. Verification:
* Thoroughly test the restored database to ensure data integrity and functionality.
5. Cutover:
* Once verification is complete, switch over traffic to the new PostgreSQL 16 pod.
* Delete the old PostgreSQL 12 pod.
Best Regards,
Ramzy
On Wed, Nov 20, 2024, 02:47 Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Nov 19, 2024, at 1:40 PM, Kris Deugau <kdeugau@vianet.ca> wrote:
>
> I stand corrected. I hadn't read the docs on pg_upgrade for quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html:
>
> "If you did not start the new cluster, the old cluster was unmodified except that, when linking started, a .old suffix was appended to $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix from $PGDATA/global/pg_control; you can then restart the old cluster."
>
> I see what you mean.
>
There's nothing wrong per se about taking the snapshot before, I was just saving the potential time of re-running pg_upgrade. Heck, take a snapshot before *and* after ;-)
pg_dumpall uses a single thread to generate one big .sql file. If your database(s) are small enough that generating (even a compressed) .sql file, copying it then importing it is Good Enough, then that's fine.
Otherwise, "logical replication" or "multithreaded pg_dump" is what you want.
On Thu, Nov 21, 2024 at 1:17 PM Motog Plus <mplus7535@gmail.com> wrote:
Dear Team,Thank you for your valuable inputs on the PostgreSQL upgrade.Given the challenges encountered with pg_upgrade in a Kubernetes environment, we're considering a more traditional approach involving pg_dumpall to take backup and then restore the data using psql utility.Can you please advise if this approach will be fine or you see any issues with it?A high level overview of the steps:1. Backup:* Connect to the existing PostgreSQL 12 pod.* Execute pg_dumpall to create a complete database dump.2. New Deployment:* Create a new PostgreSQL 16 pod.I think no need to use initidb as it will be autoinitialized .3. Restore:* Transfer the backup file to the new pod.* Use psql utility to restore the database from the dump.4. Verification:* Thoroughly test the restored database to ensure data integrity and functionality.5. Cutover:* Once verification is complete, switch over traffic to the new PostgreSQL 16 pod.* Delete the old PostgreSQL 12 pod.Best Regards,RamzyOn Wed, Nov 20, 2024, 02:47 Scott Ribe <scott_ribe@elevated-dev.com> wrote:> On Nov 19, 2024, at 1:40 PM, Kris Deugau <kdeugau@vianet.ca> wrote:
>
> I stand corrected. I hadn't read the docs on pg_upgrade for quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html:
>
> "If you did not start the new cluster, the old cluster was unmodified except that, when linking started, a .old suffix was appended to $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix from $PGDATA/global/pg_control; you can then restart the old cluster."
>
> I see what you mean.
>
There's nothing wrong per se about taking the snapshot before, I was just saving the potential time of re-running pg_upgrade. Heck, take a snapshot before *and* after ;-)
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Hi Ron,
Thanks for your response l.
Logical replication i can't use because of primary key issues.
I have multiple DBs, then multiple schemas and then multiple tables, functions etc
Will I be able to copy whole cluster including data, roles, globals everything using pg_dump?
Also while restoring, can I restore everything, the same structure, in a single go using pg_restore?
On Fri, Nov 22, 2024, 00:16 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
pg_dumpall uses a single thread to generate one big .sql file. If your database(s) are small enough that generating (even a compressed) .sql file, copying it then importing it is Good Enough, then that's fine.Otherwise, "logical replication" or "multithreaded pg_dump" is what you want.On Thu, Nov 21, 2024 at 1:17 PM Motog Plus <mplus7535@gmail.com> wrote:Dear Team,Thank you for your valuable inputs on the PostgreSQL upgrade.Given the challenges encountered with pg_upgrade in a Kubernetes environment, we're considering a more traditional approach involving pg_dumpall to take backup and then restore the data using psql utility.Can you please advise if this approach will be fine or you see any issues with it?A high level overview of the steps:1. Backup:* Connect to the existing PostgreSQL 12 pod.* Execute pg_dumpall to create a complete database dump.2. New Deployment:* Create a new PostgreSQL 16 pod.I think no need to use initidb as it will be autoinitialized .3. Restore:* Transfer the backup file to the new pod.* Use psql utility to restore the database from the dump.4. Verification:* Thoroughly test the restored database to ensure data integrity and functionality.5. Cutover:* Once verification is complete, switch over traffic to the new PostgreSQL 16 pod.* Delete the old PostgreSQL 12 pod.Best Regards,RamzyOn Wed, Nov 20, 2024, 02:47 Scott Ribe <scott_ribe@elevated-dev.com> wrote:> On Nov 19, 2024, at 1:40 PM, Kris Deugau <kdeugau@vianet.ca> wrote:
>
> I stand corrected. I hadn't read the docs on pg_upgrade for quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html:
>
> "If you did not start the new cluster, the old cluster was unmodified except that, when linking started, a .old suffix was appended to $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix from $PGDATA/global/pg_control; you can then restart the old cluster."
>
> I see what you mean.
>
There's nothing wrong per se about taking the snapshot before, I was just saving the potential time of re-running pg_upgrade. Heck, take a snapshot before *and* after ;-)--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
pg_dump and pg_restore only work on a single database, but they contain EVERYTHING that database needs. Thus, you'll have to do a pg_dump/pg_restore pair
If tasked with migrating multiple databases, and LR was not an option, I would:
On the new server,
1. "pg_dumpall -h $OldServer --globals > globals.sql" to get the roles, etc. Will need to remove the "postgres" role.
2. "pg_dump -h $OldServer -j$Threads -Fd $DB ..." for each database.
3. psql postgres -af globals.sql
4. "pg_restore -v -j $Threads --exit-on-error -cC -Fd --no-tablespaces -d postgres $DB" for each database.
5. vacuumdb --analyze --jobs=$(nproc) -d $DB
Do all the pg_dump and then all the pg_restore commands, or alternate pg_dump/pg_restore pairs, one database at a time. That's up to you.
I would set these config params before each Step4:
pg_ctl restart -wt9999 -mfast \
-o "-c hba_file=$PGDATA/pg_hba_maintmode.conf" \
-o "-c fsync=off" \
-o "-c log_statement=none" \
-o "-c log_temp_files=100kB" \
-o "-c log_checkpoints=on" \
-o "-c log_min_duration_statement=120000" \
-o "-c shared_buffers=${SharedBuffs}GB" \
-o "-c maintenance_work_mem=${MaintMem}GB" \
-o "-c synchronous_commit=off" \
-o "-c archive_mode=off" \
-o "-c full_page_writes=off" \
-o "-c checkpoint_timeout=${CheckPoint}min" \
-o "-c max_wal_size=${MaxWalSize}GB" \
-o "-c wal_level=minimal" \
-o "-c max_wal_senders=0" \
-o "-c wal_buffers=${WalBuffs}MB" \
-o "-c autovacuum=off"
-o "-c hba_file=$PGDATA/pg_hba_maintmode.conf" \
-o "-c fsync=off" \
-o "-c log_statement=none" \
-o "-c log_temp_files=100kB" \
-o "-c log_checkpoints=on" \
-o "-c log_min_duration_statement=120000" \
-o "-c shared_buffers=${SharedBuffs}GB" \
-o "-c maintenance_work_mem=${MaintMem}GB" \
-o "-c synchronous_commit=off" \
-o "-c archive_mode=off" \
-o "-c full_page_writes=off" \
-o "-c checkpoint_timeout=${CheckPoint}min" \
-o "-c max_wal_size=${MaxWalSize}GB" \
-o "-c wal_level=minimal" \
-o "-c max_wal_senders=0" \
-o "-c wal_buffers=${WalBuffs}MB" \
-o "-c autovacuum=off"
And then this after Step 5:
pg_ctl -wt9999 stop -mfast && pg_ctl -wt9999 start
Be careful with what you set ${SharedBuffs} and ${MaintMem} to: with lots of threads, it's easy to run out of memory, and then the oom will kill the pg_restore.
On Thu, Nov 21, 2024 at 9:55 PM Motog Plus <mplus7535@gmail.com> wrote:
Hi Ron,Thanks for your response l.Logical replication i can't use because of primary key issues.I have multiple DBs, then multiple schemas and then multiple tables, functions etcWill I be able to copy whole cluster including data, roles, globals everything using pg_dump?Also while restoring, can I restore everything, the same structure, in a single go using pg_restore?On Fri, Nov 22, 2024, 00:16 Ron Johnson <ronljohnsonjr@gmail.com> wrote:pg_dumpall uses a single thread to generate one big .sql file. If your database(s) are small enough that generating (even a compressed) .sql file, copying it then importing it is Good Enough, then that's fine.Otherwise, "logical replication" or "multithreaded pg_dump" is what you want.On Thu, Nov 21, 2024 at 1:17 PM Motog Plus <mplus7535@gmail.com> wrote:Dear Team,Thank you for your valuable inputs on the PostgreSQL upgrade.Given the challenges encountered with pg_upgrade in a Kubernetes environment, we're considering a more traditional approach involving pg_dumpall to take backup and then restore the data using psql utility.Can you please advise if this approach will be fine or you see any issues with it?A high level overview of the steps:1. Backup:* Connect to the existing PostgreSQL 12 pod.* Execute pg_dumpall to create a complete database dump.2. New Deployment:* Create a new PostgreSQL 16 pod.I think no need to use initidb as it will be autoinitialized .3. Restore:* Transfer the backup file to the new pod.* Use psql utility to restore the database from the dump.4. Verification:* Thoroughly test the restored database to ensure data integrity and functionality.5. Cutover:* Once verification is complete, switch over traffic to the new PostgreSQL 16 pod.* Delete the old PostgreSQL 12 pod.Best Regards,RamzyOn Wed, Nov 20, 2024, 02:47 Scott Ribe <scott_ribe@elevated-dev.com> wrote:> On Nov 19, 2024, at 1:40 PM, Kris Deugau <kdeugau@vianet.ca> wrote:
>
> I stand corrected. I hadn't read the docs on pg_upgrade for quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html:
>
> "If you did not start the new cluster, the old cluster was unmodified except that, when linking started, a .old suffix was appended to $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix from $PGDATA/global/pg_control; you can then restart the old cluster."
>
> I see what you mean.
>
There's nothing wrong per se about taking the snapshot before, I was just saving the potential time of re-running pg_upgrade. Heck, take a snapshot before *and* after ;-)
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!