Обсуждение: Upgrade & Rollback plan: My customer requests rollback via old-version standby (13 ↔ 17) — need community advice

Hi all,

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?

Any insight, best practice, or reference architecture from the
community would be very helpful.
Thank you for your time and guidance. Have a good day!

--
Best Regards,

Miles Le | Vu (Mr.)

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

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?

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.)

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.

On Sat, 2025-10-11 at 23:02 +0700, Vu Le (JData - HN) wrote:
> If possible, could you please share any additional best practices or
> important considerations apart from testing the new version in a
> staging environment?

All I can think of is:

- Always keep a backup around.

- Read the "Migration to version x" section of the release notes of v14,
  v15, v16 and v17 before you test the application.  That will give you
  ideas what areas to test particularly well.

- Test your administrative procedures too.  For example, v15 removed
  the exclusive online file system backup.

Yours,
Laurenz Albe

On Sat, Oct 11, 2025 at 08:16:42PM +0200, Laurenz Albe wrote:
> On Sat, 2025-10-11 at 23:02 +0700, Vu Le (JData - HN) wrote:
> > If possible, could you please share any additional best practices or
> > important considerations apart from testing the new version in a
> > staging environment?
> 
> All I can think of is:
> 
> - Always keep a backup around.
> 
> - Read the "Migration to version x" section of the release notes of v14,
>   v15, v16 and v17 before you test the application.  That will give you
>   ideas what areas to test particularly well.
> 
> - Test your administrative procedures too.  For example, v15 removed
>   the exclusive online file system backup.

Agreed, and this might help for reviewing the release notes:

    https://momjian.us/main/blogs/pgblog/2022.html#June_13_2022

-- 
  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.

Dear Bruce and Laurenz,

Thank you very much for taking the time to share your valuable advice with me.

The information you provided was highly useful, and I appreciate the
specific guidance.

I intend to dedicate time to research your suggestions further.

I look forward to applying this knowledge. Thank you again for your
time and expertise.

Best regards,
Miles Le | Vu (Mr.)

On Sun, Oct 12, 2025 at 1:50 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Sat, Oct 11, 2025 at 08:16:42PM +0200, Laurenz Albe wrote:
> > On Sat, 2025-10-11 at 23:02 +0700, Vu Le (JData - HN) wrote:
> > > If possible, could you please share any additional best practices or
> > > important considerations apart from testing the new version in a
> > > staging environment?
> >
> > All I can think of is:
> >
> > - Always keep a backup around.
> >
> > - Read the "Migration to version x" section of the release notes of v14,
> >   v15, v16 and v17 before you test the application.  That will give you
> >   ideas what areas to test particularly well.
> >
> > - Test your administrative procedures too.  For example, v15 removed
> >   the exclusive online file system backup.
>
> Agreed, and this might help for reviewing the release notes:
>
>         https://momjian.us/main/blogs/pgblog/2022.html#June_13_2022
>
> --
>   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.