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

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