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 1tFFq2-00C8sE-D1 for pgsql-general@arkaria.postgresql.org; Sun, 24 Nov 2024 16:51:58 +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 1tFFq1-008Zpj-1d for pgsql-general@arkaria.postgresql.org; Sun, 24 Nov 2024 16:51:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFFq0-008Zpa-IH for pgsql-general@lists.postgresql.org; Sun, 24 Nov 2024 16:51:56 +0000 Received: from mail-pl1-x62a.google.com ([2607:f8b0:4864:20::62a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tFFpt-003Yl7-KP for pgsql-general@postgresql.org; Sun, 24 Nov 2024 16:51:55 +0000 Received: by mail-pl1-x62a.google.com with SMTP id d9443c01a7336-212a3067b11so24266745ad.3 for ; Sun, 24 Nov 2024 08:51:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732467109; x=1733071909; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=yyegK7fzylmzrz47USYmVgSC/VhA0kg/WqtF9Rrhw5E=; b=Nf/HjCNtdQeBwIs7HDzTR4OyYYP8W3jRsO+ry2sqRFifIVVbt6xgBMxBpOEbcmBJk4 xJFRER7iRhSr8tdLnUs2ZZKlyIT+igxV8hx2XHdKDNKKnkv149Atos3a3+dL3FnaT5et 0p/ZU2UJSkoa1lAdOwtNZo+LNOU3fkst79JgJ21pjYQVX++WARd7owelFs4Bnu6mHnXz kU/ALrEUJYkUU1gEAx5LaE6mGcm6Ro6PyMmsNLAJo+t0FJKfnire79s1ULDT6dqmgyxR mbQ8vsLX/QAV/3MbrgHSVvjj3DhbUDY7Tm4LqkgpRLuIg5rSXYmHwUJl6qeR5+fZ0aFz CRww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732467109; x=1733071909; h=cc: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=yyegK7fzylmzrz47USYmVgSC/VhA0kg/WqtF9Rrhw5E=; b=MSm+O5MGBZHIJkjX8o96Oqjoyv5u5AVgPz4SC1d8amrCZXKMZEDRTuIEwXt5jxdxRq KocMTesiwo0YzGhhvIdVuqvczEjxQOKLuTAmtK8RTIJ6MCr5Un9fsZNswYIV7TQqrdVB Ue35V68BqLQTweo7sFfLlRMzPCm1nNeb7jDvn8yGejXuraLveZv83j6ctnQ1MXB6B/dq MTgMu1p4x9Y1LYa6ojtY3AcL7PXvL3DE8vHHz3l3ACyY1wdkGttrQ8yAizz4fBvp1FoI aNp2ItZTVHcaCoAKvnViL5PXw1td46/Mxo+fTr9EWOgwYIBYw9Um7bDOLAr+6fhwOnnf QMCQ== X-Gm-Message-State: AOJu0YxuBzxja4dqxw2SER5z41I9sxS+iwl3AH32NsQ+nBFz/UFhW1sZ LrALJDeABeEqR7vqsB2NVPbUpl6gHCvC0JDg7vz5YaXtse/Sa0m/lLKqUtj3dN2aGPPJ+AYludb 0QDGQVR6kSOH+0fLr3SC50acDHN0= X-Gm-Gg: ASbGnctPFX4b966XhDotMU6dn4rgRbSQhD7/jQasBKq5MuJ+sm3HRtxYhPZW3gMfapD CEzK8cklYOLVs9EY6PrPXso/QV/V0bQ== X-Google-Smtp-Source: AGHT+IFCi7LiRiUERDgt0xmrdL1WPv6f0yE+dn5VHQpOvMNg2a271qx4CpuV7wRM/oCHGwAeA2zQz/ac/pQisjG/9vM= X-Received: by 2002:a17:902:d490:b0:212:4582:37fa with SMTP id d9443c01a7336-2129f7c1863mr132415975ad.53.1732467108706; Sun, 24 Nov 2024 08:51:48 -0800 (PST) MIME-Version: 1.0 References: <6c498f0e-64f9-449a-9b90-5cd72d00e2ef@aklaver.com> <2a7d96ac-83a7-4ddc-a3ce-9c637f2c1c76@aklaver.com> In-Reply-To: <2a7d96ac-83a7-4ddc-a3ce-9c637f2c1c76@aklaver.com> From: Subhash Udata Date: Sun, 24 Nov 2024 22:21:37 +0530 Message-ID: Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication To: Adrian Klaver Cc: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="0000000000009984970627ab6e93" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009984970627ab6e93 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I understand your point and appreciate the clarification. I have reviewed the references and now have a better understanding of the minor upgrade process. However, my concern lies in the fact that we are working with production servers, where downtime is not acceptable. Additionally, if a failover occurs due to a network issue or any other disaster, setting up replication again requires running the pg_basebackup command. For large databases, this process becomes a significant challenge, as running pg_basebackup for the entire cluster can be time-consuming and resource-intensive. On Sun, 24 Nov 2024 at 22:11, Adrian Klaver wrote: > On 11/24/24 08:36, Subhash Udata wrote: > > The reason to upgrade from 15.0 to 15.9 is this > > https://www.postgresql.org/support/security/CVE-2024-10979/ > > > > > > Here it is mentioned that this vulnerability is fixed in 15.9 > > So our organization wants an upgrade from 15.0 to 15.9 > > Sorry, I was not clear enough. When I said 'Why?' it was not referring > to reason you wanted to upgrade, it was why go through the whole > pg_basebackup process. Read this link: > > https://www.postgresql.org/support/versioning/ > > for why that is not necessary. > > > > > On Sun, 24 Nov 2024 at 21:48, Adrian Klaver > > wrote: > > > > On 11/24/24 08:05, 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. > > > * 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 stil= l > be > > > running version 15.0. > > > * Is it possible to establish streaming replication betwee= n > > 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= =E2=80=933 > > 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? > > > > Why? > > > > 15.0 --> 15.9(actually you want the latest release 15.10) is a mino= r > > upgrade it involves shutting down the servers installing the new > > version > > binaries on each and restarting them. > > > > You should read: > > > > https://www.postgresql.org/support/versioning/ > > > > > > It would be a good idea to go through the Release Notes here: > > > > https://www.postgresql.org/docs/15/release.html > > > > > > To see what changed. > > > > > > > > Your guidance and recommendations on these questions will be > greatly > > > appreciated. > > > > > > Thank you for your time and support! > > > > > > Best regards, > > > > > > Subhash > > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --0000000000009984970627ab6e93 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

I understand your point and appreciate= the clarification.

I have reviewed the references and now have a bet= ter understanding of the minor upgrade process.

However, my concern l= ies in the fact that we are working with production servers, where downtime= is not acceptable.

Additionally, if a failover occurs due to a netwo= rk issue or any other disaster, setting up replication again requires runni= ng the pg_basebackup command. For large databases, this proces= s becomes a significant challenge, as running pg_basebackup fo= r the entire cluster can be time-consuming and resource-intensive.


On Su= n, 24 Nov 2024 at 22:11, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/24/24 08:36, Subhash Udata wrot= e:
> The reason to upgrade from 15.0 to 15.9 is this
> https://www.postgresql.org/support/s= ecurity/CVE-2024-10979/
> <https://www.postgresql.org/suppo= rt/security/CVE-2024-10979/>
>
> Here it is mentioned that this vulnerability is fixed in 15.9
> So our organization wants an upgrade from 15.0 to 15.9

Sorry, I was not clear enough. When I said 'Why?' it was not referr= ing
to reason you wanted to upgrade, it was why go through the whole
pg_basebackup process. Read this link:

https://www.postgresql.org/support/versioning/

for why that is not necessary.

>
> On Sun, 24 Nov 2024 at 21:48, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 11/24/24 08:05, Subhash Udata wrote:
>=C2=A0 =C2=A0 =C2=A0 > Dear PostgreSQL Community,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I have a production database setup with a pri= mary server and a
>=C2=A0 =C2=A0 =C2=A0standby
>=C2=A0 =C2=A0 =C2=A0 > server. The database is currently running on = *PostgreSQL 15.0*,
>=C2=A0 =C2=A0 =C2=A0and I
>=C2=A0 =C2=A0 =C2=A0 > plan to upgrade both servers to *15.9*.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I have the following questions regarding the = upgrade and replication
>=C2=A0 =C2=A0 =C2=A0 > process:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 1.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0*Upgrade and Replication C= ompatibility*:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0* My plan is to per= form a failover, promote the standby server
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(currently 1= 5.0) to primary, and then upgrade the old primary
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0server to ve= rsion 15.9.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0* After upgrading t= he old primary server to version 15.9, I
>=C2=A0 =C2=A0 =C2=A0want
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0to configure= it as a standby server and set up streaming
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0replication = with the new primary server, which will still be
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0running vers= ion 15.0.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0* Is it possible to= establish streaming replication between
>=C2=A0 =C2=A0 =C2=A0these
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0two versions= (*15.0* as primary and *15.9* as standby)?
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 2.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0*Efficient Replication Set= up*:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0* The production da= tabase is around *1TB in size*, and creating
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0replication = using |pg_basebackup| is taking more than 2=E2=80=933
>=C2=A0 =C2=A0 =C2=A0hours
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0to complete.=
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0* Is there an alter= native method to set up replication without
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0taking a ful= l backup of the entire cluster but instead using
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0only the WAL= files that have changed on both servers?
>
>=C2=A0 =C2=A0 =C2=A0Why?
>
>=C2=A0 =C2=A0 =C2=A015.0 --> 15.9(actually you want the latest relea= se 15.10) is a minor
>=C2=A0 =C2=A0 =C2=A0upgrade it involves shutting down the servers insta= lling the new
>=C2=A0 =C2=A0 =C2=A0version
>=C2=A0 =C2=A0 =C2=A0binaries on each and restarting them.
>
>=C2=A0 =C2=A0 =C2=A0You should read:
>
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/sup= port/versioning/
>=C2=A0 =C2=A0 =C2=A0<https://www.postgresql.org= /support/versioning/>
>
>=C2=A0 =C2=A0 =C2=A0It would be a good idea to go through the Release N= otes here:
>
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/do= cs/15/release.html
>=C2=A0 =C2=A0 =C2=A0<https://www.postgresql.or= g/docs/15/release.html>
>
>=C2=A0 =C2=A0 =C2=A0To see what changed.
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Your guidance and recommendations on these qu= estions will be greatly
>=C2=A0 =C2=A0 =C2=A0 > appreciated.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Thank you for your time and support!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Best regards,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Subhash
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com

--0000000000009984970627ab6e93--