public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Vu Le (JData - HN) <[email protected]>
To: [email protected]
Subject: Re: Upgrade & Rollback plan: My customer requests rollback via old-version standby (13 ↔ 17) — need community advice
Date: Fri, 10 Oct 2025 11:01:31 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACT9sogdbwVqsXQeT-V8GP+GhhQrQcwmCPOCTtaPL35jHg5UXQ@mail.gmail.com>
References: <CACT9sogdbwVqsXQeT-V8GP+GhhQrQcwmCPOCTtaPL35jHg5UXQ@mail.gmail.com>
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
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Upgrade & Rollback plan: My customer requests rollback via old-version standby (13 ↔ 17) — need community advice
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox