0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Near-Zero-Downtime Major Version Upgrades

Use logical replication to upgrade across versions while keeping the application online.

Near-Zero-Downtime Major Version Upgrades is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why pg_upgrade Is Not Enough

A major PostgreSQL version jump (e.g. 14 to 17) changes the on-disk catalog format, so the old data directory can't be opened by the new binary directly.

  • pg_dump/pg_restore works but locks you out for the full dump+load window — hours on a large database.
  • pg_upgrade --link is fast but still requires the application to be fully stopped during the swap, and it operates in place.

Logical replication breaks this trade-off: it streams row-level changes from the old cluster into a freshly initialized new-version cluster while the application keeps writing. Downtime shrinks to a single, planned cutover of a few seconds.

The Core Idea: Two Clusters, One Stream

The upgrade runs as a publisher / subscriber pair across versions:

  • Publisher = your current production cluster (old version).
  • Subscriber = a new cluster running the target major version, initialized empty.

Logical replication is version-independent: it ships decoded logical changes (INSERT/UPDATE/DELETE), not physical WAL pages, so a v14 publisher can feed a v17 subscriber. Once the subscriber is fully caught up and lag is near zero, you flip the application's connection string to the new cluster.

Preparing the Publisher

Logical decoding must be enabled on the old cluster before you start. Set wal_level = logical (requires a restart — plan it during an earlier maintenance window) and give yourself enough replication headroom.

Verify the running configuration with a quick query so you don't discover the wrong setting mid-cutover.

-- postgresql.conf on the OLD (publisher) cluster
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- Confirm after restart:
SHOW wal_level;
SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders');

Creating the Publication

A publication defines which tables are streamed. For a full-database upgrade you publish all tables.

  • FOR ALL TABLES captures every existing and future table automatically.
  • Tables included in a publication must have a replica identity so UPDATE/DELETE can be replayed — a primary key is the default and best choice.

Tables that lack a primary key need REPLICA IDENTITY FULL or a unique index, otherwise UPDATEs/DELETEs on them will fail to replicate.

-- On the OLD cluster
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- Find tables with no usable replica identity BEFORE you start
SELECT n.nspname AS schema, c.relname AS table
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relreplident = 'd'          -- default (uses PK)
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  )
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

Seeding the New Cluster's Schema

Logical replication copies data, never DDL. You must create the schema on the new subscriber yourself before subscribing.

  • Dump the schema only with pg_dump --schema-only from the old cluster and restore it on the new one.
  • A key optimization: load the schema without indexes and foreign keys first, let the initial data copy run fast, then add indexes afterward. This dramatically speeds the bulk copy.
# Run from a shell with access to both clusters
pg_dump --schema-only --no-owner \
  -h old-host -U postgres appdb > schema.sql

# Restore schema into the NEW v17 cluster
psql -h new-host -U postgres -d appdb -f schema.sql

Creating the Subscription

On the new cluster, a subscription connects to the publisher, performs an initial data copy, then streams ongoing changes.

  • copy_data = true (the default) triggers the initial snapshot of existing rows.
  • The subscription automatically creates a replication slot on the publisher to track its position.

For large databases, raise max_sync_workers_per_subscription so multiple tables copy in parallel.

-- On the NEW (subscriber) cluster
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-host port=5432 dbname=appdb user=repl password=secret'
  PUBLICATION upgrade_pub
  WITH (copy_data = true, streaming = on);

Monitoring Initial Sync and Lag

Don't guess when it's safe to cut over — measure it. Two signals matter:

  • Per-table sync state: every table must reach the r (ready) state in pg_subscription_rel.
  • Replication lag: the byte distance between the publisher's current WAL position and what the subscriber has confirmed.

Watch lag from the publisher via pg_replication_slots / pg_stat_replication.

-- On the SUBSCRIBER: are all tables done copying?
SELECT srsubid, srrelid::regclass AS table, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';   -- empty result = all ready

-- On the PUBLISHER: how far behind is the subscriber?
SELECT slot_name,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

The Sequence Trap

This is the single most common way to corrupt data during a logical-replication upgrade: sequences are NOT replicated.

The schema dump recreates each sequence at its starting value, not its current value. If you cut over without fixing them, the new cluster will hand out IDs that already exist on the old one, causing duplicate-key violations and silent data collisions.

