Engineering problem

PostgreSQL Logical Replication for Migrations and CDC

Use publications, subscriptions, and replication slots to copy selected PostgreSQL changes. Rehearse lag, schema changes, slot retention, and cutover.

Editorial update

Decision in brief

Use native logical replication for a selective PostgreSQL-to-PostgreSQL copy or a major-version cutover that requires a short write pause. Use a change data capture connector when a warehouse or stream needs transformed or independently replayable events. For primary failover, design for whole-cluster recovery and test it separately.

Copy selected tables: Publisher — Select tables. Subscriber — Copy rows. Then — Apply changes.
Rehearse initial copy, replication lag and cutover. Test primary failover separately.

Choose a replication method by target and failure window

A publication selects changes on the publisher. A subscription normally runs an initial table copy and then applies changes made since that snapshot. A primary key commonly supplies replica identity for UPDATE and DELETE. Validate replica identity for each published table. Prepare compatible tables and apply schema changes in a compatible order because native logical replication does not send DDL or sequence state.

The publisher needs wal_level set to logical plus sufficient replication slots and sender capacity. A lagging slot can retain write-ahead log (WAL) on disk even while disconnected. Watch the slot’s active state, restart_lsn, confirmed_flush_lsn and WAL status alongside publisher disk headroom and subscriber apply progress. The byte distance from the current WAL position to restart_lsn is a retention indicator. Measure end-to-end delay with a timestamped row. Set a WAL retention budget with a resynchronization plan because a configured cap can make a far-behind slot unusable.

GoalStarting choiceAcceptance evidence
Copy selected tables to another PostgreSQL versionNative publication and subscriptionSchema compatibility and validated row parity after a tested write pause
Send changes to a warehouse or event streamLogical decoding with an appropriate CDC connectorInitial snapshot and resumed stream agree after interruption, including deletes and duplicates
Fail over an application database as a wholePhysical streaming replication and failover toolingPromotion, application reconnect, backup restore and any CDC slot continuity meet their own limits

What HN reports suggest monitoring during replication

In a Snowflake CDC discussion, bastawhiz said a multi-terabyte PeerDB setup worked well until concurrent replication put compute or I/O pressure on either side. Limit initial-copy concurrency during your trial and measure its effect on the publisher’s existing workload.

In a separate Show HN, ish1907 posted Slotwatch, a tool focused on PostgreSQL replication slots. Use that visibility to answer an operational question: which owner gets paged when a disconnected consumer keeps retaining WAL, and when should that owner start a fresh synchronization?

Rehearse a table copy, a lag incident, and a cutover

Use disposable instances with sanitized representative tables. Write down the maximum publish-side I/O and lag, subscriber outage window, disk headroom and cutover duration before enabling replication.

  1. Create the target schema and a publication for two representative tables in staging. Give the subscription the intended replication role, run the initial snapshot and validate insert, update and delete cases, including replica identity.
  2. Replay write traffic and a compatible schema change. Track apply lag, source query latency and table-copy load. Pause the consumer briefly and confirm an alert before retained WAL approaches the approved limit. Resume and verify deterministic catch-up.
  3. For a migration, stop staging writes to the source, wait for apply to catch up, verify counts and selected checksums, set target sequences where needed and run application smoke tests against the target. Keep the source available. Plan how to reconcile new target writes before moving traffic back.
  4. Approve only when parity, cutover time and recovery budgets all pass. Stop before write cutover if schema drift, a stalled slot or conflicting writes make the results uncertain.

Related articles