Zero-Downtime PostgreSQL Major Version Upgrades: A Step-by-Step Guide with Logical Replication and pg_upgrade

Upgrading PostgreSQL between major versions (e.g. from Postgres 14 to Postgres 17) cannot be achieved via standard physical streaming replication because internal on-disk binary formats change. Shutting down the database for hours to run `pg_dump` and `pg_restore` is impossible for 24/7 global businesses. By implementing native PostgreSQL Logical Replication, engineering teams can sync live production data to a new target cluster in real time and execute a DNS cutover in under 30 seconds.

Architectural Takeaways

• Logical Replication streams row-level changes (DML) across different PostgreSQL major versions without locking tables or interrupting active transactions.
• Sequences do not synchronize automatically over logical replication; export sequence values immediately prior to the cutover window.
• Always configure reverse replication from the new target cluster back to the old cluster during switchover to allow instantaneous rollback without data loss.

1. In-Place pg_upgrade vs Logical Replication Blue-Green

`pg_upgrade --link` is fast but requires a complete database shutdown during execution and offers no instant rollback if queries perform unexpectedly. Logical replication creates a decoupled Blue-Green environment where the new cluster can be thoroughly benchmarked under read traffic before switching primary traffic.

2. Step-by-Step Publication & Subscription Configuration

On the source database, we set `wal_level = logical` and create a publication encompassing all application tables. The target database subscribes to the source, copying initial table states and streaming incremental changes.

3. Handling Sequences, Triggers & DDL Sync

Because logical replication does not replicate DDL schema migrations or sequence updates (`nextval()`), sequences must be queried on the source and updated on the target using `setval()` right before routing application traffic.

4. The 30-Second Cutover Runbook & Zero-Risk Rollback

During the cutover window: 1) Pause PgBouncer connections, 2) Sync final sequence values, 3) Re-enable triggers and foreign keys on target, 4) Point PgBouncer to the PostgreSQL 17 target cluster, 5) Resume traffic. Total downtime: under 20 seconds.

Read more technical guides on our Dispatches Index →