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

public inbox for [email protected]  
help / color / mirror / Atom feed

From: kasem adel <[email protected]>
To: Dischner, Anton <[email protected]>
Cc: Scott Ribe <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster
Date: Tue, 7 Jan 2025 19:22:18 +0200
Message-ID: <CAP-pBm-bmSJjM_xusQ+Mu83z0TBK677UkSz8j_zFDu=XO+RdMQ@mail.gmail.com> (raw)
In-Reply-To: <CAP-pBm9XKWLRxUCAksTw1K9pvoDpABwq_i8GrKD7ha+Rg8fAuw@mail.gmail.com>
References: <CAP-pBm9YJqrZQgrH1ZsFt9RbTsOTKH1niS5bEhhB_mSsT49B5Q@mail.gmail.com>
	<[email protected]>
	<CAP-pBm-Y=b6YxHPag0TLertO=7OCo1kN8RVjBg5mvJNx6Y56KA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAP-pBm-_SNXLaOfPbMxs5WWykwfMdJY6Nmgs21oHo4bJFFxXxg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAP-pBm9XKWLRxUCAksTw1K9pvoDpABwq_i8GrKD7ha+Rg8fAuw@mail.gmail.com>

Dear Anton

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

Thanks

في الثلاثاء، ٧ يناير ٢٠٢٥ ٦:٢٦ م kasem adel <[email protected]> كتب:

> 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 <
> [email protected]> كتب:
>
>> 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 <[email protected]>
>> Gesendet: Dienstag, 7. Januar 2025 15:42
>> An: kasem adel <[email protected]>
>> Cc: pgsql-admin <[email protected]>
>> 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.
>>
>>
>>
>>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster
  In-Reply-To: <CAP-pBm-bmSJjM_xusQ+Mu83z0TBK677UkSz8j_zFDu=XO+RdMQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox