PostgreSQL logical replication: monitor drift, not just lag

A logical-replication dashboard can show a connected worker and a small LSN gap while the subscriber is already wrong. One update may have been skipped because its row was missing, a local write may have changed the same key, a sequence may still be at its old value, or a publisher migration may have introduced a column the subscriber cannot accept. Transport lag answers “how far behind is the apply stream?” It does not answer “are these databases equivalent enough for the job?”

PostgreSQL's current logical-replication documentation describes a publish/subscribe stream based on replication identity. It copies an initial snapshot, then applies publisher changes in order, with transactional consistency within one subscription. That is a strong transport and apply guarantee. It is not a promise that every database object, local write or operational mistake will remain aligned.

This is a current-documentation explainer verified against PostgreSQL 18 on 28 September 2026, not an announcement of a feature released today. The operating goal is to turn “replication is running” into a layered health model with four independent signals: transport, slot retention, apply behavior and content correctness.

Start with the contract, not the connection string

Define exactly what the subscriber is for. An analytics copy can tolerate seconds of delay and may intentionally contain fewer columns. A zero-downtime migration needs tighter schema compatibility, sequence alignment and a tested cutover. A downstream service database may use row filters and column lists, which means equality with the publisher is neither expected nor useful.

Record the contract per publication: tables, row filters, column lists, allowed DML operations, replication identity and whether the subscriber is read-only. The CREATE PUBLICATION reference notes that publications default to insert, update, delete and truncate, that update/delete tables need a replica identity, and that column lists do not change the behavior of `TRUNCATE`. Schema-level publications can also include future persistent tables and partitions. Treat each of those choices as a change-controlled API.

Overlapping publications in one subscription do not apply the same row twice, but multiple subscriptions can overlap and require care. PostgreSQL's subscription documentation explicitly warns about overlapping objects when several subscriptions connect the same pair. Maintain an inventory from publication to subscription to slot so ownership is visible during incidents.

Monitor four planes, not one lag number

The first plane is transport. On the subscriber, logical-replication monitoring exposes one row per subscription worker in `pg_stat_subscription`. An enabled subscription normally has an apply worker; table synchronization and parallel apply can add more. Alert when the expected apply row disappears, when `last_msg_receipt_time` ages beyond the workload's normal quiet period, or when `received_lsn` and `latest_end_lsn` stop moving while publisher writes continue.

Do not treat a quiet database as a broken one. Emit a small synthetic transaction on a dedicated replicated heartbeat table, then measure end-to-end visibility on the subscriber. This separates “no business writes occurred” from “the stream stopped.” The heartbeat should be ordinary application data in the publication, not a monitoring shortcut that bypasses the same path.

The second plane is retained WAL. The publisher's `pg_replication_slots` view exposes `active`, `inactive_since`, `restart_lsn`, `confirmed_flush_lsn`, `wal_status`, `safe_wal_size` and invalidation reasons. A disconnected consumer can retain WAL and exhaust storage. If `max_slot_wal_keep_size` is bounded, the slot can instead move through `unreserved` to `lost`. Alert on inactivity age, retained-byte growth, `wal_status` and the rate at which `safe_wal_size` is falling—not only on free disk space.

The third plane is apply behavior. PostgreSQL 18 records `apply_error_count`, `sync_error_count` and detailed conflict counters in `pg_stat_subscription_stats`. The statistics reference includes insert/update uniqueness conflicts, origin differences and missing rows. Graph counter deltas and keep the reset timestamp. A zero total after a restart or manual statistics reset is not evidence that the past was clean.

The fourth plane is content. Compare what the business depends on: counts by immutable time or tenant bucket, sums of stable amounts, minimum/maximum keys, and deterministic hashes over canonical columns. Run small buckets continuously and a broader sweep less often. Store the publisher and subscriber results with a comparison ID and a time boundary. Without a shared boundary, concurrent writes can create false mismatches.

Understand conflicts that do not stop replication

