Zero-Downtime PostgreSQL Upgrade

You've planned the migration for months, but when the clock strikes 2 AM and you run pg_upgrade, your heart sinks: your web app starts returning 500s, connections pile up, and your users are staring at a spinning loader. Upgrading PostgreSQL doesn't have to mean a maintenance window that makes your team and your customers suffer. In this lesson, you'll learn how to upgrade PostgreSQL with minimal downtime — not by gambling with risky in-place upgrades, but by applying battle-tested strategies that keep your service available while the database catches up to a new major version.

The problem this lesson solves

Every major PostgreSQL release brings improvements: better query planner statistics, faster indexes, new data types, and security fixes. But upgrading is scary. The classic approach — stop the database, run pg_upgrade, restart — causes real downtime that can last from minutes to hours, depending on your data size and hardware. For a busy production system, even 30 seconds of outage can mean lost transactions, frustrated users, and a bad day for your on-call engineer.

The core pain is a version mismatch: PostgreSQL's on-disk data format is not guaranteed to be readable by a newer major version. A major upgrade (e.g., 14 to 15, or 15 to 16) requires a data migration, and doing that while the old database stays online is the essence of a minimal-downtime upgrade. This lesson gives you the mental model and practical steps to pull it off.

Core concept / mental model

Think of upgrading PostgreSQL like changing the engine of a car while it's still driving. You can't just yank out the old engine and bolt in the new one — the car would stop. Instead, you build a parallel engine alongside, test it, then gracefully switch the driver to the new one.

In database terms, the mental model is blue-green deployment applied to databases:

• Blue = your current, production PostgreSQL instance (e.g., version 14).
• Green = a new instance running the target version (e.g., version 16) on the same or different hardware.
• Replication = a continuous stream of changes from blue to green, keeping green as a live copy.
• Switchover = a tiny window (seconds, not minutes) where you stop writes on blue, let green catch up, and redirect traffic to green.

Key terms you'll encounter:

• pg_upgrade — PostgreSQL's built-in in-place upgrade tool; fast but requires downtime.
• Logical replication — streams changes (INSERTs, UPDATEs, DELETEs) using the write-ahead log (WAL), allowing cross-version replication.
• pg_dump / pg_restore — full data export/import; slow for large DBs, but version-agnostic.
• Rolling upgrade — upgrade replicas one at a time (used with streaming replication).

The critical insight: downtime is proportional to how long it takes to make the new version's data consistent with an instant in time. pg_upgrade locks the old cluster for the entire migration. Logical replication only locks for the final switchover seconds.

How it works step by step

There are two main strategies for a minimal-downtime upgrade. Here's the step-by-step for each.

Strategy A: Logical replication (most flexible)

1. Set up a new instance with the target version (e.g., install PostgreSQL 16). Configure it with wal_level = logical on the source.
2. Create a publication on the old database: CREATE PUBLICATION mypub FOR ALL TABLES;
3. Create a subscription on the new instance: CREATE SUBSCRIPTION mysub CONNECTION 'dbname=mydb host=oldhost' PUBLICATION mypub;
4. Pre-sync data: The initial copy happens automatically via pg_dump. For large DBs, you might pre-load a dump to speed up initial sync.
5. Monitor lag: Keep an eye on pg_stat_subscription to see when the new DB has caught up to the old one.
6. Switchover: Stop all writes to the old DB (or put app in read-only mode), wait for replication lag to reach zero, then: ALTER SUBSCRIPTION mysub DISABLE; and redirect connections to the new host.
7. Clean up: Remove the subscription and old cluster after verifying.

Strategy B: pg_upgrade with a swap (minimal downtime)

1. Backup everything (pg_dump or filesystem snapshot) — non-negotiable.
2. Stop the old cluster (short downtime starts here).
3. Run pg_upgrade with --link for fastest method (hard links data files).
4. Start the new cluster and run checks (ANALYZE to update stats).
5. Redirect app traffic to the new cluster.
6. Optional: Use a streaming replica of the new version to minimize the window further (promote the replica instead of the original).

The --link option makes pg_upgrade nearly instantaneous (just modifies files in place) — but it's risky if you don't have a backup. For a true minimal-downtime experience, logical replication is the gold standard.

Hands-on walkthrough

Let's do a practical exercise: upgrading from PostgreSQL 14 to 16 on a single server using logical replication. We'll use Docker containers to simulate two PostgreSQL instances.

Step 1: Run old PostgreSQL (14) with a test table

# Create a network so containers can talk
docker network create pgnet

# Run old PostgreSQL 14
docker run -d --name pgold --network pgnet -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:14

# Create a test database and populate
docker exec -i pgold psql -U postgres -c "CREATE DATABASE testdb;"
docker exec -i pgold psql -U postgres -d testdb \
  -c "CREATE TABLE users (id serial PRIMARY KEY, name text, created_at timestamptz default now());"

