PostgreSQL Logical Replication Upgrade: A Minimal-Downtime Guide

How logical replication enables cross-version upgrades

Physical streaming replication ships WAL records that describe changes to disk pages. The standby replays them byte for byte, so it must run the same major version and architecture as the primary. The on-disk format can change between major versions, which is why a physical replica cannot serve as the upgrade target.

Logical replication works one level higher. The publisher runs logical decoding over its WAL and turns page-level changes into row-level operations: this row was inserted, this one updated, this one deleted, along with transaction boundaries. The built-in pgoutput plugin streams those operations to subscribers, and each subscriber applies them as ordinary SQL-level changes against its own tables. The subscriber never reads the publisher's storage format, so the two clusters can run different major versions.

The moving parts

• Old cluster (publisher): holds a publication, which lists the tables to replicate. It also holds a replication slot, which records how far the subscriber has confirmed. The slot keeps WAL on disk until the subscriber has consumed it.
• New cluster (subscriber): holds a subscription. It copies each table's existing rows once, then applies the live change stream.
• Applications: connect through a connection pooler. At cutover you repoint the pooler instead of changing every client's configuration.

Why choose it over pg_upgrade

pg_upgrade is fast, but the cluster is offline for the duration of the upgrade. If something goes wrong after you start the new cluster, rolling back is hard. Logical replication moves the slow part, the initial data copy, to a time while production is still serving traffic. The outage shrinks to a short write freeze: you stop writes, let the subscriber catch up, and switch the pooler. The old cluster stays intact as a fallback until you decide to retire it.

Copy                   +------------------------------+
                   |         Applications         |
                   +--------------+---------------+
                                  |
                                  v
                   +------------------------------+
                   |      Connection pooler       |
                   +-----+------------------+-----+
                         |                  :
          before cutover |                  : after cutover
                         v                  v
+------------------------------+      +------------------------------+
| OLD CLUSTER (publisher)      |      | NEW CLUSTER (subscriber)     |
| major version N              |      | newer major version          |
|                              |      |                              |
| WAL --> logical decoding     |      | SUBSCRIPTION:                |
| PUBLICATION: tables to send  |      |   1. copy existing rows      |
| REPLICATION SLOT:            |      |   2. apply live row          |
|   confirmed position,        |      |      changes as SQL writes   |
|   retains unconsumed WAL     |      |   --> tables                 |
+--------------+---------------+      +--------------+---------------+
               |                                     ^
               +-------------------------------------+
         pgoutput stream: row-level INSERT / UPDATE / DELETE
         plus transaction boundaries, decoded from publisher WAL

The old cluster decodes its WAL into row-level changes that the newer-version subscriber copies and applies, while the pooler switches application traffic from the old cluster to the new one at cutover.

When to use it, when to avoid it, and the alternatives

This approach pays off when the database is too large to dump and restore inside a maintenance window, or when the business cannot accept more than a brief write pause. It also requires a team that can rehearse the migration end to end, because most failures show up during the rehearsal rather than during the cutover. Downtime is reduced, not eliminated. You still need a write freeze, a final catch-up, and a connection switch.

It is a poor fit in four cases:

• Small databases that can tolerate a short outage. pg_upgrade or a dump/restore finishes quickly and has far fewer moving parts.
• Schemas that change constantly. Logical replication does not carry DDL, so every migration must be applied to both clusters in the right order.
• Heavy use of large objects. They are not replicated, so you need a separate copy step.
• Tables without a primary key or usable replica identity. On these tables, UPDATE and DELETE fail on the publisher. Setting REPLICA IDENTITY FULL makes them work, but the subscriber then has to match rows by full comparison, which is slow.

Preparing the source and target clusters

Source cluster settings

Set wal_level = logical on the old cluster. This setting requires a restart, so schedule it early and separately from the cutover. Set max_replication_slots and max_wal_senders above current usage, leaving room for one slot per subscription plus the temporary slots used during the initial table sync. Create a dedicated role with the REPLICATION attribute and SELECT on every published table, because the initial copy runs ordinary queries.

In pg_hba.conf, allow that role from the new cluster's address. A logical replication connection matches on the database name, not the replication keyword, which only applies to physical replication.

Schema and DDL

Logical replication copies rows, not DDL. Dump the schema with pg_dump --schema-only, using the new version's binary, and apply it to the target. From then until cutover, freeze schema changes or apply each one to both sides, adding columns on the subscriber first. If the schemas drift, apply errors stop the subscription and WAL builds up on the publisher.

Replica identity

Without a replica identity, the publisher rejects UPDATE and DELETE on published tables. To find tables with no primary key, run SELECT c.oid::regclass FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','p') AND n.nspname NOT IN ('pg_catalog','information_schema') AND NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = c.oid AND contype = 'p');.

For each table it returns, prefer ALTER TABLE ... REPLICA IDENTITY USING INDEX on a unique, non-partial index over NOT NULL columns. Use REPLICA IDENTITY FULL only as a last resort. It logs the entire old row, which increases WAL volume. It can also force a scan of the subscriber table for every updated or deleted row.

