Zero‑downtime PostgreSQL major version upgrade using logical replication

Problem & Takeaway

\n

The goal is to move a production PostgreSQL cluster from an older major version to a newer one while keeping downtime to a few seconds or less. A zero‑downtime upgrade means users experience only a brief pause, if any, during the cutover.

\n\n

Prerequisites

\n

\n
• Source and target servers run PostgreSQL 10 or later; the source may be older but must support logical replication.
\n
• Network connectivity between the two hosts and the ability to open port 5432 from target to source.
\n
• Sufficient disk space for WAL files and for replication slots that may hold WAL until they are shipped.
\n
• A replication user on the source with REPLICATION privilege and the ability to create publications and subscriptions.
\n
• Access to both servers as a superuser or a role with the needed privileges to run the commands below.
\n

\n\n

Procedure

\n

\n
1. On the source server (run as a superuser):\n

   CREATE PUBLICATION pub_all FOR ALL TABLES;

   \n

   This publishes every table in the database. The command requires the REPLICATION privilege.

   \n\n
2. On the target server (also as superuser):\n

   CREATE SUBSCRIPTION sub_all\n  CONNECTION 'host= port=5432 user= password= dbname='\n  PUBLICATION pub_all;

   \n

   Replace placeholders with the actual host name or IP, the replication user name, its password, and the target database name. The target must have permission to create a subscription.

   \n\n
3. Allow the initial data copy to finish. The first synchronization transfers a full copy of the data and then streams changes. Monitor progress with:\n

   SELECT * FROM pg_stat_subscription;

   \n

   Look for the state column; it should show streaming and the received_lag and flush_lag values should be decreasing.

   \n\n
4. When the lag columns report zero and the state remains streaming, the data is in sync. At this point you can disable further writes on the target to avoid divergent changes:\n

   ALTER SUBSCRIPTION sub_all SET (enabled = false);

   \n

   This stops the subscription from applying incoming WAL, effectively freezing the target at the current state.

   \n\n
5. Redirect application traffic to the target. Update your connection strings or DNS records so that new connections go to the target server. Because the target is now a read‑only copy of the source, you may need to pause writes briefly while the switch occurs, but the pause is typically measured in seconds.
   \n\n
6. Optionally, clean up:\n

   DROP SUBSCRIPTION sub_all;

   \n

   and drop the publication if it is no longer needed on the source:\n

   DROP PUBLICATION pub_all;

   \n

   These commands remove the replication objects and free resources.

   \n

\n\n

Verification Checks

\n

\n
• Query pg_stat_subscription on the target again. Verify that sync_state is sync, received_lag and flush_lag are zero, and that state is streaming (or sync depending on version).
\n
• Run a quick count comparison on a few large tables:\n

  SELECT relname, reltuples FROM pg_stat_user_tables WHERE relname IN ('big_table_1','big_table_2');

  \n

  Execute the same query on both source and target and confirm the row counts match within an acceptable tolerance.

  \n\n
• Test application connectivity by issuing a simple INSERT or SELECT on the target and confirming the transaction commits without error before performing the final switchover.
\n

\n\n

Recovery Options

\n

If replication stalls before the lag reaches zero, you can drop the subscription and retry after investigating network or configuration issues. After a successful cutover, if unexpected problems arise you can revert to the original cluster by redirecting traffic back to the source (which has not been altered) and re‑creating the subscription, or by restoring from a recent base backup and re‑initializing replication.

\n\n

Cautions & Limitations

\n

Logical replication does not automatically replicate DDL statements in PostgreSQL versions prior to 13; schema changes must be applied manually on both sides or you must run on a version that supports DDL replication.

\n

Large transactions or high write rates can cause replication slots to retain WAL files indefinitely, eventually exhausting disk space. Monitor pg_replication_slots and consider using temporary slots or adjusting max_slot_wal_keep_size to limit retention.

\n

The cutover itself requires a brief pause of writes; coordinate with your operations team to minimize user impact.