Upgrade/Migration, CDC, Zero-Downtime Cutovers, and Logical Replication Runbooks

Learning outcomes

Logical replication is often selected for low-downtime migration because source and destination can run different PostgreSQL major versions and can remain online while most data is copied. That capability does not make the cutover automatic. The hard part is establishing one writer, proving catch-up, validating business state, reconciling non-replicated state, and deciding when rollback is still safe.

1. Define the migration contract before creating a subscription

Start with measurable acceptance criteria rather than commands. Define source and destination versions, published databases/tables, excluded objects, write ownership, allowable replication lag, maximum write freeze, validation queries, sequence/large-object handling, rollback point, and who owns slot cleanup.

2. Initial synchronization and catch-up phase

Provision destination schema first, create publication/subscription, and allow initial synchronization to finish while the source application remains on the publisher. Track each table's pg_subscription_rel state plus apply-worker progress and source slot retention.

SELECT s.subname,       r.srrelid::regclass AS relation,       r.srsubstate,       r.srsublsnFROM pg_subscription_rel AS rJOIN pg_subscription AS s ON s.oid = r.srsubidWHERE s.subname = 'ch15_servicehub_sub'ORDER BY relation;SELECT subname, worker_type, received_lsn, latest_end_lsn,       last_msg_receipt_time, latest_end_timeFROM pg_stat_subscriptionWHERE subname = 'ch15_servicehub_sub';

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,       wal_status, safe_wal_size, inactive_since,       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS bytes_ahead_of_consumerFROM pg_replication_slotsWHERE slot_name = 'ch15_servicehub_sub';

One byte-distance sample is not a service-level objective. Observe the trend under real write load. A destination that is always 20 MB behind but catching up may be healthier than one that is 2 MB behind and continuously diverging.

3. Validate continuously, not only at the end

Run non-destructive validation while replication is active. Simple row counts are useful but not sufficient. Add domain invariants, aggregate totals, nullability/constraint checks, representative keys, and optionally deterministic chunked hashes where data volume permits.

SELECT count(*) AS rows,       count(*) FILTER (WHERE status = 'completed') AS completed_rows,       sum(labor_minutes) AS total_labor_minutes,       min(work_order_id) AS min_id,       max(work_order_id) AS max_idFROM app.ch15_work_orders;SELECT region, status, count(*) AS rowsFROM app.ch15_work_ordersGROUP BY region, statusORDER BY region, status;

Differences can be expected while writes continue, so pair validation with a captured source progress point. Final equality is established only after write quiescence and complete subscriber catch-up.

4. Cutover: establish exactly one writer

The most dangerous “zero-downtime” mistake is running two independent writers against the same logical key space with no conflict-resolution design. Built-in logical replication is not an automatic multi-primary consensus system.

1. Announce/enter a controlled write-quiescence window.
2. Stop or queue source writes at the application/router layer.
3. Capture the final source WAL position and a business marker.
4. Wait until subscriber progress reaches the corresponding source change set.
5. Run final data and schema validation.
6. Reconcile sequences and all out-of-band state.
7. Switch application connection/routing to the destination.
8. Perform one controlled destination write and verify it.

BEGIN;INSERT INTO app.ch15_work_orders(work_order_id, customer_id, region, status, labor_minutes, changed_at)VALUES(15999, 5999, 'cutover', 'cutover_marker', 0, clock_timestamp());COMMIT;SELECT pg_current_wal_lsn() AS source_after_marker;

SELECT *FROM app.ch15_work_ordersWHERE work_order_id = 15999;SELECT subname, received_lsn, latest_end_lsn, latest_end_timeFROM pg_stat_subscriptionWHERE subname = 'ch15_servicehub_sub'  AND worker_type = 'apply';

5. Reconcile sequence and unsupported state before destination writes

Before routing writers, apply the Chapter 15 gap checklist: sequences/identities, large objects, roles and grants, extensions, generated-column compatibility, application jobs, certificates/secrets, server settings, and any schema changes performed outside the replication stream.

-- For every sequence-backed key, verify sequence state against replicated data.SELECT max(work_order_id) FROM app.ch15_work_orders;-- Then set the actual destination sequence only if this table uses one,-- using the verified sequence name and after source writes are frozen.

Do not blindly run setval(max(id)) for every sequence. Some sequences intentionally allocate outside current table maxima; others feed multiple tables. Reconciliation must understand the application's allocation contract.

6. Rollback is easy only before destination divergence

Before the destination accepts independent writes, rollback can often mean “route back to the source and resume.” After destination writes begin, the two sides can diverge. A rollback then requires a designed reverse replication path, captured change set, or another reconciliation strategy. Do not claim a reversible migration if the runbook has not defined this state transition.

7. Cleanup only after the rollback window closes

A finished migration can leave logical slots retaining WAL forever if nobody owns teardown. After the destination is accepted, the old source is no longer needed for rollback, and all audit artifacts are preserved, remove the subscription/slot through normal PostgreSQL commands.

ALTER SUBSCRIPTION ch15_servicehub_sub DISABLE;-- Confirm rollback window is closed and slot cleanup is approved.DROP SUBSCRIPTION ch15_servicehub_sub;

SELECT slot_name, slot_type, active, restart_lsn,       confirmed_flush_lsn, wal_status, inactive_sinceFROM pg_replication_slotsWHERE slot_type = 'logical';

If a network failure prevents subscription drop from removing a remote slot, follow documented slot-disassociation/drop procedures. Do not delete files from pg_wal or the replication-slot directory manually.

8. Built-in replication versus external CDC

Change Data Capture (CDC) is a broader integration category. External systems may use logical decoding plugins, Kafka-compatible platforms, connectors, schema registries, transformation layers, or non-PostgreSQL sinks. Those tools introduce their own delivery guarantees, checkpoint formats, schemas, ordering rules, and failure modes.

Built-in PostgreSQL logical replication is strongest when the destination is PostgreSQL and same-named target tables can receive transactional table changes. It is not a general event bus, schema-transform engine, long-term audit log, or automatic bidirectional conflict resolver.

9. PostgreSQL major-version upgrade notes

Logical replication supports migration between different PostgreSQL major versions, making it useful for low-downtime upgrades. Cross-version migration still requires extension/type/DDL compatibility testing. PostgreSQL 18 also documents special procedures for upgrading clusters that already participate in logical replication; logical-slot migration through pg_upgrade requires old logical-replication cluster members to be PostgreSQL 17 or later.

Do not conflate “using logical replication to migrate application data to a new major version” with “upgrading an existing logical-replication topology in place.” They are related but operationally different projects.

10. Final ServiceHub migration drill

Recreate the Chapter 15 publisher/subscriber pair, perform initial synchronization, create ordinary source writes while observing catch-up, run validation, enter a write freeze, write the cutover marker, wait for the destination, reconcile non-replicated state, route one test writer to the destination, and record measured timings. The drill is successful only if the data contract and single-writer invariant are proven—not merely because CREATE SUBSCRIPTION succeeded.

Authoritative references

Logical replication is version-, privilege-, topology-, and schema-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — Upgrading a PostgreSQL Cluster
• PostgreSQL 18 — Logical Replication Upgrade
• PostgreSQL 18 — Logical Replication
• PostgreSQL 18 — Logical Replication Restrictions
• PostgreSQL 18 — Replication Slots