The conflict documentation distinguishes failures from tolerated divergence. A uniqueness violation raises an error and stops apply until resolved. But an update or delete whose target row is missing is counted as a conflict and skipped. Replication may continue, lag may fall to zero, and the data difference remains. That is the clearest example of why conflict counters and reconciliation belong beside lag.

Local writes on the subscriber are another risk. Logical apply behaves like normal DML: an incoming change can overwrite a row changed locally, and origin-difference detection requires `track_commit_timestamp`. Even when detected, origin-different updates and deletes are applied. If the subscriber is meant to be read-only, enforce that with roles and connection policy instead of relying on convention.

When apply stops, capture the subscription, relation, remote transaction LSN, server log and relevant rows before changing anything. Fix the root cause, then resume and reconcile the affected key range. ALTER SUBSCRIPTION supports `SKIP` by finish LSN, but it skips all data modifications in that remote transaction. It is an emergency recovery tool, not a generic “acknowledge error” button. Every skip needs a repair plan and a durable audit record.

Put DDL and sequences in a separate migration lane

PostgreSQL's restrictions are operationally important: DDL and schema definitions are not replicated, sequence state is not replicated, and large objects are not replicated. Tables are matched by qualified name and columns by name. If an incoming row no longer fits the subscriber schema, apply errors until the schema is compatible.

For additive changes, update the subscriber schema first, verify that old rows still apply, then deploy the publisher change and application code. For removals, stop producing the old shape first, wait for the apply stream and reconciliation to reach the chosen watermark, then remove the subscriber object before or with the publisher cleanup according to the tested compatibility plan. Avoid a single migration that assumes DDL travels with the data.

Sequence drift matters when the subscriber may accept writes after cutover. Replicated identity-column values do not advance the subscriber's underlying sequence. Before promotion, calculate safe sequence values from the data and publisher state, update them explicitly, and verify that a new write cannot collide. If the subscriber will always remain read-only, record that assumption and enforce it.

Publication changes are deployments too. Adding a table and refreshing a subscription can start an initial copy depending on options. Track table synchronization states and size the copy against network, I/O and retention budgets. Never refresh a large production publication blindly during peak traffic.

Make failover readiness measurable

A subscription that works against today's publisher may fail after the publisher's primary changes. PostgreSQL's logical-replication failover guide documents `failover = true` slots synchronized to a physical standby. Synchronization is asynchronous; configuration alone is not readiness.

Before a planned promotion, enumerate the subscription and completed table-sync slots required by all subscribers, then verify on the standby that every slot exists, is synchronized, is not temporary and has no invalidation reason. Also ensure the standby is ahead of the subscriber as the guide requires. Turn that query into a preflight check that must pass, rather than a wiki step someone remembers during an outage.

After promotion, verify connection routing, slot continuity, worker presence, heartbeat visibility and content buckets. Do not declare success when the application reconnects; declare it when the subscriber resumes from the expected position without re-copying or losing the comparison boundary.

Build a runbook around repairable states

Use severity by consequence. A growing LSN gap with intact slots is usually a capacity or blocking problem. An inactive slot with rapidly shrinking `safe_wal_size` is a retention incident. A uniqueness conflict is an apply outage. A rising missing-row counter is a correctness incident even if apply continues. A reconciliation mismatch is a data incident whose blast radius is the failed bucket, not automatically the whole database.

Test each state deliberately: revoke connectivity, pause the subscriber, fill WAL toward its configured limit, introduce a controlled missing row, make an incompatible schema change in a staging pair, refresh a publication with a new table, promote a publisher standby and rehearse a sequence-aligned cutover. Prove that alerts fire, operators can identify the affected keys, and the documented repair returns both counters and content checks to normal.

Logical replication is reliable when its boundaries are explicit. PostgreSQL preserves the ordered change stream; your operating design must preserve the schema contract, slot capacity, conflict visibility, content invariants and failover path. Lag is one signal. Correctness is a system of evidence.

Prepared by: Noor Yasser