Fallback and Staged Upgrade Plan for pg_auto_failover (PG15 to PG17)

Products

• VMware Tanzu for Postgres
• VMware Tanzu Data Suite

Issue/Introduction

Purpose

This article outlines the fallback strategy and interim operational requirements for upgrading a pg_auto_failover cluster from PostgreSQL 15 to PostgreSQL 17 in a staged manner (Primary + First Secondary first).

Environment

Context

In scenarios with greater than or equal to 3 nodes where limited maintenance windows prevent a full cluster upgrade, users may opt to upgrade only the primary and one secondary. This creates an interim state where the remaining PG15 nodes are temporarily incompatible with the upgraded monitor and primary.

---

Technical Constraints

• Version Incompatibility: PostgreSQL physical/streaming replication requires the standby to be on the same major version as the primary. A PG17 primary cannot stream to a PG15 standby.
• Monitor Compatibility: A monitor upgraded to the newer pg_auto_failover extension is not compatible with PG15 keepers. Keeping PG15 nodes registered against a PG17 monitor will cause the node-active process to exit and eventually stop PostgreSQL.
• Data Directory Shared Files: Using pg_upgrade --link makes the old cluster unsafe to use once the new PG17 cluster has started, as files are shared between versions.

Cause

Staged Upgrade Strategy (Node 1 & 2)

1. Pre-Upgrade Preparation

Before upgrading the first two nodes, you must manage the remaining PG15 nodes (Nodes 3–6) to prevent cluster instability.

• Drop PG15 Nodes: Remove Nodes 3–6 from the monitor or take them fully offline before the upgrade begins.
• Sync Settings: Check number_sync_standbys and synchronous_standby_names on the primary. If these are not adjusted before dropping nodes, the primary may block commits while waiting for standbys that are no longer present.

2. Post-Upgrade: Re-adding Nodes 3–6

Once Nodes 1 and 2 are on PG17, the remaining nodes must be rebuilt as fresh PG17 replicas.

• Option: pg_basebackup via pg_autoctl:
  • Use pg_autoctl create postgres --maximum-backup-rate to throttle bandwidth and reduce impact on the PG17 primary (e.g., set to 50M).
  • Stagger Re-addition: Do not re-add all nodes at once. Rebuild them sequentially to avoid multiplying the load on the primary.
• Option: pgBackRest:
  • Note that a PG15 backup cannot seed a PG17 node. A new backup must be taken from the PG17 primary first.
  • pg_auto_failover does not have native pgBackRest integration for node creation; pg_autoctl create postgres typically manages its own pg_basebackup.

---

Fallback Plan: The "Point of No Return"

The cutover to PG17 is a one-way door once the data directory is upgraded and the application begins writes.

Scenario A: Rollback Before Application Cutover

If the upgrade of Nodes 1 and 2 fails before the application is pointed to the new PG17 cluster:

1. Promote a PG15 Node: Promote one of the original standbys (e.g., Node 3) to be the new PG15 primary.
2. Rebuild Nodes 1 & 2: Re-provision Nodes 1 and 2 as PG15 standbys.

Scenario B: Rollback After Application Cutover (Unsafe)

Once the application is live on PG17, there is no supported automatic rollback to PG15.

• Why: PostgreSQL does not support downgrading major versions. Logic breaks (e.g., on TRUNCATE) when moving from PG17 back to PG15.
• Recovery: Rollback in this state requires restoring from a PG15 backup and manual reconciliation of any data generated during the PG17 window.

Resolution

Safer Alternative: Blue/Green Deployment

To avoid the risks of an in-place upgrade and the "Point of No Return," a Blue/Green strategy is recommended:

1. Build Green: Deploy a completely new pg_auto_failover PG17 cluster alongside the existing PG15 (Blue) cluster.
2. Validate: Fully test the Green cluster without touching production.
3. Cutover: Perform a short write freeze on Blue, migrate data, and point the application to Green.
4. Rollback: If issues occur during cutover, Blue remains unmodified and can be resumed immediately.