Subject: Guidance Needed for PostgreSQL Upgrade from 12 to 15

From: Motog Plus <[email-masked]>
Date: Mon, 18 Nov 2024 21:38:31 +0530

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

---

From: Ron Johnson <[email-masked]>
Date: Mon, 18 Nov 2024 12:11:14 -0500

On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <[email-masked]> wrote:

> 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!

> 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!

---

From: Motog Plus <[email-masked]>
Date: Mon, 18 Nov 2024 22:49:54 +0530

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 <[email-masked]> wrote:

>> 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:
>>

---

From: Ron Johnson <[email-masked]>
Date: Mon, 18 Nov 2024 12:26:12 -0500

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

---

From: Achilleas Mantzios <[email-masked]>
Date: Mon, 18 Nov 2024 21:10:47 +0200

> The only ways to do version upgrade are:
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).

>     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
>
>
>             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.
>
>             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.
>         --

---

From: Scott Ribe <[email-masked]>
Date: Mon, 18 Nov 2024 12:20:59 -0700

> On Nov 18, 2024, at 12:10 PM, Achilleas Mantzios <[email-masked]> wrote:
> 
> 
> 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).

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 and try upgrading again.

---

From: Kris Deugau <[email-masked]>
Date: Tue, 19 Nov 2024 12:32:29 -0500

> Now, if there's a failure, you can go back to the snapshot and either start up the old PG, or do some troubleshooting and try 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

---

From: Scott Ribe <[email-masked]>
Date: Tue, 19 Nov 2024 10:42:42 -0700

> On Nov 19, 2024, at 10:32 AM, Kris Deugau <[email-masked]> 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 to it, you can attempt changes on either the old or new. (Taking care to take additional snapshots as needed, to preserve the ability to roll back to this state.)

---

From: Kris Deugau <[email-masked]>
Date: Tue, 19 Nov 2024 15:40:51 -0500

> Not true, pg_upgrade leaves it in a state where either can be started. By taking the snapshot after, if you roll back to it, you can attempt changes on either the old or new. (Taking care to take a…

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

---

From: Scott Ribe <[email-masked]>
Date: Tue, 19 Nov 2024 14:16:47 -0700

> On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email-masked]> 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 ;-)

---

From: Motog Plus <[email-masked]>
Date: Thu, 21 Nov 2024 23:47:01 +0530

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 <[email-masked]> wrote:

> $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 ;-)
>
>
>
>

---

From: Ron Johnson <[email-masked]>
Date: Thu, 21 Nov 2024 13:45:42 -0500

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.

---

From: Motog Plus <[email-masked]>
Date: Fri, 22 Nov 2024 08:24:56 +0530

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 <[email-masked]> wrote:

>>> 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 ;-)
>>>
>>>
>>>
>>>
>
> --

---

From: Ron Johnson <[email-masked]>
Date: Thu, 21 Nov 2024 22:46:14 -0500

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"

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 <[email-masked]> wrote:

>>> Can you please advise if this approach will be fine or you see any
>>> issues with it?
>>>