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: SOzcn <[email protected]>
Cc: 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: Wed, 8 Jan 2025 01:38:51 +0200
Message-ID: <CAP-pBm9b7=yBWrsniWtLeKSP1Vjiqto_Dk=Mbr7RvhOj0yfU8A@mail.gmail.com> (raw)
In-Reply-To: <CAJyV5AZapeVRytDFiawrOq4Ymt6kvqXf4h21_AJbp_MD+3VCGw@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]>
	<CAJyV5AZapeVRytDFiawrOq4Ymt6kvqXf4h21_AJbp_MD+3VCGw@mail.gmail.com>

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 <[email protected]> كتب:

>
>
> 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 <[email protected]>, 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 <[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], [email protected]
  Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster
  In-Reply-To: <CAP-pBm9b7=yBWrsniWtLeKSP1Vjiqto_Dk=Mbr7RvhOj0yfU8A@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