Monitor logical replication (pg_stat_subscription, apply errors, slot lag)

Command

-- Subscriber:
SELECT subname, pid, leader_pid, relid::regclass AS syncing_table, received_lsn, latest_end_lsn, now() - last_msg_receipt_time AS since_last_msg, latest_end_time FROM pg_stat_subscription;
SELECT subname, apply_error_count, sync_error_count, stats_reset FROM pg_stat_subscription_stats;
-- Publisher:
SELECT slot_name, plugin, database, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS confirmed_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots WHERE slot_type = 'logical';

What it does

On the subscriber, shows apply/tablesync workers, received and confirmed LSN, time of last message and cumulative apply/sync error counts; on the publisher, shows the logical slot's unconfirmed (confirmed_flush) WAL and the total WAL it retains.

When to use it

During logical replication setup, to confirm lag approaches zero during an upgrade/migration cutover, and for error alerting.

Caution

If pid is NULL, the apply worker is not running (subscription disabled or repeatedly failing and restarting); check the server log. A rising apply_error_count indicates a conflict. A stalled subscription accumulates WAL and catalog_xmin on the publisher; monitor publisher disk and catalog bloat.