How does logical replication enable a Postgres major version upgrade?

Physical streaming replication ships write-ahead log (WAL) records from a primary to a standby, which replays them byte for byte against its own data files. Because WAL describes changes to on-disk pages, the standby must run the same major version as the primary. The on-disk format can change between major releases, so a physical replica cannot be used to move to a new major version.

Logical replication works at a higher level. On the source, logical decoding reads the WAL and turns it into row-level changes: this row was inserted, this row was updated, this key was deleted. A publication on the source defines which tables to stream. A subscription on the target connects to the source, copies the existing rows in those tables, and then applies the ongoing changes as ordinary SQL operations. Because the target never touches the source's data files, it can run a newer major version.

Why this matters for upgrades

With pg_upgrade or dump/restore, the application is down for the whole conversion, and that time grows with the size of the data and the number of objects. With logical replication, the new cluster is built and caught up while the old one keeps serving traffic. Downtime shrinks to the cutover window. That window covers stopping writes, letting the last changes drain, syncing sequences, and repointing clients. It is often seconds to a few minutes.

Topology during migration

  • Source cluster (old version): remains the primary and takes all reads and writes. It holds the publication and a replication slot for the subscriber.
  • Target cluster (new version): has the schema already in place and holds the subscription. It is read-only from the application's point of view until cutover.
  • Application: points only at the source until you deliberately switch it over.
               +---------------------------------+
               |           Application           |
               +---------------------------------+
                 |                           :
    reads/writes |                           : repointed at cutover
                 v                           v
+-----------------------------+   +-----------------------------+
| SOURCE cluster (old major)  |   | TARGET cluster (new major)  |
| primary: all reads/writes   |   | schema created in advance   |
|                             |   | read-only until cutover     |
| WAL                         |   |                             |
|  |                          |   | SUBSCRIPTION                |
|  v                          |   |  1. copy existing rows      |
| logical decoding            |   |  2. apply ongoing changes   |
|  |                          |   |     as ordinary SQL         |
|  v                          |   |                             |
| PUBLICATION (chosen tables) |   |                             |
| replication slot -----------+-->| apply worker                |
+-----------------------------+   +-----------------------------+
   row-level changes, not page images, so the major versions may differ

During migration the old-version source keeps serving all traffic and streams decoded row changes from its publication and replication slot to a subscription on the new-version target, which takes over only when clients are repointed at cutover.

When should you use logical replication instead of pg_upgrade?

Logical replication is worth its overhead when the maintenance window is shorter than the time pg_upgrade or a dump/restore would need, or when you want the new cluster running and checked under real data before you commit to it. For small databases, or when an hour of planned downtime is acceptable, pg_upgrade is usually simpler. In link mode its downtime depends mostly on the size of the schema, not the volume of data, so even large clusters often finish quickly.

pg_upgrade Dump/restore Logical replication
Downtime Short in link mode; longer in copy mode Grows with data size Only the cutover itself
Operational complexity Low Low High
Second full-size cluster No (link mode) Usually Yes
DDL handling Upgraded in place Captured in the dump Not replicated; apply manually to both sides
Validation effort Low Moderate High

The development cost is easy to underestimate. Logical replication does not replicate DDL, so any schema change made during the sync window has to be applied to both clusters in the right order, or replication stalls on a mismatch. In practice this means a schema freeze. For a team that ships migrations several times a day, the freeze requires coordination across services, a review of pending migrations, and a clear owner who can grant exceptions. Account for that cost along with the infrastructure bill for running two clusters.

Avoid this approach if you have tables without a usable replica identity, depend heavily on large objects, or cannot staff a rehearsed cutover.

What do you need to configure on the source and target clusters?

Source cluster

Set wal_level = logical so WAL contains enough detail to decode row changes. Each subscription uses one replication slot and one WAL sender, plus temporary ones during the initial table sync, so raise max_replication_slots and max_wal_senders above your current usage. All three settings require a restart, so schedule it early. Create a dedicated role with the REPLICATION attribute and SELECT on the published tables, because the initial copy reads them with ordinary queries. Logical replication connects to a named database, so the pg_hba.conf entry must match that database name rather than the replication keyword. Also confirm the target can reach the source port through firewalls and security groups.

