Thread: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

On Sun, 2025-01-05 at 19:57 +0200, kasem adel wrote:
> Proposed Upgrade Approach:
>
> Simultaneous Upgrade Process:
>
> Install new PostgreSQL version packages on both nodes
> Run pg_upgrade with --check flag on both nodes
> Execute pg_upgrade with --link option on both nodes simultaneously
> Update Patroni configuration for new version on both nodes

No, that won't work.

You have to upgrade the primary, then rebuild the standby with
"patronictl reinit".  There is no safe way to upgrade the standby.

Yours,
Laurenz Albe

> On Jan 6, 2025, at 11:57 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> One month to rebuild a standby?

In addition to other alternatives presented:

- better hardware, one month to restore seems insane
- separate OLTP from data warehouse for reporting
- if the issue is network bandwidth, then local backup, bzip2 for compression

If you do go to logical replication, be sure to look at new features in 17 for managing slots and syncing up.

How large is this database?

Hi,

if you are using the same slow internet connection for WAL and data-transfer you might considering to use rsync which has a full set of throttling, resuming/ibcremential and as mentioned data-compression options so that you do not saturate you connection,

BTW nice challenge,

Best,

Anton

-----Ursprüngliche Nachricht-----
Von: Scott Ribe <scott_ribe@elevated-dev.com>
Gesendet: Dienstag, 7. Januar 2025 15:42
An: kasem adel <kasemadel8@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

> Are you mean do the following step:
>
> 1- upgrade primary node
> 2- take base backup from new  version in primary node and keep wal file for 3 days
> 3- move backup by external hard disk to the replica node
> 4- restore base backup to replica
> 5- start replication to replicate delta from primary node.
>
> Please confirm if this the best approach and it will work without risk.

Yes, that's what I meant. It will work, nothing is completely without risk ;-) Main thing is to make absolutely sure you don't lose WAL during that time period. If you could set up WAL archiving to push to the remote site, that would be great as then you could configure the remote to pull accumulated WAL locally instead of across the slow network link.

But, just thought of this:

- pg_upgrade both sides
- with neither side running, rsync the data directories (bonus points for being paranoid and using -c)
- fix up the standby flag on the standby
- fix up the postgresql.conf -- for instance, standby config has been moved out of a separate file into the main one, so that you can have common config both sides now, with the only necessary difference being the standby flag
- bring them up

You could even try to figure out where the catalog tables are stored and only rsync those, since pg_)upgrade doesn't change the format of your data files. But personally, I wouldn't. I wouldn't want to introduce the possibility of error on my part, and the rsync checksum is a nice check that nothing has gotten corrupted over time from network or disk glitch. (Excluding disk glitch on the primary...)

You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade ourput should be deterministic, right? So if you make sure that clients are disconnected and standby is completely in sync before starting, why not? Maybe you could. But because it's not designed nor documented for that use, so although you likely could make it work, that's a dangerous path. The last thing you want to do is take your server down for this scheduled operation, and wind up at the end with an unusable standby.

Dear Anton

I need the type of backup that I can take from primary to replica regular backup or base backup or copy data director. 

Thanks

في الثلاثاء، ٧ يناير ٢٠٢٥ ٥:٤١ م Dischner, Anton <Anton.Dischner@med.uni-muenchen.de> كتب:

Hi,

if you are using the same slow internet connection for WAL and data-transfer you might considering to use rsync which has a full set of throttling, resuming/ibcremential and as mentioned data-compression options so that you do not saturate you connection,

BTW nice challenge,

Best,

Anton

-----Ursprüngliche Nachricht-----
Von: Scott Ribe <scott_ribe@elevated-dev.com>
Gesendet: Dienstag, 7. Januar 2025 15:42
An: kasem adel <kasemadel8@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

> Are you mean do the following step:
>
> 1- upgrade primary node
> 2- take base backup from new  version in primary node and keep wal file for 3 days
> 3- move backup by external hard disk to the replica node
> 4- restore base backup to replica
> 5- start replication to replicate delta from primary node.
>
> Please confirm if this the best approach and it will work without risk.

Yes, that's what I meant. It will work, nothing is completely without risk ;-) Main thing is to make absolutely sure you don't lose WAL during that time period. If you could set up WAL archiving to push to the remote site, that would be great as then you could configure the remote to pull accumulated WAL locally instead of across the slow network link.

But, just thought of this:

