Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

This would help me. I will try out the pg_rewind and rsync options.

On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov <ilan(at)tzirechnoy(dot)com> wrote:

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