PostgreSQL Logical Replication Monitoring Checklist

This article provides a practical, end-to-end monitoring checklist for PostgreSQL logical replication, covering both publisher and subscriber sides. You will learn which metrics to watch, which queries to run, and what warning signs indicate trouble, so you can keep your replication healthy and avoid data loss or downtime.

Background: Why Logical Replication Needs Monitoring

Logical replication in PostgreSQL (introduced in version 10) works by streaming changes from the publisher’s write-ahead log (WAL) to a subscriber, which applies them via SQL. Unlike physical replication, it can replicate specific tables and even between different PostgreSQL major versions. However, because it decodes WAL and applies changes as regular transactions, it has its own failure modes: lag, conflicts, and silent stalls. Monitoring is essential because a broken replication pipeline often goes unnoticed until the subscriber is far behind or the publisher’s disk fills up.

How Logical Replication Works (Briefly)

On the publisher, a publication defines which tables are replicated. The WAL contains the changes, and a walsender process decodes them into a stream of changes. On the subscriber, a apply worker (or several, for parallel apply) receives the stream and applies it to the local tables. The subscriber also has a table sync worker that does the initial copy of existing data. Monitoring must cover both the WAL generation on the publisher and the apply progress on the subscriber.

Publisher-Side Monitoring Checklist

These checks on the publisher ensure that changes are being captured and sent without delay or resource exhaustion.

• WAL generation rate: Track pg_current_wal_lsn() over time to estimate how fast WAL is being produced. A sudden spike could indicate heavy writes, which might outpace the subscriber.
• Replication slots: Logical replication uses a replication slot to keep WAL until the subscriber confirms receipt. If the subscriber is down, WAL accumulates. Check pg_replication_slots for the slot’s restart_lsn and confirmed_flush_lsn. If restart_lsn is far behind the current WAL, disk usage will grow.
• WAL disk usage: Monitor the disk space where WAL files are stored (typically pg_wal). If it fills up, the publisher will stop accepting writes. Use pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) to see how much WAL is being retained for the slot.
• Walsender activity: Query pg_stat_replication for the walsender process. Check state (should be ‘streaming’), sent_lsn, write_lsn, flush_lsn, and replay_lsn (the last is for physical replication, but for logical, flush_lsn is what matters). If state is ‘catchup’ or ‘backup’, the subscriber is behind.
• Replication conflicts: On the publisher, check pg_stat_replication for conflict_count? Actually, that’s on the subscriber. On the publisher, look for errors in the logs like ‘replication slot … is active for PID …’.

Subscriber-Side Monitoring Checklist

These checks on the subscriber ensure that changes are being received and applied correctly.

• Apply lag: The most important metric. Compare the LSN that the subscriber has applied (pg_last_wal_replay_lsn() on the subscriber) with the LSN that the publisher has sent (sent_lsn in pg_stat_replication on the publisher). The difference in bytes gives you the lag. You can also see the lag in pg_stat_subscription as latest_end_lsn and last_msg_send_time.
• Subscription status: Query pg_stat_subscription on the subscriber. Check subname, pid (if NULL, the worker is not running), relid (NULL for the apply worker, specific for table sync), received_lsn, latest_end_lsn, and last_msg_receipt_time. If pid is NULL, the subscription is not active.
• Table sync progress: During initial copy, check pg_stat_progress_copy to see how many rows have been copied. If a table sync fails, it will retry; monitor the logs for errors.
• Apply worker errors: Check the subscriber logs for errors like ‘logical replication apply worker for subscription … will restart’. Frequent restarts indicate a persistent problem, often a conflict or a missing column.
• Conflict detection: Logical replication can fail if a unique constraint is violated on the subscriber. Monitor for errors like ‘duplicate key value violates unique constraint’. These conflicts can stop replication until resolved.

Automated Monitoring Queries

Here are a few concrete SQL queries you can run to get key metrics.

