How to plan and perform a safe rollback for a PostgreSQL major upgrade (12→14) in production with a read replica — practical guide

By · Published on · 10 min read

Practical guide for safe rollback during PostgreSQL major upgrade 12→14 with a read replica
Photo: panumas nikhomkhai / Pexels

A practical guide to planning and executing a safe rollback when upgrading PostgreSQL from 12 to 14 in production with a read replica. Covers prerequisites, when to use pg_upgrade or logical replication, how to perform the cutover, and how to revert safely — minimizing downtime and data loss with a simple, rehearsed plan.

Scenario, risks and objective of the rollback plan

For PostgreSQL major upgrades, physical streaming replication won’t work across different major versions. Your 12 read replica won’t follow a 14 primary. The plan must therefore cover how to migrate, cut traffic, and return quickly if needed.

The objective is to upgrade to 14 with minimal downtime, keep a clear and documented rollback path, and reduce data-loss risk if a revert becomes necessary.

Capacity and connections matter on cutover day. If the connection pool is exhausted you can face queues and timeouts. See guidance on diagnosing and fixing this in the Postgres guide on "Too many connections": how to diagnose and resolve 'Too many connections' (/en/postgres-too-many-connections-billing-peak).

  • A 12 replica cannot "become" a 14 replica and continue following a 12 primary.
  • A safe rollback requires keeping the 12 cluster intact until the 14 has stabilized.
  • Without reverse replication configured, rolling back may discard writes made after the cutover.
  • Plan a short maintenance window with a DDL freeze (app change freeze).

Prerequisites and critical checks before the upgrade

Inventory your environment and reduce unknowns. Have tested backups and a viable recovery point (PITR). Confirm disk space to keep the 12 cluster intact while 14 stabilizes.

Validate: compatible extensions and their versions, configuration parameters, long-running transactions, foreign keys disabled where needed, whether logical replication is possible (if you choose it), and DNS/route TTLs for a fast flip. Adjust the pooler for smooth reconnections.

Below are useful queries to map extensions, long sessions and WAL/slots configuration.

  • Full tested backups + rehearsal of restore (PITR).
  • Free space for a side-by-side 14 cluster alongside 12.
  • DDL freeze and application change freeze during the window.
  • Reduce DNS/route TTLs in advance.
  • Review extensions and their versions.
  • Pooler (PgBouncer) prepared for the migration.
-- Extensões instaladas
SELECT extname, extversion FROM pg_extension ORDER BY 1;

-- Transações longas (potencial bloqueio no corte)
SELECT pid, usename, xact_start, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND state <> 'idle'
ORDER BY xact_start;

-- Nível de WAL e slots (para replicação lógica)
SHOW wal_level;         -- deve ser 'logical' para pub/sub
SHOW max_replication_slots;
SHOW max_wal_senders;

Replica-based strategies: which path to choose

Choose an approach based on acceptable downtime, rollback complexity and available resources. A read replica helps create a consistent clone for testing or as the basis for the 14 cluster, but at some point you must break replication.

Consider trade-offs between speed of cutover, operational complexity and potential data loss on rollback.

Strategy Downtime Rollback When to use Notes
A) pg_upgrade on a copy of the 12 cluster Minutes up to a few hours (cutover) Simple: stop 14 and return to 12 (writes after cut lost) Environments with a short window and acceptance of minimal possible data loss on rollback Avoid --link; keep the 12 intact until 14 stabilizes
B) Logical replication 12→14 (publication/subscription) Low to very low Medium: can return to 12; zero-loss rollback requires reverse replication or blocking writes during the smoke period When you need minimal downtime and want to validate 14 with real data Requires wal_level=logical and handling tables without PKs/replication edge cases
C) Dump/restore (pg_dump/pg_restore) High Simple: if failure occurs you don't cut over Smaller databases and when higher downtime is acceptable Simpler but with greater unavailability

Plan A: pg_upgrade using the replica (no --link)

Use the replica as the source for the 14 cluster without risking the 12 primary. Do not use --link if you require guaranteed rollback; --link modifies files in the source cluster.

Summary: stop the 12 replica, copy its datadir to a new location, initialize 14, run pg_upgrade, test, cutover, and only then decommission the 12 after an observation window.

  • Stop the 12 replica and ensure consistency.
  • Clone the datadir to a new folder/disk.
  • initdb for 14 and pg_upgrade --check.
  • Run pg_upgrade and perform post-upgrade analysis (analyze/vacuum).
  • Short cutover: point the app to 14.
  • Keep 12 offline and intact for rollback.
# Parar a réplica 12 (ajuste caminhos conforme seu ambiente)
/path/to/old/bin/pg_ctl -D /pgdata/12/replica stop

# Clonar dados da réplica para área do upgrade (cópia completa)
rsync -aH --delete /pgdata/12/replica/ /pgdata/12/upgrade-copy/

# Inicializar cluster 14
/path/to/new/bin/initdb -D /pgdata/14/main

# Verificação prévia
/path/to/new/bin/pg_upgrade \
  --old-datadir=/pgdata/12/upgrade-copy \
  --new-datadir=/pgdata/14/main \
  --old-bindir=/path/to/old/bin \
  --new-bindir=/path/to/new/bin \
  --check

# Executar upgrade (sem --link)
/path/to/new/bin/pg_upgrade \
  --old-datadir=/pgdata/12/upgrade-copy \
  --new-datadir=/pgdata/14/main \
  --old-bindir=/path/to/old/bin \
  --new-bindir=/path/to/new/bin

Plan B: logical replication 12→14 to minimize downtime

With publication/subscription the 14 cluster receives data from 12 while you validate performance. Requirements: wal_level=logical, primary keys on replicated tables, and handling sequences and DDL. For a zero-loss rollback, keep writes blocked during the smoke period or implement careful reverse replication.

