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

Dear Anton

For regular backup I mean take dump from specific database that we are use.

Thanks

في الثلاثاء، ٧ يناير ٢٠٢٥ ٦:٢٦ م kasem adel <kasemadel8(at)gmail(dot)com> كتب:

> 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(dot)Dischner(at)med(dot)uni-muenchen(dot)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(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.
>>
>>
>>
>>