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 1tD79D-00G1HK-Lg for pgsql-admin@arkaria.postgresql.org; Mon, 18 Nov 2024 19:10:55 +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 1tD79B-006omy-HL for pgsql-admin@arkaria.postgresql.org; Mon, 18 Nov 2024 19:10:54 +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 1tD79B-006omq-4f for pgsql-admin@lists.postgresql.org; Mon, 18 Nov 2024 19:10:53 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tD799-002cVg-Cu for pgsql-admin@lists.postgresql.org; Mon, 18 Nov 2024 19:10:53 +0000 Content-Type: multipart/alternative; boundary="------------swaPWnyiKnmgpw4m2PGNi9Gx" Message-ID: <0b8c1b80-93f5-40a7-b67b-19e58207c12c@cloud.gatewaynet.com> Date: Mon, 18 Nov 2024 21:10:47 +0200 MIME-Version: 1.0 Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 To: pgsql-admin@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------swaPWnyiKnmgpw4m2PGNi9Gx Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 18/11/24 19:26, ο/η Ron Johnson έγραψε: > No.   Neither rsync nor streaming replication will do version upgrades. > > The only ways to do version upgrade are: > 1. pg_dump + pg_restore > 2. pg_upgrade > 3. Logical replication > I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap the roles of publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaks beyond repair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (such as sequences). > On Mon, Nov 18, 2024 at 12:20 PM Motog Plus wrote: > > Thanks Ron for your suggestion. > So for pg upgrade in streaming replication setup, rsync will be a > better option for standby upgrade if we are using --link option or > recreating the standby, once upgrade completes on primary? Any > suggestions on this please. > > Best Regards, > Raman > > > On Mon, Nov 18, 2024, 22:41 Ron Johnson > wrote: > > On Mon, Nov 18, 2024 at 11:08 AM Motog Plus > wrote: > > Dear Team, > > We are in the process of upgrading our PostgreSQL database > from version 12 to version 15. I would greatly appreciate > your advice on the following: > > 1. Would it be recommended to use the --link option with > the pg_upgrade command, from disk space point of view? > > > Yes. > > Are there any potential downsides to consider? > > > Very much: it's a one-way journey.  No rolling back if > something goes wrong! > > 2. Do you have any suggestions or best practices for > performing an efficient upgrade in a replication setup > (streaming replication) for both the primary and standby > servers? > > > Streaming replication is not an upgrade method.  Logical > replication is absolutely a valid method of upgrading PG on > one server to PG on a different server. > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > > > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! --------------swaPWnyiKnmgpw4m2PGNi9Gx Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


Στις 18/11/24 19:26, ο/η Ron Johnson έγραψε:
No.   Neither rsync nor streaming replication will do version upgrades.

The only ways to do version upgrade are:
1. pg_dump + pg_restore
2. pg_upgrade
3. Logical replication

I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap the roles of publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaks beyond repair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (such as sequences).

On Mon, Nov 18, 2024 at 12:20 PM Motog Plus <mplus7535@gmail.com> wrote:
Thanks Ron for your suggestion.
So for pg upgrade in streaming replication setup, rsync will be a better option for standby upgrade if we are using --link option or recreating the standby, once upgrade completes on primary? Any suggestions on this please.

Best Regards,
Raman


On Mon, Nov 18, 2024, 22:41 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <mplus7535@gmail.com> wrote:
Dear Team,

We are in the process of upgrading our PostgreSQL database from version 12 to version 15. I would greatly appreciate your advice on the following:

1. Would it be recommended to use the --link option with the pg_upgrade command, from disk space point of view?

Yes.
 
Are there any potential downsides to consider?

Very much: it's a one-way journey.  No rolling back if something goes wrong!
 
2. Do you have any suggestions or best practices for performing an efficient upgrade in a replication setup (streaming replication) for both the primary and standby servers?

Streaming replication is not an upgrade method.  Logical replication is absolutely a valid method of upgrading PG on one server to PG on a different server.
 
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!


--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
--------------swaPWnyiKnmgpw4m2PGNi9Gx--