MIME-Version: 1.0
References: <CACT9sogdbwVqsXQeT-V8GP+GhhQrQcwmCPOCTtaPL35jHg5UXQ@mail.gmail.com>
 <8b4a6869b1fca2d0c39777b76439e6f153ba8c76.camel@cybertec.at>
In-Reply-To: <8b4a6869b1fca2d0c39777b76439e6f153ba8c76.camel@cybertec.at>
From: "Vu Le (JData - HN)" <dba4@jprotech.com.vn>
Date: Sat, 11 Oct 2025 23:02:43 +0700
Message-ID: <CACT9sojZgGUtpm64dqdAf5pRrBgxxsweMcR7mZFmmnoOjoRApw@mail.gmail.com>
Subject: =?UTF-8?Q?Re=3A_Upgrade_=26_Rollback_plan=3A_My_customer_requests_ro?=
	=?UTF-8?Q?llback_via_old=2Dversion_standby_=2813_=E2=86=94_17=29_=E2=80=94_need_communit?=
	=?UTF-8?Q?y_advice?=
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACT9sojZgGUtpm64dqdAf5pRrBgxxsweMcR7mZFmmnoOjoRApw%40mail.gmail.com>
Precedence: bulk

Thank you very much, Laurenz.
After reading several sources, I can confirm that this approach is
indeed not feasible at all.
I=E2=80=99m 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?

Thank you once again for your guidance.
Wishing you a pleasant weekend ahead!


On Fri, Oct 10, 2025 at 4:01=E2=80=AFPM 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=E2=80=
=99d
> > like to confirm if it=E2=80=99s technically feasible or advisable befor=
e
> > proceeding.
> >
> > 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 o=
n 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=E2=80=99t want the old data directory to become outda=
ted
> > (they expects it could stay in sync with the upgraded node).
> > - They only have **UAT and Production environments** (no dedicated Stag=
ing).
> >
> > Questions:
> > 1. Is there **any supported or practical method** to replicate data
> > *backward* (from PostgreSQL 17 to 13) =E2=80=94 even temporarily, for r=
ollback
> > 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


--
Best Regards,

Miles Le | Vu (Mr.)