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

In this case, I don’t think there’s a need to complicate the steps. If I
were in your position, I would likely follow the steps outlined below:

- Set up a Patroni Cluster, then disable the Patroni Cluster service.
- Install the same PostgreSQL version in the new environment and create a
replica using pg_basebackup.
- Set up a cluster for the new PostgreSQL version you intend to use in the
new environment, whether it’s 15, 16, or 17, based on your preference.
- Update the Patroni Cluster configuration to point to the data path of the
new version.
- When it’s time to upgrade, failover to the old version to make it the - --
- Primary, then perform the upgrade. Once upgraded, transition to the new
version, upgrade it, and enable the Patroni Cluster.
- Afterward, to maintain the health of the environment, initiate backup
processes. If you are using pgbackrest, start the backup workflows. If not,
configure regular backups using pg_basebackup or pg_dump and automate them
with crontab.

This is a general approach I’ve followed in similar cases.

kasem adel <kasemadel8(at)gmail(dot)com>, 8 Oca 2025 Çar, 02:40 tarihinde şunu
yazdı:

> 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(at)gmail(dot)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(dot)Dischner(at)med(dot)uni-muenchen(dot)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(at)elevated-dev(dot)com>
>>> Gesendet: Dienstag, 7. Januar 2025 15:42
>>> An: kasem adel <kasemadel8(at)gmail(dot)com>
>>> Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)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.
>>>
>>>
>>>
>>>
>>>
>>>