Replica identity

A published table without a replica identity rejects UPDATE and DELETE on the source. Find these tables by joining pg_class to pg_index and filtering for tables that have no indisprimary row. Fix each one by adding a primary key or by pointing REPLICA IDENTITY USING INDEX at a unique, non-partial index on NOT NULL columns. Use REPLICA IDENTITY FULL only as a last resort. It writes the entire old row to WAL, and the subscriber may have to scan the table to find the matching row for every change. On large tables this can leave apply far behind.

Target cluster

Restore the output of pg_dump --schema-only and the roles from pg_dumpall --globals-only. Keep primary keys and replica identity indexes, because apply uses them to locate rows. Build secondary indexes after the initial sync finishes, since maintaining them during the bulk copy slows it down.

Inventory

List what logical replication does not carry: sequence values, DDL, and large objects. Plan a separate copy step for each one.

-- Source: logical decoding settings (all three need a restart)
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 20;  -- current usage + subscriptions + sync workers
ALTER SYSTEM SET max_wal_senders = 20;

-- Dedicated role: REPLICATION to stream, SELECT for the initial copy
CREATE ROLE upgrade_repl WITH LOGIN REPLICATION PASSWORD 'change-me-placeholder';
GRANT USAGE ON SCHEMA public TO upgrade_repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO upgrade_repl;
-- pg_hba.conf names the database, not the 'replication' keyword:
--   host  appdb  upgrade_repl  10.0.2.0/24  scram-sha-256

-- Tables with no primary key and no explicit replica identity index or FULL
SELECT n.nspname AS schema, c.relname AS table_name, c.relreplident
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND c.relreplident IN ('d', 'n')  -- 'd' default (PK only), 'n' nothing
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  );

-- Preferred fix: unique, non-partial 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: whole old row goes to WAL; apply may scan per change
-- ALTER TABLE public.audit_log REPLICA IDENTITY FULL;

-- Inventory: large objects are not replicated and need their own copy step
SELECT count(*) AS large_objects FROM pg_largeobject_metadata;

Source-side preparation in SQL: enabling logical decoding settings, creating a replication role, finding tables that lack a usable replica identity, fixing one with REPLICA IDENTITY USING INDEX, and counting large objects that must be copied separately.

How do you set up the publication and subscription and monitor the initial sync?

On the source, create a publication covering the tables you inventoried. CREATE PUBLICATION upgrade_pub FOR ALL TABLES is the simplest option for a whole-database move. Use an explicit FOR TABLE list if you are excluding anything. On the target, CREATE SUBSCRIPTION upgrade_sub CONNECTION '...' PUBLICATION upgrade_pub connects back to the source and creates a replication slot there. It then starts copying data.

What the initial sync does

For each table, a sync worker runs a full COPY from the source and then catches up on changes made during the copy. After that, the main apply worker takes over that table. Each copy is a full sequential read of the table, inside a long-running transaction. That adds read I/O and network load on the source and holds back vacuum cleanup until the copy finishes. On a large or busy database, start the subscription off-peak. You can also lower max_sync_workers_per_subscription to limit how many tables are copied in parallel.

Monitoring

  • pg_subscription_rel (target): srsubstate shows each table's progress. A table moves from i and d (copying) through f and s to r (ready). Initial sync is done when every row reads r.
  • pg_stat_subscription (target): compare received_lsn and latest_end_lsn, and check that last_msg_receipt_time keeps advancing.
  • pg_replication_slots (source): check active. Compute retained WAL with pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn).

A slot holds WAL until the subscriber confirms it has applied that WAL. If the subscriber is down, falling behind, or forgotten, the source keeps accumulating WAL and can fill its disk. Alert on retained WAL, and drop slots you no longer need. If your version supports max_slot_wal_keep_size, consider setting it as a safety cap. The trade-off is that a slot which exceeds the cap is invalidated, and you would have to restart the subscription with a fresh initial sync.

