public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ilya Anfimov <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
Date: Mon, 25 Nov 2024 12:48:51 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
References: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote:
> Dear PostgreSQL Community,
>
> I have a production database setup with a primary server and a standby
> server. The database is currently running on PostgreSQL 15.0, and I plan
> to upgrade both servers to 15.9.
>
> I have the following questions regarding the upgrade and replication
> process:
>
> 1. Upgrade and Replication Compatibility:
>
> * My plan is to perform a failover, promote the standby server
> (currently 15.0) to primary, and then upgrade the old primary
> server to version 15.9.
1) Why do you want to use a switchover first?
You can upgrade the standby, then switchover to it.
(You could even don't switchover back, when the old primary
would be upgraded and synchonized).
> * After upgrading the old primary server to version 15.9, I want to
> configure it as a standby server and set up streaming replication
> with the new primary server, which will still be running version
> 15.0.
> * Is it possible to establish streaming replication between these
> two versions (15.0 as primary and 15.9 as standby)?
> 2. Efficient Replication Setup:
>
> * The production database is around 1TB in size, and creating
> replication using pg_basebackup is taking more than 2-3 hours to
> complete.
> * Is there an alternative method to set up replication without
> taking a full backup of the entire cluster but instead using only
> the WAL files that have changed on both servers?
Well, there are some.
pg_rewind is one of those (you should keep all the WAL files be-
tween switchover point and now on both servers. Also, maximum one
switchover/failover AFAIK. Also, it's a bit fragile nevertheless,
bad things could happen if you mix timelines from the very
straight scenario of one switchover+pg_rewind on the old prima-
ry).
Hoewever, I'd usually use rsync+low-level backup protocol
https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
This requires some manual commands, writing backup_label and so
on -- but looks more straightforward to me.
(And yes, rsync uses block-level comparision and transfers only
change blocks.
setting block-size to 8k in rsync could be beneficial).
>
> Your guidance and recommendations on these questions will be greatly
> appreciated.
>
> Thank you for your time and support!
>
> Best regards,
>
> Subhash
view thread (9+ messages) latest in thread
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]
Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
In-Reply-To: <[email protected]>
* 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