During cutover (after writes stop on the old cluster), advance every sequence on the new cluster past its old value. pg_dump --section=pre-data won't save you here — handle sequences explicitly.

-- Run on OLD cluster to generate setval statements,
-- then execute the output on the NEW cluster during cutover.
SELECT format(
  'SELECT setval(%L, %s, true);',
  schemaname || '.' || sequencename,
  COALESCE(last_value, 1)
)
FROM pg_sequences;

Rebuilding Indexes and Validating

If you deferred indexes/constraints to speed the copy (scene 5), now is the time to add them back — ideally while replication is still streaming, so they're ready before cutover.

  • Create indexes on the subscriber; ongoing changes keep flowing.
  • Re-add foreign keys and run ANALYZE so the new cluster's planner has fresh statistics — otherwise your first production queries hit bad plans.

Run a row-count and checksum comparison on a few critical tables to confirm fidelity before trusting the cutover.

-- On the NEW cluster, after the bulk copy is done
CREATE INDEX CONCURRENTLY idx_orders_customer
  ON orders (customer_id);

-- Refresh planner statistics for the new major version
ANALYZE;

-- Spot-check fidelity on a hot table
SELECT count(*), sum(hashtext(t::text)::bigint) AS checksum
FROM orders t;

The Cutover Sequence

Cutover is a tight, ordered checklist. The goal is to make sure no write is lost and no sequence collides.

  • Stop application writes to the old cluster (drain connections or set the old DB to default_transaction_read_only = on).
  • Wait until subscriber lag reaches zero — the new cluster has every committed change.
  • Run the setval statements to fix sequences.
  • Point the application's connection string at the new cluster and resume writes.

Done correctly, user-visible downtime is just the few seconds it takes to flip the connection string.

-- On the OLD cluster: stop new writes cleanly
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

-- On the PUBLISHER: confirm zero outstanding lag
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';   -- proceed only when 0

Tearing Down Cleanly

After the application is live on the new cluster and you've verified writes are landing, decommission the replication plumbing so it doesn't leak resources.

  • Drop the subscription on the new cluster — this also removes the remote replication slot when reachable.
  • Drop the publication on the old cluster.
  • If a slot lingers (e.g. publisher was unreachable), drop it manually — an orphaned logical slot pins WAL forever and will eventually fill the old disk.

Keep the old cluster read-only and intact for a rollback window before retiring it.

-- On the NEW cluster
DROP SUBSCRIPTION upgrade_sub;

-- On the OLD cluster
DROP PUBLICATION upgrade_pub;

-- If a slot was left behind on the OLD cluster:
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE slot_type = 'logical' AND active = false;

Quick Check

You're cutting over a v14 to v17 upgrade via logical replication. The bulk copy finished and lag is zero. What MUST you do during cutover that logical replication will not handle for you?

Recap: The Near-Zero-Downtime Playbook

Logical replication turns a multi-hour major upgrade into a seconds-long cutover. The disciplined sequence:

  • Prepare the old cluster: wal_level = logical, slots, and verify replica identity on every table.
  • Build a new-version cluster, restore schema-only (defer indexes/FKs for a fast copy).
  • Replicate: CREATE PUBLICATION on old, CREATE SUBSCRIPTION on new, monitor pg_subscription_rel and slot lag until ready.
  • Reconcile: rebuild indexes, re-add FKs, ANALYZE, and never forget to setval() the sequences.
  • Cut over: make old read-only, wait for zero lag, fix sequences, repoint the app.
  • Clean up: drop subscription/publication and any orphaned slots; keep the old cluster as a rollback safety net.

The sequence trap and an orphaned slot pinning WAL are the two mistakes that bite teams most — guard against both explicitly.

Frequently asked questions

Is the “Near-Zero-Downtime Major Version Upgrades” lesson free?

Yes — the full text of “Near-Zero-Downtime Major Version Upgrades” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Near-Zero-Downtime Major Version Upgrades”?

Use logical replication to upgrade across versions while keeping the application online. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Near-Zero-Downtime Major Version Upgrades” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Publications, Subscriptions, and Replica Identity
  2. Offloading Read and Analytic Workloads
  3. Near-Zero-Downtime Major Version Upgrades
  4. Monitoring Replication Lag and Slot Bloat
← Back to PostgreSQL Performance & Query Optimization