Enable logical replication, create the publication on 12, initialize 14 and create the subscription, then monitor sync and lag. Rehearse the cutover: block writes, wait for catch-up, and switch the endpoint.

  • Enable wal_level=logical on the 12 and restart.
  • Create a publication on 12 (filter out temporary tables as needed).
  • Initialize 14 and create the subscription pointing to 12.
  • Monitor synchronization and replication lag.
  • Rehearse the cutover: block writes, wait for catch-up, and change the endpoint.
-- No 12 (primário)
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
-- Reinicie o 12 para aplicar wal_level

-- Publicação (ajuste filtros conforme necessário)
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- No 14 (novo cluster)
-- Crie roles, extensions e estruturas antes da assinatura
CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=old12 dbname=app user=replicator password=secret port=5432'
PUBLICATION upgrade_pub
WITH (copy_data = true, create_slot = true, slot_name = 'upgrade_slot');

Tests and validations before cutover

Run the analysis script generated by pg_upgrade (or use vacuumdb) to build accurate statistics. Compare cardinality for critical tables, checksums of samples, and results of known queries. Run low-intensity read/write loads in staging connected to the 14 cluster.

Validate application migrations and schema rollback plans in the Migrations & rollbacks guide (/en/postgresql-migrations-rollbacks). If you see plan regressions or stats issues, follow techniques from Recover PostgreSQL Indexes and Statistics (/en/recover-postgresql-indexes-statistics).

  • Run analyze/vacuum for stable query plans.
  • Compare row counts and critical aggregates per table.
  • Rerun heavy reports and cache-populating queries.
  • Test pooler and application reconnection behavior.
  • Verify roles, permissions and search_path.
# Após pg_upgrade, execute o script gerado
./analyze_new_cluster.sh

# Alternativa: analisar tudo com paralelismo
vacuumdb --all --analyze-in-stages --jobs=4

# Migrar objetos globais (roles, tablespaces)
pg_dumpall --globals-only | psql -h new14 -U postgres

Controlled cutover and rollback window

Organize a reversible cutover. Block application writes for a short period to eliminate divergence during the smoke tests. Drain old sessions, point the endpoint to 14 and monitor latency, locks and errors. Keep the 12 offline, intact and ready to return.

If you use DNS, reduce TTL days in advance. With HAProxy/PgBouncer swap the backend and test new connections first. Document cutover time and applied migrations.

  • Temporarily block writes at the app/role level.
  • Drain sessions and terminate active connections.
  • Flip the endpoint (DNS/HAProxy/PgBouncer).
  • Automated and manual smoke tests.
  • Intensive observation for an agreed period.
-- Exemplo: drenar sessões do banco 'app' (execute como superusuário)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'app' AND pid <> pg_backend_pid();

-- Opcional: tornar a role do app somente leitura durante o smoke
ALTER ROLE app SET default_transaction_read_only = on; -- reverter depois

Safe rollback: criteria, execution and post-rollback

Define objective criteria to trigger a rollback: detected corruption, severe performance regressions, critical application failures, or incompatible extensions. The rollback must be fast and repeatable.

Typical execution: stop 14, point endpoints back to 12, and re-enable writes only after basic checks. Log writes lost on 14 (if any) and plan manual reconciliation.

To reduce data loss keep writes blocked during smoke. Alternatively, implement reverse logical replication 14→12 before cutover, knowing it adds complexity and potential conflicts. As a last resort, use PITR; see the point-in-time recovery guide (/en/recover-dropped-postgresql-table-pitr) for details.

This guide exists to help prepare objective criteria, rehearsed steps and continuous communication with stakeholders during the upgrade and potential rollback.

  • Clear approval criteria and named approvers for rollback.
  • Rehearsed and timed rollback steps.
  • Business communication and impact logging.
  • Data reconciliation plan if needed.
  • Checklist to re-open traffic on the 12 cluster.

Frequently asked questions

Can I keep the 12 replica following a 14 primary after the upgrade?

No. Physical streaming replication is version-specific. Clusters 12 and 14 cannot stream-replicate between each other. Use a side-by-side approach with pg_upgrade on a copy, or use logical replication (publication/subscription) to move data between differing major versions.

How can I minimize cutover downtime to the 14 cluster?

Prepare everything in advance: updated statistics, migrated roles and permissions, validate in staging, low DNS TTL, automated flip via HAProxy/PgBouncer and a short write block. With logical replication the 14 can be nearly synchronized and the cutover is fast.

Is rollback possible without data loss?

Possible but more complex. You must block writes during the smoke period or configure reverse logical replication 14→12 with conflict resolution strategies. Without those, returning to 12 will discard writes made on 14 after the cutover.

What should I check about extensions for the 12→14 upgrade?

Confirm each extension has a compatible version on 14 and a migration plan. Upgrade extensions at the correct time and validate related functions, types and indexes. If unsure, isolate the extension schema in tests before cutting traffic.

Do I need to run VACUUM/ANALYZE after the upgrade?

Yes. Updated statistics are essential for good query plans. Run the script generated by pg_upgrade or use vacuumdb --analyze-in-stages. After cutover, monitor critical queries and adjust costs, indexes and parameters if you see regressions.

How are sequences handled with logical replication?

Sequences are not replicated automatically. Initialize sequence values manually on 14 after the initial copy or synchronize them with scripts. Ensure there are no overlapping values and validate keys before allowing writes on the new cluster.

Want a second opinion on your upgrade and rollback plan?

If you need a review of your cutover design, validations and fallback plan, let's discuss your environment and constraints.

Discuss the upgrade

See also