Step 2: Prepare old instance for logical replication

# Enable logical replication in postgresql.conf and restart
docker exec pgold psql -U postgres -c "ALTER SYSTEM SET wal_level = logical;"
docker exec pgold pg_ctlcluster 14 main restart  # or via docker restart

docker exec pgold psql -U postgres -d testdb -c "CREATE PUBLICATION mypub FOR TABLE users;"

Step 3: Start new PostgreSQL 16 and subscribe

# Run new PostgreSQL 16
docker run -d --name pgnew --network pgnet -e POSTGRES_PASSWORD=secret -p 5433:5432 postgres:16

# Create same database
docker exec pgnew psql -U postgres -c "CREATE DATABASE testdb;"

# Create subscription (initial sync happens automatically)
docker exec pgnew psql -U postgres -d testdb -c "
  CREATE SUBSCRIPTION mysub
  CONNECTION 'host=pgold port=5432 dbname=testdb user=postgres password=secret'
  PUBLICATION mypub;
"

Step 4: Test replication and perform switchover

# Insert on old; check it appears on new
docker exec pgold psql -U postgres -d testdb -c "INSERT INTO users (name) VALUES ('alice');"
docker exec pgnew psql -U postgres -d testdb -c "SELECT * FROM users;"
# Expected output: 1 row with alice

# Simulate switchover: stop writes, disable subscription, and point app to new
# In real life, you'd flip your app's database hostname here.
docker exec pgnew psql -U postgres -d testdb -c "ALTER SUBSCRIPTION mysub DISABLE;"

Now your app can connect to pgnew (port 5433) without any data loss — downtime was only the time it took to run that last ALTER SUBSCRIPTION command (milliseconds).

Expected output for the SELECT:

 id | name  |          created_at          
----+-------+-------------------------------
  1 | alice | 2025-04-06 18:23:45.123456+00
(1 row)

Compare options / when to choose what

When to choose what:

• If you can tolerate 10–30 minutes of downtime (e.g., internal tool, weekend), use plain pg_upgrade.
• If you need near-zero downtime and have complex app logic, invest in logical replication.
• If your DB is <100GB, pg_dump is simplest, but still requires downtime.
• If you have a streaming replica already, you can upgrade replicas one at a time and promote them — like logical replication but for the same version.

Variations:

• Use pg_upgrade --link with an immutable filesystem snapshot to make it effectively atomic.
• Use pglogical or Bucardo third-party tools when built-in logical replication has limitations (e.g., DDL changes).
• Use pg_rewind to quickly resync failed nodes after a switchover.

Troubleshooting & edge cases

"Subscription not synchronizing"

Symptom: pg_stat_subscription shows sync_state = 'init' forever.

Cause: The initial table copy is failing (e.g., conflicting constraints or data type mismatch).

Fix: Check the logs: docker logs pgnew. Ensure the table schemas match exactly (unique indexes are required for replication). Drop and recreate the subscription if needed.

"ERROR: relation \"public.users\" does not exist"

Symptom: When you create the subscription, the new DB doesn't have the table.

Cause: The initial sync didn't create the table (logical replication replicates data, not DDL).

Fix: Run pg_dump --schema-only to copy the schema before subscribing.

"Replication lag never reaches zero"

Symptom: pg_stat_subscription shows ongoing received_lsn lag.

Cause: Long-running transactions on the source block WAL cleanup, or the network is too slow.

Fix: Ensure max_wal_senders and max_replication_slots are high enough on the source. Consider pg_terminate_backend on idle-in-transaction connections.

"Sequence values are off after switchover"

Symptom: After upgrade, inserting rows causes duplicate primary key errors.

Cause: Sequences are not automatically synced by logical replication.

Fix: After switchover, run SELECT setval('seq_name', (SELECT max(id) FROM table)); for each sequence.

Pro tip: Always perform a full ANALYZE after upgrade to refresh planner statistics — old stats can cause catastrophic query plan changes.

What you learned & what's next

You've learned how to upgrade PostgreSQL with minimal downtime by:

• Understanding the blue-green deployment mental model for databases.
• Setting up logical replication from old to new version.
• Performing a switchover with only seconds of downtime.
• Comparing pg_upgrade, logical replication, and other methods.
• Troubleshooting common issues like replication lag and sequence gaps.

Next lesson in the PostgreSQL Tutorial track: you'll dive into managing high availability with Patroni — what to do after the upgrade to keep your cluster resilient. Alternatively, if you want to deepen your understanding of the internals, explore WAL and streaming replication in the prior lesson.

Go ahead and try the hands-on exercise: upgrade a test database using logical replication, then measure your actual downtime with time — you'll see it's often under a second. Your productions will thank you.