Copy-- On the OLD cluster, as a superuser.
-- All three settings take effect only after a restart; do this well before cutover.
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;  -- current usage + subscriptions + sync slots
ALTER SYSTEM SET max_wal_senders = 10;

-- Dedicated role: REPLICATION to stream changes, SELECT for the initial table copy.
CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'change-me-placeholder';
GRANT USAGE ON SCHEMA public TO repl_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;

-- pg_hba.conf entry (logical replication matches the database name, not 'replication'):
--   host  appdb  repl_user  10.0.2.15/32  scram-sha-256

-- Tables that have no primary key and so need an explicit replica identity.
SELECT c.oid::regclass AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conrelid = c.oid AND contype = 'p'
  );

-- Preferred: a unique, non-partial, non-deferrable index on NOT NULL columns.
ALTER TABLE public.events ALTER COLUMN event_uuid SET NOT NULL;
CREATE UNIQUE INDEX events_event_uuid_key ON public.events (event_uuid);
ALTER TABLE public.events REPLICA IDENTITY USING INDEX events_event_uuid_key;

-- Last resort: logs the whole old row (more WAL), and the subscriber may
-- have to scan the table to find each updated or deleted row.
ALTER TABLE public.audit_log REPLICA IDENTITY FULL;

Source-cluster preparation for logical replication. It sets restart-only parameters, creates a replication role with SELECT grants, shows the pg_hba.conf entry, finds tables without a primary key, and assigns a replica identity using an index or, as a last resort, FULL.

Creating the publication and subscription

On the old cluster, create a publication. FOR ALL TABLES is the simplest choice for an upgrade. It also covers tables created later, but their schema still has to exist on the subscriber. An explicit table list gives you finer control, for example to migrate in batches or to leave out scratch tables you plan to drop. Creating a publication only records metadata, so it costs almost nothing.

On the new cluster, CREATE SUBSCRIPTION points at the publisher with a connection string and names the publication. By default this does three things in order:

1. It creates a replication slot on the publisher. The slot needs a consistent snapshot, so it waits for transactions already in progress to finish. A session left "idle in transaction" can therefore stall the command indefinitely. Check pg_stat_activity before you run it.
2. It copies each table. Table-sync workers each run a COPY of one table. Each worker holds a long-lived snapshot and reads the whole table, which adds I/O on the source and holds back vacuum cleanup on the publisher until that copy completes. Your largest table usually determines how long this phase takes.
3. It switches to streaming. As each table finishes, it catches up and joins ongoing change streaming.

Writes to the subscriber are not blocked during any of this. If an application or a careless script inserts a row there, a replicated insert with the same key will fail with a unique violation. The apply worker then stops and keeps retrying, while WAL builds up on the publisher. Replication will not resume until someone finds the error in the subscriber's log and removes or fixes the conflicting row by hand.

Copy-- On the OLD cluster (publisher): publish everything, or list tables explicitly.
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
-- CREATE PUBLICATION upgrade_pub FOR TABLE public.orders, public.customers;

-- Before subscribing, find sessions that would stall slot creation.
SELECT pid, usename, state, xact_start, left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- On the NEW cluster (subscriber): the schema must already exist here.
-- The password is read from ~/.pgpass so it never appears in the catalog.
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-db.internal port=5432 dbname=appdb user=replicator'
  PUBLICATION upgrade_pub;

-- Track the initial sync per table on the subscriber.
-- srsubstate: i = init, d = copying data, f = copy finished,
--             s = synchronized, r = ready (normal streaming)
SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';

-- Watch the apply worker. A unique violation shows up in the subscriber's
-- server log, and the worker keeps restarting until the row is fixed.
SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

-- On the publisher: how much WAL the slot is holding back.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

-- Resolving a conflict by hand on the subscriber: remove the stray row
-- (key taken from the error in the log) so the replicated insert can apply.
DELETE FROM public.orders WHERE id = 48213;

Creates the publication and subscription, checks for idle-in-transaction sessions that would block slot creation, monitors per-table sync state, apply progress and retained WAL, and removes a stray subscriber row that caused a unique-violation apply conflict.

Monitoring lag and protecting the publisher's disk

That is why lag monitoring belongs on both clusters for the entire life of the replication, not just during the initial copy.

On the subscriber

Query pg_subscription_rel to follow the initial sync. Each table moves through srsubstate values: i (initializing), d (copying data), s (synchronized) and r (ready, streaming normally). Cutover cannot happen until every row shows r. pg_stat_subscription shows received_lsn, latest_end_lsn and last_msg_receipt_time for each worker. If the receipt time stops advancing while the publisher is still taking writes, the apply worker has stalled.

On the publisher

pg_stat_replication reports sent_lsn, flush_lsn, replay_lsn and the write_lag, flush_lag and replay_lag intervals for each connected subscription. A stalled subscriber often disappears from this view altogether, so it cannot be your only signal. pg_replication_slots keeps listing the slot whether or not anything is connected. Measure how much WAL each slot is holding with pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn), and check the active column.

