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

On Sat, Oct 11, 2025 at 11:02:43PM +0700, Vu Le (JData - HN) wrote:
> Thank you very much, Laurenz.
> After reading several sources, I can confirm that this approach is
> indeed not feasible at all.
> I’m planning to prepare a short proposal and report to the customer,
> focusing on the major risks they would face rather than trying to
> implement it.
> 
> If possible, could you please share any additional best practices or
> important considerations apart from testing the new version in a
> staging environment?

Yeah, the odds of needing to revert to older Postgres releases is
minimal, so there isn't much discussion or infrastructure to make it
easy.  I think we do pretty well with upgrading.  ;-)

I know there are other unnamed databases where downgrading is a common
need.

---------------------------------------------------------------------------

> 
> Thank you once again for your guidance.
> Wishing you a pleasant weekend ahead!
> 
> 
> 
> On Fri, Oct 10, 2025 at 4:01 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> >
> > 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
> 
> 
> 
> --
> Best Regards,
> 
> Miles Le | Vu (Mr.)
> 
> 

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.