Date: Mon, 25 Nov 2024 12:48:51 +0300
From: Ilya Anfimov <ilan@tzirechnoy.com>
To: pgsql-general@lists.postgresql.org, pgsql-general@postgresql.org
Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting
 Up Streaming Replication
Message-ID: <Z0RIA1bmYS2t1mRg@azor.tzirechnoy.ru>
Mail-Followup-To: Ilya Anfimov <ilan@tzirechnoy.com>,
	pgsql-general@lists.postgresql.org, pgsql-general@postgresql.org
References: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Z0RIA1bmYS2t1mRg%40azor.tzirechnoy.ru>
Precedence: bulk

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