Re: Upgrade & Rollback plan: My customer requests rollback via old-version standby (13 ↔ 17) — need community advice

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.
>
> My customer has requested the following rollback approach, and I’d
> like to confirm if it’s technically feasible or advisable before
> proceeding.
>
> Scenario:
> 1. They have a **Primary–Standby 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 —
> minimizing downtime.
> 3. They also asked whether it’s 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.
>
> Constraints:
> - They **cannot use a Blue/Green or clone-based approach**, because of
> **limited storage resources**.
> - They also doesn’t want the old data directory to become outdated
> (they expects it could stay in sync with the upgraded node).
> - They only have **UAT and Production environments** (no dedicated Staging).
>
> Questions:
> 1. Is there **any supported or practical method** to replicate data
> *backward* (from PostgreSQL 17 to 13) — even temporarily, for rollback
> 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 = 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