Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFmsX-00Fxf8-EV for pgsql-general@arkaria.postgresql.org; Tue, 26 Nov 2024 04:08:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tFmsW-003T7s-1H for pgsql-general@arkaria.postgresql.org; Tue, 26 Nov 2024 04:08:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFmsV-003T7j-K5 for pgsql-general@lists.postgresql.org; Tue, 26 Nov 2024 04:08:43 +0000 Received: from mail-pj1-x102b.google.com ([2607:f8b0:4864:20::102b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tFmsS-003siu-AC for pgsql-general@lists.postgresql.org; Tue, 26 Nov 2024 04:08:42 +0000 Received: by mail-pj1-x102b.google.com with SMTP id 98e67ed59e1d1-2eacc4c9164so4465545a91.0 for ; Mon, 25 Nov 2024 20:08:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732594118; x=1733198918; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=VG6LpPQ4oJLmofFXYYHDB/mbFSvSP/+qAzyXKsgHNCk=; b=ACJlg2+MXy5QD+Z/O1veMg/Qodj93XtNCxShJUkCmtNBB9HsGCHzVwv1Q0+fq88Ljz dmbjOIJ0B1ujOVH2Inwd98eDEgaTEUC71m8E3F1Snpyoz94Bt2Os2HJo2xCUbKiOUQT6 4TgE/AP4a8XXeehobMgfw20mgOtj1WuWAyyfZnask7BoAm44G6cNiILib72Nm1Wdc7bE QYkq9DKP0ls9/I81b848jXQPrjtAAsLd5NxlHjDzZY3bOxzM3JJr66gc4Nl1br0G5mx3 JuWZ/3jZLChNs+o2LW+0a5aoVyvGfVWf1rYOmg7t7JwQ/HAqWSCaasG2MpeHvzRGO7W6 vv0w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732594118; x=1733198918; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=VG6LpPQ4oJLmofFXYYHDB/mbFSvSP/+qAzyXKsgHNCk=; b=anDQTE7g0chA/U8bJf/phOt/18kAOumWFl4tEBE4BT7w/uKzKD8Y7tifmmmhxRsN29 VoCSxoBEfjuAyTUouwV7SJabh9ZWCQG359LOhEI7REoA1LZ5viBTJhEoOLfTC/7LwCVL 1CmTOIN57gavnP7De5E2jMHEqQN7jDhECuLxiaU28YWhiA2yC5pvuVZ/HTdKZWMrwsP6 Ox9ciswZUthcubSNpzUTBOzaMzJp7FISjM62gVjQPEQ3OMws7i4jWV2o3IOGncJvCrg9 i83R+X8KSpuNg/wnR2umcCDhIrdpFyl8RbfqAodl5X9mH/nhqXBArcehLWjQQ+nIRUDR Mtmw== X-Forwarded-Encrypted: i=1; AJvYcCWohCjS9KC5Xd53gqKkgPf1Mj9yZcTVN7y52vzZG5l+1gLjuy+Y0ONooLMGU+XlFu3tO6hZB8sr1LKEpCx/@lists.postgresql.org X-Gm-Message-State: AOJu0Yy57esYDMkU3e23G/BBjaG/Hx1NepGC2/XqQuPpzjSiIoDR7xR2 AxHE9WQmrpW44gxZwDzEToJyFjxA6rxTzTIq2MmytAIPmx9KPQXpURBXonl3U+nmdARaCBEvJJM cTlQ6aAmOIKIklyNL0fX26zZkwxU= X-Gm-Gg: ASbGncv23z6AadSwAm0YDMtCMx7D0JtW6QqU2O4Dk4j3TQeMr93X242VxCEdeai9Zxq ENvqGO9zyPTSGNFRutaJxOnWH5EaqjQk= X-Google-Smtp-Source: AGHT+IFQjVNfpyLa35OyuSNWtmMwn2hmleMMx6bv6+fadm1mUITB0vyENF0dEMl21EZ2bCbtUDXPKK2QoF6v161U8FU= X-Received: by 2002:a17:90b:5111:b0:2ea:7755:a0fa with SMTP id 98e67ed59e1d1-2eb0e02b0efmr21248079a91.7.1732594117851; Mon, 25 Nov 2024 20:08:37 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Subhash Udata Date: Tue, 26 Nov 2024 09:38:26 +0530 Message-ID: Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication To: Ilya Anfimov , pgsql-general@lists.postgresql.org, pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000ef40570627c90062" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ef40570627c90062 Content-Type: text/plain; charset="UTF-8" This would help me. I will try out the pg_rewind and rsync options. On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov 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 > > > --000000000000ef40570627c90062 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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@tzirechnoy.com> wrote:
On Sun, Nov 24, 2024 at 09:35:15PM += 0530, Subhash Udata wrote:
>=C2=A0 =C2=A0 Dear PostgreSQL Community,
>
>=C2=A0 =C2=A0 I have a production database setup with a primary server = and a standby
>=C2=A0 =C2=A0 server. The database is currently running on PostgreSQL 1= 5.0, and I plan
>=C2=A0 =C2=A0 to upgrade both servers to 15.9.
>
>=C2=A0 =C2=A0 I have the following questions regarding the upgrade and = replication
>=C2=A0 =C2=A0 process:
>
>=C2=A0 =C2=A0 =C2=A01. Upgrade and Replication Compatibility:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* My plan is to perform a fail= over, promote the standby server
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(currently 15.0) to pri= mary, and then upgrade the old primary
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0server to version 15.9.=

=C2=A01) Why do you want to use a switchover first?
=C2=A0You can upgrade the standby, then switchover to it.
=C2=A0(You=C2=A0 could=C2=A0 even=C2=A0 don't=C2=A0 switchover=C2=A0 ba= ck, when the old primary
would be upgraded and synchonized).


>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* After upgrading the old prim= ary server to version 15.9, I want to
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0configure it as a stand= by server and set up streaming replication
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0with the new primary se= rver, which will still be running version
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A015.0.
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* Is it possible to establish = streaming replication between these
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0two versions (15.0 as p= rimary and 15.9 as standby)?
>=C2=A0 =C2=A0 =C2=A02. Efficient Replication Setup:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* The production database is a= round 1TB in size, and creating
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0replication using pg_ba= sebackup is taking more than 2-3 hours to
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0complete.
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* Is there an alternative meth= od to set up replication without
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0taking a full backup of= the entire cluster but instead using only
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0the WAL files that have= changed on both servers?

=C2=A0Well, there are some.

pg_rewind=C2=A0 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=C2=A0 if=C2=A0 you=C2=A0 mix=C2=A0 timelines=C2=A0 = from=C2=A0 the=C2=A0 very
straight=C2=A0 scenario=C2=A0 of one switchover+pg_rewind on the old prima-=
ry).

=C2=A0Hoewever, I'd usually use rsync+low-level backup protocol
=C2=A0https:= //www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE= -BACKUP

=C2=A0This=C2=A0 requires some manual commands, writing backup_label and so=
on -- but looks more straightforward to me.
=C2=A0(And yes, rsync uses block-level comparision and transfers=C2=A0 only=
change blocks.
=C2=A0setting block-size to 8k in rsync could be beneficial).

>
>=C2=A0 =C2=A0 Your guidance and recommendations on these questions will= be greatly
>=C2=A0 =C2=A0 appreciated.
>
>=C2=A0 =C2=A0 Thank you for your time and support!
>
>=C2=A0 =C2=A0 Best regards,
>
>=C2=A0 =C2=A0 Subhash


--000000000000ef40570627c90062--