- pg_upgrade both sides
- with neither side running, rsync the data directories (bonus points for being paranoid and using -c)
- fix up the standby flag on the standby
- fix up the postgresql.conf -- for instance, standby config has been moved out of a separate file into the main one, so that you can have common config both sides now, with the only necessary difference being the standby flag
- bring them up

You could even try to figure out where the catalog tables are stored and only rsync those, since pg_)upgrade doesn't change the format of your data files. But personally, I wouldn't. I wouldn't want to introduce the possibility of error on my part, and the rsync checksum is a nice check that nothing has gotten corrupted over time from network or disk glitch. (Excluding disk glitch on the primary...)

You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade ourput should be deterministic, right? So if you make sure that clients are disconnected and standby is completely in sync before starting, why not? Maybe you could. But because it's not designed nor documented for that use, so although you likely could make it work, that's a dangerous path. The last thing you want to do is take your server down for this scheduled operation, and wind up at the end with an unusable standby.

Hi,

if you are using the same slow internet connection for WAL and data-transfer you might considering to use rsync which has a full set of throttling, resuming/ibcremential and as mentioned data-compression options so that you do not saturate you connection,

BTW nice challenge,

Best,

Anton

-----Ursprüngliche Nachricht-----
Von: Scott Ribe <scott_ribe@elevated-dev.com>
Gesendet: Dienstag, 7. Januar 2025 15:42
An: kasem adel <kasemadel8@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

> Are you mean do the following step:
>
> 1- upgrade primary node
> 2- take base backup from new  version in primary node and keep wal file for 3 days
> 3- move backup by external hard disk to the replica node
> 4- restore base backup to replica
> 5- start replication to replicate delta from primary node.
>
> Please confirm if this the best approach and it will work without risk.

Yes, that's what I meant. It will work, nothing is completely without risk ;-) Main thing is to make absolutely sure you don't lose WAL during that time period. If you could set up WAL archiving to push to the remote site, that would be great as then you could configure the remote to pull accumulated WAL locally instead of across the slow network link.

But, just thought of this:

- pg_upgrade both sides
- with neither side running, rsync the data directories (bonus points for being paranoid and using -c)
- fix up the standby flag on the standby
- fix up the postgresql.conf -- for instance, standby config has been moved out of a separate file into the main one, so that you can have common config both sides now, with the only necessary difference being the standby flag
- bring them up

You could even try to figure out where the catalog tables are stored and only rsync those, since pg_)upgrade doesn't change the format of your data files. But personally, I wouldn't. I wouldn't want to introduce the possibility of error on my part, and the rsync checksum is a nice check that nothing has gotten corrupted over time from network or disk glitch. (Excluding disk glitch on the primary...)

You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade ourput should be deterministic, right? So if you make sure that clients are disconnected and standby is completely in sync before starting, why not? Maybe you could. But because it's not designed nor documented for that use, so although you likely could make it work, that's a dangerous path. The last thing you want to do is take your server down for this scheduled operation, and wind up at the end with an unusable standby.

Hello,

Could you first clarify the reason for using 'pg_dump' in the upgrade method?

In a PostgreSQL database environment managed by a Patroni cluster, if there aren't overly complex extensions in use, you can replicate the database to the new environment and then perform a failover during the upgrade process. By using the 'pg_upgrade' method, you can first run it with the '--check' flag to ensure compatibility, and then proceed with the upgrade.

Since the PostgreSQL cluster will be initialized with Patroni, there should be no issues, and this approach will likely reduce your workload.

If your database is small or involves a highly complex structure, 'pg_dump' is indeed an option. However, is it truly necessary? Testing this approach would provide more clarity. 

Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>, 7 Oca 2025 Sal, 18:41 tarihinde şunu yazdı:

Hi,

if you are using the same slow internet connection for WAL and data-transfer you might considering to use rsync which has a full set of throttling, resuming/ibcremential and as mentioned data-compression options so that you do not saturate you connection,

BTW nice challenge,

Best,

Anton

-----Ursprüngliche Nachricht-----
Von: Scott Ribe <scott_ribe@elevated-dev.com>
Gesendet: Dienstag, 7. Januar 2025 15:42
An: kasem adel <kasemadel8@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

> Are you mean do the following step:
>
> 1- upgrade primary node
> 2- take base backup from new  version in primary node and keep wal file for 3 days
> 3- move backup by external hard disk to the replica node
> 4- restore base backup to replica
> 5- start replication to replicate delta from primary node.
>
> Please confirm if this the best approach and it will work without risk.

