Message-ID: <8b4a6869b1fca2d0c39777b76439e6f153ba8c76.camel@cybertec.at>
Subject: Re: Upgrade & Rollback plan: My customer requests rollback via
 old-version standby (13 =?UTF-8?Q?=E2=86=94?= 17) =?UTF-8?Q?=E2=80=94?=
 need community advice
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Vu Le (JData - HN)" <dba4@jprotech.com.vn>, 
	pgsql-general@lists.postgresql.org
Date: Fri, 10 Oct 2025 11:01:31 +0200
In-Reply-To: <CACT9sogdbwVqsXQeT-V8GP+GhhQrQcwmCPOCTtaPL35jHg5UXQ@mail.gmail.com>
References: 
	<CACT9sogdbwVqsXQeT-V8GP+GhhQrQcwmCPOCTtaPL35jHg5UXQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/8b4a6869b1fca2d0c39777b76439e6f153ba8c76.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-10-10 at 15:26 +0700, Vu Le (JData - HN) wrote:
> I'm currently planning a major version upgrade from PostgreSQL 13.x to
> 17.x in a production environment.
>=20
> My customer has requested the following rollback approach, and I=E2=80=99=
d
> like to confirm if it=E2=80=99s technically feasible or advisable before
> proceeding.
>=20
> Scenario:
> 1. They have a **Primary=E2=80=93Standby setup** (streaming replication).
> 2. Their idea is to **upgrade only the Primary** (to v17) first, while
> keeping the **Standby** on v13 (the old version).
>    - The upgraded Primary will run read/write traffic for about a week
> to validate stability.
>    - If any serious issue occurs, the plan is to **switch over**
> (promote the v13 Standby), adjust IPs, and resume operations there =E2=80=
=94
> minimizing downtime.
> 3. They also asked whether it=E2=80=99s possible for **data generated on =
the
> v17 Primary** to still be **replicated back to the v13 Standby**, so
> that rollback would be fast and without data loss.
>=20
> Constraints:
> - They **cannot use a Blue/Green or clone-based approach**, because of
> **limited storage resources**.
> - They also doesn=E2=80=99t want the old data directory to become outdate=
d
> (they expects it could stay in sync with the upgraded node).
> - They only have **UAT and Production environments** (no dedicated Stagin=
g).
>=20
> Questions:
> 1. Is there **any supported or practical method** to replicate data
> *backward* (from PostgreSQL 17 to 13) =E2=80=94 even temporarily, for rol=
lback
> purposes?
> 2. If not, what are the **recommended real-world rollback strategies**
> for a low-downtime upgrade under these constraints?
> 3. Are there open-source tools or logical replication setups (e.g.,
> pglogical, Bucardo, etc.) that could safely achieve something similar?

The only way to achieve something like that is to use logical replication.
You'd have to switch from streaming replication to logical replication:

- create a publication for all tables on the primary
- turn off the application
- promote the standby server
- create a subscription on the former standby with "copy_data =3D off"

Then you can upgrade the former primary with pg_upgrade --link and
restart the application.

After that, logical replication will keep the v13 machine updated.

Note that you cannot run any DDL statements on the database after that,
else replication will break.

You cannot upgrade the standby server, you'll have to discard the data
directory and start with a new pg_basebackup.

This is all pretty complicated and should be tested well.
But then, it might be a better idea to invest the testing effort into
testing the application on PostgreSQL v17, so that you are confident
that you won't need to downgrade.  That would allow you to use a simpler
and less risky form of upgrade.

Yours,
Laurenz Albe