-- SOURCE (old major version): publish every table
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- TARGET (new major version): schema must already exist here.
-- Creates a slot on the source and starts the initial COPY.
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-db.internal port=5432 dbname=app user=replicator password=CHANGE_ME'
  PUBLICATION upgrade_pub;

-- TARGET: per-table sync state; initial sync is done when no rows are returned
SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r'
ORDER BY 1;

-- TARGET: apply/sync workers; last_msg_receipt_time should keep advancing
SELECT subname, relid::regclass AS syncing_table, received_lsn,
       latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

-- SOURCE: slot health and WAL retained because of it.
-- Alert if active is false for long or retained_wal keeps growing.
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Creates the publication and subscription, then shows monitoring queries for per-table sync state and worker progress on the target, plus slot activity and retained WAL on the source.

How do you validate the replicated data before cutover?

Validation answers two questions: is the data on the target the same as the source, and can replication keep up with production traffic? Check both before every cutover rehearsal, not just the final one.

Compare the data

Start with exact row counts on every replicated table. Use count(*), not planner estimates from pg_class, because statistics on a freshly synced cluster are not comparable. Counts catch missing tables and failed syncs, but they miss rows with the same count and different contents. For critical tables, such as payments, accounts, and anything with financial or legal weight, compute a checksum over rows ordered by primary key, split into key ranges so each query stays bounded. On very large tables, compare a random sample of primary keys column by column. On a live system, rows keep changing during the comparison, so either compare ranges below a key or timestamp watermark that is no longer being written, or run the comparison during a quiet window.

Confirm lag stays near zero

Under normal load, the slot's confirmed_flush_lsn on the source should trail pg_current_wal_lsn() by a small and steady amount. If lag grows during peak hours, the target will not catch up quickly once you block writes, and your cutover window gets longer.

Keep the target read-only

Revoke write privileges from application roles on the target. A stray insert there can later collide with a replicated row and raise a unique violation. The apply worker stops on that error and retries in a loop. Meanwhile WAL accumulates on the source until someone removes the conflicting row by hand.

Automate the checks

Script the counts, checksums, and lag thresholds so the script reports a single pass or fail. You will run validation in every rehearsal, and a repeatable script makes results comparable between runs. It also removes the need for an engineer to spend an evening comparing query output.

#!/usr/bin/env bash
# Pre-cutover validation: prints PASS or FAIL and exits non-zero on any mismatch.
set -euo pipefail

SRC="host=pg-old.internal dbname=app user=validator"   # old major version
DST="host=pg-new.internal dbname=app user=validator"   # new major version
SLOT="upgrade_sub"                                      # logical slot on the source
MAX_LAG_BYTES=$((64 * 1024 * 1024))                     # alert threshold, tune per workload
WATERMARK=48000000   # compare only ids below this; newer rows may still be changing
STEP=1000000         # key-range size, keeps each checksum query bounded
fail=0

q() { psql "$1" -X -At -v ON_ERROR_STOP=1 -c "$2"; }

# 1. Count plus checksum per key range on critical tables.
#    Rows are ordered by primary key so both sides hash the same sequence.
#    Some types (for example floats) can print differently across major versions,
#    so cast those columns explicitly if a range keeps failing.
for t in public.accounts public.payments public.ledger_entries; do
  for ((lo = 0; lo < WATERMARK; lo += STEP)); do
    hi=$((lo + STEP))
    sql="SELECT count(*), md5(coalesce(string_agg(x::text, '|' ORDER BY x.id), ''))
         FROM $t x WHERE x.id >= $lo AND x.id < $hi"
    if [[ "$(q "$SRC" "$sql")" != "$(q "$DST" "$sql")" ]]; then
      echo "MISMATCH $t ids [$lo, $hi)"; fail=1
    fi
  done
done

