Upgrading PostgreSQL Major Versions with Zero Downtime Using Logical Replication — From Slot Creation to Cutover Sequence and Rollback Criteria

Anyone who has used pg_upgrade knows the feeling. Major version upgrade day is always a nerve-wracking experience. You post a maintenance notice at 2 AM, halt the DB for anywhere from tens of minutes to several hours depending on data size, and then deal with post-upgrade VACUUM, statistics re-collection, and unexpected planner regressions. Honestly, the first few times I just accepted this as "standard procedure."

But with Logical Replication, the picture changes completely. You run both the old and new DB simultaneously, sync changes in real time, and then simply redirect traffic at the connection pooler level. Gadget.dev's case study reports completing a cutover in under 30 seconds using only pgBouncer PAUSE/RESUME. With a blue-green setup, you can keep the old DB as a fallback path, leaving plenty of room for rollback.

This post covers everything in one place: why logical replication works for major version upgrades, the order for configuring slots, Publications, and Subscriptions, the sequence problem you must handle right before cutover, and the criteria for deciding to roll back.

Why Logical Replication Is Used for Version Upgrades

Streaming replication (physical replication) copies binary blocks as-is. This requires the source and target PostgreSQL versions to be identical, and makes it impossible if there are architectural differences. Logical replication, on the other hand, decodes WAL into row-level DML messages. Because INSERT is delivered as INSERT and UPDATE as UPDATE, it can be applied even when the target is a different major version. Thanks to the pgoutput decoding plugin built into PostgreSQL 10 and later, no external extensions are needed.

This architecture is the key. The source DB continues receiving traffic and accumulates changes in the slot, while the target DB receives that stream and catches up. Once the gap between the two narrows sufficiently, you briefly pause a connection pooler like pgBouncer, redirect connections to the target, and resume — done.

Three Core Components

The slot matters for an important reason: even if the target goes down temporarily or falls behind, the WAL on the source won't disappear. Conversely, this characteristic is also the biggest risk — if the target falls far behind, the source disk can fill up with WAL, so on PostgreSQL 13 and later you must set max_slot_wal_keep_size. On PostgreSQL 10–12, this parameter doesn't exist, so you should either run a monitoring script that watches slot lag and manually drops the slot when it exceeds a threshold, or separately reserve enough disk space for partitions subject to archiving.

Pre-flight Checklist — Check These First

Start by verifying that the source DB is PG10 or later and has wal_level = logical.

-- On the source DB
SHOW wal_level;
-- Result should be: logical
 
-- If not logical, modify postgresql.conf and restart
-- wal_level = logical

Next, find tables without a primary key. Since a PK or REPLICA IDENTITY is required for UPDATE and DELETE replication, skipping this step causes replication to silently fail later. I once missed a single append-only table used for statistics, and only discovered it after cutover when DELETEs weren't showing up in the admin dashboard.

-- List of tables without a PK
SELECT t.tablename
FROM pg_tables t
WHERE t.schemaname = 'public'
  AND t.tablename NOT IN (
    SELECT tc.table_name
    FROM information_schema.table_constraints tc
    WHERE tc.constraint_type = 'PRIMARY KEY'
      AND tc.table_schema = 'public'
  );

For tables where adding a PK is difficult, you can work around it with REPLICA IDENTITY FULL. Note that this records all columns in WAL, increasing load.

ALTER TABLE legacy_log_table REPLICA IDENTITY FULL;

Other prerequisites:

• Apply the same DDL to the target DB in advance: Logical replication does not replicate DDL. CREATE TABLE, indexes, foreign keys, and extension installations must be created on the new version ahead of time.
• Handle pg_largeobject separately: Large Objects are outside the scope of logical replication. If you use them, a separate migration plan is required.
• Ensure sufficient disk space: Since both old and new DBs run simultaneously, you need roughly 2x the current data size.

Step-by-Step Setup — From Creating the Publication to Initial Sync

Step 1: Source DB — Create the Publication

-- On the source (old version) DB
-- Replicate all tables
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
 
-- To selectively replicate specific tables
CREATE PUBLICATION upgrade_pub FOR TABLE users, orders, products;

Grant appropriate privileges to the replication-dedicated account. GRANT ... ON ALL TABLES only applies to tables that currently exist, so if new tables might be created during the replication period, it's safer to also set ALTER DEFAULT PRIVILEGES.

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
 
-- Automatically grant SELECT on tables created afterward
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO replicator;

You also need to allow replication connections from the target DB host in pg_hba.conf.

# pg_hba.conf (source DB)
host    replication     replicator      <target_IP>/32        md5

Step 2: Target DB — Create the Subscription

Connect to the target DB and create a Subscription; the initial snapshot (full data copy) will start automatically.

-- On the target (new version) DB
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=source-db.internal port=5432 dbname=mydb user=replicator password=strong_password'
  PUBLICATION upgrade_pub;