Why the slot is the real risk

A logical slot guarantees that the publisher keeps every WAL segment the subscriber has not yet confirmed. It has no built-in expiry. If the subscriber is down, stuck on an error, or abandoned after a failed test, WAL keeps accumulating on the production primary until its disk fills and the database stops accepting writes.

To guard against this:

• Alert on retained bytes per slot, with a threshold set well below the free space on the WAL volume.
• Alert separately on active = false lasting longer than a few minutes.
• Consider setting max_slot_wal_keep_size, which caps retention. The tradeoff is that a slot that exceeds the cap is invalidated, and the subscription then needs a full resync.
• After a failed attempt or after cutover, drop leftover slots with pg_drop_replication_slot.

Copy-- PUBLISHER: WAL retained per slot (alert on bytes AND on active = false)
SELECT slot_name, active, wal_status,  -- wal_status/safe_wal_size need PG 13+
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS unconfirmed,
       pg_size_pretty(safe_wal_size) AS headroom_before_invalidation  -- NULL if no cap
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- PUBLISHER: per-connection lag (a stalled subscriber may be missing here)
SELECT application_name, state, sent_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- SUBSCRIBER: tables not yet ready; cutover waits until this returns no rows
SELECT s.subname, sr.srrelid::regclass AS table_name, sr.srsubstate
FROM pg_subscription_rel sr
JOIN pg_subscription s ON s.oid = sr.srsubid
WHERE sr.srsubstate <> 'r';

-- SUBSCRIBER: worker progress; a stale receipt time means the worker stalled
SELECT subname, pid, received_lsn, latest_end_lsn,
       now() - last_msg_receipt_time AS since_last_msg
FROM pg_stat_subscription;

-- PUBLISHER cleanup: drop a slot abandoned after a failed test.
-- Confirm no subscription will use it again; it must be inactive to drop.
-- SELECT pg_drop_replication_slot('upgrade_test_sub');

Monitoring queries for both clusters: WAL retained by each logical slot on the publisher, replication lag, per-table sync state and apply-worker progress on the subscriber, plus the call that drops an abandoned slot.

Filling the gaps: sequences, large objects, and validation

Logical replication copies row changes for published tables and nothing else. It does not carry DDL, sequence state, large objects, materialized view contents, or roles. Each needs its own step before cutover.

Sequences

The target's sequences stay at their initial values while rows with high IDs arrive through replication. The first insert after cutover then draws an ID that already exists. Missed sequences are the usual cause of duplicate key errors right after cutover. Identity columns are backed by sequences and have the same problem.

To fix this, read every sequence's current value on the source from pg_sequences.last_value. Then call setval on the target with headroom, for example the source value plus a margin larger than the inserts you expect between reading and switching traffic. Gaps in IDs cost nothing, and collisions cause an outage. Do this after writes are frozen, or at least repeat it then.

Large objects and other non-table objects

Data stored through the large object API lives in pg_largeobject, which is not replicated. Copy it separately during the freeze, or convert those columns to bytea before the migration. Refresh materialized views on the target. Recreate roles and grants from a globals-only dump.

Validation

With writes paused and lag at zero, compare count(*) per table between source and target. Row counts can match even when values differ, so on critical tables also compare aggregates such as sum(amount) and max(id). For a stronger check, compute a hash over the rows in primary key order. Run the expensive comparisons on a few key tables first, because hashing every row of a large table takes time out of the cutover window.

Running the cutover and planning rollback

Cutover runbook

1. Block writes on the old primary. Pause the connection pooler or set default_transaction_read_only on the database and terminate existing sessions. A setting only affects new transactions, so open sessions keep writing until you end them.
2. Wait for lag to reach zero. Compare the publisher's current WAL position with the subscriber's confirmed position until they match.
3. Sync sequences. Copy each sequence value to the target with a safety margin, because logical replication does not carry sequence state.
4. Repoint applications or poolers to the new cluster.
5. Open writes on the new cluster.
6. Disable the subscription, detach its slot, drop the subscription, and drop the slot on the old cluster. An orphaned slot keeps the old primary from removing WAL.

The write freeze lasts as long as steps 1–5 take. Two things set its length: how much lag remains when you block writes, and how quickly your clients reconnect. Start the freeze only after lag is already near zero, and script the sequence sync.

Rollback

Before step 5, you can create a publication on the new cluster and a subscription on the old one with copy_data = false. The old cluster then keeps receiving writes made on the new one. Rolling back becomes a second cutover in the opposite direction, so you avoid restoring a backup and losing data.

Cost

Illustrative estimate. Assumed inputs: two engineers, 16 hours of runbook writing and staging rehearsal, a 3-minute freeze, compared with a 4-hour dump-and-restore outage. Logical replication costs about 32 engineer-hours. In return, it replaces hours of downtime with minutes and gives you a tested path back to the old cluster.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql logical replication upgrade, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.