Yes, that's what I meant. It will work, nothing is completely without risk ;-) Main thing is to make absolutely sure you don't lose WAL during that time period. If you could set up WAL archiving to push to the remote site, that would be great as then you could configure the remote to pull accumulated WAL locally instead of across the slow network link.

But, just thought of this:

- pg_upgrade both sides
- with neither side running, rsync the data directories (bonus points for being paranoid and using -c)
- fix up the standby flag on the standby
- fix up the postgresql.conf -- for instance, standby config has been moved out of a separate file into the main one, so that you can have common config both sides now, with the only necessary difference being the standby flag
- bring them up

You could even try to figure out where the catalog tables are stored and only rsync those, since pg_)upgrade doesn't change the format of your data files. But personally, I wouldn't. I wouldn't want to introduce the possibility of error on my part, and the rsync checksum is a nice check that nothing has gotten corrupted over time from network or disk glitch. (Excluding disk glitch on the primary...)

You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade ourput should be deterministic, right? So if you make sure that clients are disconnected and standby is completely in sync before starting, why not? Maybe you could. But because it's not designed nor documented for that use, so although you likely could make it work, that's a dangerous path. The last thing you want to do is take your server down for this scheduled operation, and wind up at the end with an unusable standby.

Dear sozcn

No I mean when take backup to restore it to replica after upgrade what type of backup I  need to take to enable replication after restore backup in my last email to upgrade.

Thanks

في الثلاثاء، ٧ يناير ٢٠٢٥ ١١:٤٣ م SOzcn <selahattinozcnma@gmail.com> كتب:

Hello,

Could you first clarify the reason for using 'pg_dump' in the upgrade method?

In a PostgreSQL database environment managed by a Patroni cluster, if there aren't overly complex extensions in use, you can replicate the database to the new environment and then perform a failover during the upgrade process. By using the 'pg_upgrade' method, you can first run it with the '--check' flag to ensure compatibility, and then proceed with the upgrade.

Since the PostgreSQL cluster will be initialized with Patroni, there should be no issues, and this approach will likely reduce your workload.

If your database is small or involves a highly complex structure, 'pg_dump' is indeed an option. However, is it truly necessary? Testing this approach would provide more clarity. 

Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>, 7 Oca 2025 Sal, 18:41 tarihinde şunu yazdı:

Hi,

if you are using the same slow internet connection for WAL and data-transfer you might considering to use rsync which has a full set of throttling, resuming/ibcremential and as mentioned data-compression options so that you do not saturate you connection,

BTW nice challenge,

Best,

Anton

-----Ursprüngliche Nachricht-----
Von: Scott Ribe <scott_ribe@elevated-dev.com>
Gesendet: Dienstag, 7. Januar 2025 15:42
An: kasem adel <kasemadel8@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

> Are you mean do the following step:
>
> 1- upgrade primary node
> 2- take base backup from new  version in primary node and keep wal file for 3 days
> 3- move backup by external hard disk to the replica node
> 4- restore base backup to replica
> 5- start replication to replicate delta from primary node.
>
> Please confirm if this the best approach and it will work without risk.

Yes, that's what I meant. It will work, nothing is completely without risk ;-) Main thing is to make absolutely sure you don't lose WAL during that time period. If you could set up WAL archiving to push to the remote site, that would be great as then you could configure the remote to pull accumulated WAL locally instead of across the slow network link.

But, just thought of this:

- pg_upgrade both sides
- with neither side running, rsync the data directories (bonus points for being paranoid and using -c)
- fix up the standby flag on the standby
- fix up the postgresql.conf -- for instance, standby config has been moved out of a separate file into the main one, so that you can have common config both sides now, with the only necessary difference being the standby flag
- bring them up

You could even try to figure out where the catalog tables are stored and only rsync those, since pg_)upgrade doesn't change the format of your data files. But personally, I wouldn't. I wouldn't want to introduce the possibility of error on my part, and the rsync checksum is a nice check that nothing has gotten corrupted over time from network or disk glitch. (Excluding disk glitch on the primary...)

You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade ourput should be deterministic, right? So if you make sure that clients are disconnected and standby is completely in sync before starting, why not? Maybe you could. But because it's not designed nor documented for that use, so although you likely could make it work, that's a dangerous path. The last thing you want to do is take your server down for this scheduled operation, and wind up at the end with an unusable standby.