• Publisher: WAL retained for slot
  SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots;
• Publisher: Replication state
  SELECT application_name, state, sent_lsn, write_lsn, flush_lsn FROM pg_stat_replication;
• Subscriber: Subscription status
  SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_receipt_time FROM pg_stat_subscription;
• Subscriber: Apply lag (requires querying publisher)
  On the subscriber, run SELECT pg_last_wal_replay_lsn(); and compare with the publisher’s sent_lsn from pg_stat_replication. The difference is the lag in bytes.

Common Problems and How to Detect Them

• Subscriber down: If the subscriber is offline, the publisher keeps WAL for the slot. Detect by checking pg_replication_slots on the publisher for a growing restart_lsn lag. You should set an alert on disk usage.
• Apply lag growing: If the subscriber cannot keep up, received_lsn will lag behind the publisher’s sent_lsn. This can happen due to slow queries on the subscriber, missing indexes, or a large transaction. Detect by comparing LSNs.
• Replication conflicts: A conflict occurs when a change cannot be applied, e.g., a duplicate key. The apply worker will retry, but if it persists, it will stop. Detect by checking logs for ‘conflict’ or ‘duplicate key’.
• WAL disk full: If the subscriber is down for too long, the publisher’s WAL can fill up the disk. Detect by monitoring disk usage on the publisher’s pg_wal directory. This is a critical alert.
• Table sync stuck: During initial copy, if the table is large, the sync can take a long time. Detect by checking pg_stat_progress_copy on the subscriber. If it’s stuck, check for locks or network issues.

Best Practices and Alerting Thresholds

• Set up alerting on lag: Use a threshold like 10 MB or 1 minute of lag. The exact value depends on your workload, but any sustained lag should be investigated.
• Monitor replication slot health: Alert if restart_lsn is older than a certain time (e.g., 1 hour) or if the WAL retained exceeds a size (e.g., 1 GB).
• Check for worker restarts: Count the number of restarts of the apply worker. If it restarts more than a few times in a short period, investigate.
• Use a monitoring tool: Tools like Prometheus with the postgres_exporter, or pg_stat_monitor, can collect these metrics automatically. You can also write a simple script that runs the queries and sends alerts.
• Test failover: Regularly test what happens if the subscriber goes down. Ensure you can bring it back without manual intervention.

Frequently Asked Questions

How do I check if logical replication is working?

On the subscriber, run SELECT * FROM pg_stat_subscription; and check that pid is not NULL and received_lsn is advancing. On the publisher, check pg_stat_replication for a state of ‘streaming’.

What is the difference between sent_lsn and replay_lsn?

For logical replication, sent_lsn is the last LSN sent by the publisher, and flush_lsn is the last LSN flushed to disk on the subscriber. There is no replay_lsn for logical replication; use received_lsn in pg_stat_subscription to see what has been applied.

Why is my replication slot not advancing?

If the subscriber is disconnected, the slot will not advance. Check the subscriber’s connection and the pg_stat_replication view on the publisher. Also, if the subscriber is applying a long transaction, the slot may not advance until it commits.

Can I monitor logical replication without superuser access?

Yes, you can grant the pg_monitor role to a non-superuser to allow them to view pg_stat_replication, pg_replication_slots, and pg_stat_subscription. This is a good practice for security.

What happens if the subscriber is down for a long time?

The publisher will retain WAL for the slot, which can fill up the disk. The subscriber will catch up when it comes back, but if the WAL is removed (e.g., due to disk pressure), you may need to reinitialize the subscription.

How do I monitor lag in real time?

You can write a query that compares the publisher’s current WAL position with the subscriber’s received LSN, but since they are on different databases, you need to fetch both values and compute the difference. Many monitoring tools do this automatically.

Related

• Invoice Automation with AI Agents: Matching to Disputes
• Show Hide Libraries Folder: Windows & Mac Guide
• Movies Available on Disney Plus: Full Guide