# 2. Replication lag: bytes of WAL the subscriber has not yet confirmed.
lag=$(q "$SRC" "SELECT coalesce(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn), -1)::bigint
                FROM pg_replication_slots WHERE slot_name = '$SLOT'")
if [[ -z "$lag" || "$lag" -lt 0 || "$lag" -gt "$MAX_LAG_BYTES" ]]; then
  echo "LAG FAIL: ${lag:-slot missing} bytes"; fail=1
fi

# 3. Apply worker must be running; a conflict (e.g. unique violation) stops it.
workers=$(q "$DST" "SELECT count(*) FROM pg_stat_subscription WHERE pid IS NOT NULL")
[[ "$workers" -ge 1 ]] || { echo "APPLY WORKER NOT RUNNING"; fail=1; }

if [[ $fail -eq 0 ]]; then echo PASS; else echo FAIL; exit 1; fi

A rerunnable bash script compares row counts and range checksums on critical tables below a key watermark, checks slot lag against a byte threshold, and confirms the apply worker is running, then reports a single PASS or FAIL for each rehearsal.

Cutover runbook: blocking writes, syncing sequences, and switching traffic

Cutover itself is short and mechanical. The application sees a brief write pause, typically seconds to a few minutes, so this is near-zero downtime, not zero downtime. Rehearse the steps until the pause is predictable, and announce it as a maintenance window.

Step 1: Block writes on the source

Stop writes at the entry point you control. That means pausing the pooler or scaling write workers to zero. Setting default_transaction_read_only on the source helps as a backstop, but it only applies to new sessions and clients can override it. Treat it as a guard, not the primary mechanism. Any write that lands after this point and before the switch is a write you can lose.

Step 2: Wait for lag to reach zero

Record pg_current_wal_lsn() on the source. Then wait until the subscription's slot reports a confirmed_flush_lsn at or beyond it in pg_replication_slots. Zero lag means every committed change is applied on the target.

Step 3: Sync sequences

Logical replication has historically not carried sequence state. Without this step, the target's sequences sit at their initial values and the first insert collides with an existing key.

  1. Read last_value for each sequence from pg_sequences on the source.
  2. Run setval on the target with a safety margin, for example adding a few thousand.

Gaps in IDs are harmless. Duplicates are not.

Step 4: Switch traffic

Repoint connection strings or the pooler's backend to the target. Then disable the subscription so the target stops expecting changes from the old cluster.

Step 5: Enable writes on the target

Clear any read-only setting on the target. Resume the pooler, and watch error rates closely for the first few minutes.

How do you roll back and clean up after the upgrade?

Keep the old cluster as your fallback

Leave the old cluster running and untouched. Don't drop it, resize it, or reuse its hosts. Rolling back is then a pooler change: point traffic at the old primary and resume. The catch is that any writes accepted by the new cluster after cutover are missing on the old one.

To fall back without losing those writes, set up reverse replication during the write freeze, before Step 5. Create a publication on the new cluster and a subscription on the old one with copy_data = false, since both sides already hold identical data. Post-cutover writes then flow back to the old cluster. If you fail back, sync sequences in the reverse direction first, because logical replication does not carry sequence values. Avoid DDL during this window, since schema changes are not replicated and will break the reverse stream.

Clean up

Once you're confident in the new cluster, drop the forward subscription on the target (and the reverse one, if you created it). When the publisher is reachable, DROP SUBSCRIPTION also removes the replication slot on the old primary. This matters because an orphaned slot pins WAL indefinitely and can fill the old primary's disk. Confirm the slot is gone by querying pg_replication_slots on the old primary. If it remains, drop it manually with pg_drop_replication_slot(). Then drop the publications, take a final backup, and decommission the old cluster.

Commit the whole runbook to version control as a script, including the validation queries and sequence sync, and rehearse it against a staging copy. The next major upgrade then means rerunning and adjusting a tested procedure instead of rebuilding it from memory.

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 postgres major version upgrade logical replication, 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.
  • Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

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.
  • Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
  • 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.