How long the initial copy takes depends on data size. For very large databases in the multi-TB range, the snapshot phase alone can take hours to over a day. During this time, the source DB continues receiving traffic normally, and changes accumulate in the slot.

Step 3: Monitor Replication Progress

Once the initial copy finishes, it transitions to the CDC (Change Data Capture) phase. Continuously check replication lag.

-- On source DB: check slot lag
SELECT slot_name, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots;
 
-- On target DB: check subscription status
SELECT subname, pid, received_lsn, latest_end_lsn 
FROM pg_stat_subscription;

If lag_bytes is steadily decreasing, things are on track. Before cutover, confirm that this value has stabilized to a few hundred KB or less.

Cutover — The Sequence to Finish in Under 30 Seconds

This phase represents the actual "downtime" window. Using a connection pooler (pgBouncer), it appears to the service as just a brief pause. Following the wrong order can cause data inconsistencies, so it's recommended to follow the flow below exactly.

Sequence synchronization is an easy trap to miss. Logical replication does not replicate NEXTVAL calls, so the target's sequences lag behind the source. Attempting an INSERT immediately after cutover can collide with already-existing IDs.

I handle this by bundling the "query source → calculate safe buffer → setval on target" flow into a single script. The buffer value (10000 in the example below) is based on the expected number of INSERTs during the cutover window. For services handling hundreds of inserts per second, a generous buffer costs nothing.

#!/usr/bin/env bash
# Conceptual example — calculates source last_value + safety buffer per sequence and applies it to the target
SAFETY=10000
SEQS=$(psql -Atqc "SELECT schemaname||'.'||sequencename FROM pg_sequences WHERE schemaname='public'" -h source-db)
 
for seq in $SEQS; do
  LAST=$(psql -Atqc "SELECT last_value FROM $seq" -h source-db)
  NEW=$((LAST + SAFETY))
  psql -h target-db -c "SELECT setval('$seq', $NEW);"
done

When handling individual sequences manually, it's helpful to leave the flow explicit.

-- (Source DB) Query current sequence values
SELECT sequencename, last_value
FROM pg_sequences
WHERE schemaname = 'public';
-- Example: users_id_seq.last_value = 1000000, orders_id_seq.last_value = 5000000
 
-- (Target DB) Add a safety buffer (10000) to the above results and setval
SELECT setval('public.users_id_seq',  1000000 + 10000);   -- => 1010000
SELECT setval('public.orders_id_seq', 5000000 + 10000);   -- => 5010000

pgBouncer PAUSE/RESUME is executed from the admin console.

# Connect to the pgBouncer admin console
psql -p 6432 -U pgbouncer pgbouncer -c "PAUSE;"
 
# --- In between: confirm lag_bytes, sync sequences, update connection config ---
 
psql -p 6432 -U pgbouncer pgbouncer -c "RESUME;"

Rollback — When and How to Go Back

The rollback path is what makes the logical replication approach powerful. As long as the old DB remains alive, if something goes wrong on the new version, you can recover instantly by simply redirecting pgBouncer back to the old version.

Rollback trigger criteria to consider:

• A query's execution plan changed on the new version, causing significantly increased response times
• Functional errors due to extension version mismatch
• Inconsistencies found during data integrity verification

However, rollback has an important limitation: changes accumulated on the new version will be lost. Write transactions that occurred on the new version after cutover are not reverse-replicated back to the old version. Therefore, before deciding to roll back, you must determine "how much will be lost" and share that with the entire team. I habitually check these two things together:

-- (New version DB) Estimate number of transactions committed since cutover
-- Save a snapshot immediately after cutover, then compare with the value just before rollback
SELECT datname,
       xact_commit,
       xact_rollback
FROM pg_stat_database
WHERE datname = 'mydb';
 
-- (New version DB) Count of new rows per domain table
-- If created_at, updated_at columns exist, count directly from the cutover timestamp
SELECT count(*) AS inserted_after_cutover
FROM orders
WHERE created_at >= '<cutover_timestamp>';

If these numbers are large, you need to prepare not just a simple rollback, but also a correction procedure to extract the data accumulated on the new version as CSV and reload it into the old version. The sooner the rollback decision is made, the better — and that makes understanding the scope of data loss essential.

After cutover, keep the old version alive and monitor for at least 24–48 hours, or a full week for services with weekend traffic patterns. Only after confirming no issues should you clean up the slot and shut down the old version.

Pay attention to the order of slot cleanup. A subscription created with CREATE SUBSCRIPTION automatically creates a slot of the same name on the source by default, and DROP SUBSCRIPTION deletes that slot along with it. If you run pg_drop_replication_slot on the source first, the subsequent DROP SUBSCRIPTION may fail trying to find the already-deleted slot. Choose one of these two safe approaches:

-- Method A: Run DROP SUBSCRIPTION on the target only (slot is deleted automatically)
-- Target DB
DROP SUBSCRIPTION upgrade_sub;

-- Method B: Clean up the slot on the source first, then detach the target with slot_name=NONE
-- Target DB (while disconnected)
ALTER SUBSCRIPTION upgrade_sub DISABLE;
ALTER SUBSCRIPTION upgrade_sub SET (slot_name = NONE);
DROP SUBSCRIPTION upgrade_sub;
 
-- Source DB
SELECT pg_drop_replication_slot('upgrade_sub');

Always validate rollback scripts on staging first. Running them for the first time on production day is the worst-case scenario.

Pitfalls and Trade-offs Worth Knowing

Key Limitations at a Glance

The setting to prevent WAL accumulation goes in the source DB's postgresql.conf (PG13 and later):

# Limit maximum WAL size preserved by a slot (unit: MB)
max_slot_wal_keep_size = 10240   # Example: 10GB

Exceeding this value can invalidate the slot, so it's important to continuously monitor the target DB's replication lag. On PG12 and earlier, this safety mechanism doesn't exist, so you must set up alerts on the slot lag metric.

Improvements Since PG17

Starting with PostgreSQL 17 (released September 2024), a CLI tool called pg_createsubscriber was added. It converts a physical standby into a logical subscriber in one step, significantly reducing the multi-step manual work previously required.

Improvements frequently mentioned in relation to PostgreSQL 18 include extended multi-DB support for pg_createsubscriber, parallel apply performance improvements for subscriptions, and automatic cleanup of idle slots, all of which have been discussed and developed in the community. However, since the exact availability and defaults of each feature may vary by version, it is recommended to check the relevant release's official PostgreSQL release notes directly before adopting any of these (as of 2026).

When Third-Party Tools Help

If configuring via SQL directly is cumbersome, or you want to speed up the initial copy:

In AWS RDS environments, pglogical enables jumping from an older major version to a higher one. Azure Database for PostgreSQL also officially supports the same logical replication approach in its documentation.

Wrap-Up — The Steps Most Likely to Go Wrong in Practice

Honestly, the first time I tried an upgrade with logical replication, the places I got stuck most were the sequence problem and tables without PKs. Replication itself works fine, but when an ID collision error explodes immediately after cutover, your mind goes blank for a moment. After repeating this several times, I've found that incidents generally leak out somewhere in the following sequence. I'd recommend scanning it like a checklist.

• Things missed during preparation — Whether DDL, extensions, and indexes are 100% pre-applied to the target; whether REPLICA IDENTITY FULL is set on tables without a PK; whether ALTER DEFAULT PRIVILEGES is applied to the replication account.
• Monitoring metric thresholds — Whether lag_bytes stabilizes to the cutover threshold (e.g., a few hundred KB or less); whether source disk headroom can handle the worst-case slot accumulation scenario; whether separate slot lag alerts are configured for PG13 and earlier.
• Cutover order — pgBouncer PAUSE → re-confirm lag → sequence setval → redirect connections → RESUME. Turn this into a script and always run it on staging first.
• Rollback conditions and loss scope — Document rollback triggers (query performance degradation, integrity anomalies) explicitly, and prepare a procedure to snapshot the commit count and new row count per domain table on the new version just before rolling back.
• Cleanup order — Remember that DROP SUBSCRIPTION deletes the slot along with it, and be careful not to call pg_drop_replication_slot on the source first.

If you're on PG17 or later, pg_createsubscriber removes much of the manual setup burden. If you're building a new environment, I'd recommend reviewing the latest release notes and evaluating improvements like parallel apply and automatic slot management together.

References

• PostgreSQL Official Docs — Upgrading a PostgreSQL Cluster
• PostgreSQL Official Docs — Logical Replication Restrictions
• PostgreSQL Release Notes
• Crunchy Data — Online Upgrades in Postgres
• Cybertec — PostgreSQL Major Version Upgrade via Logical Replication
• Gadget.dev — Zero downtime Postgres upgrades using logical replication
• myDBA.dev — Zero-Downtime Postgres Major Upgrades with Logical Replication
• Percona — PITR, pg_upgrade, and Logical Replication Together
• pgcopydb Official Docs
• PostgreSQL Fastware — Seamless Subscriber Upgrades
• PostgreSQL Fastware — How to upgrade replication clusters without downtime
• pganalyze — Zero downtime upgrades and logically replicate very large tables
• Microsoft — Upgrade Azure Database for PostgreSQL with Minimal Downtime
• AWS — Using logical replication for Aurora PostgreSQL major version upgrade
• Nerd Level Tech — Postgres 18 Zero-Downtime Upgrade: pg_createsubscriber
• DBI Services — PostgreSQL 17-18 Blue-Green Migration