Monitor Replication Lag

PostgreSQL replication keeps your cluster resilient, but a standby that silently falls behind can corrupt read-heavy workloads, break failover SLAs, and make your HA setup a liability. In this lesson, you'll learn how to monitor replication lag and health using built-in views, practical queries, and proactive alerting — so you can spot trouble before your users do.

The problem this lesson solves

Replication is not fire-and-forget. A standby that lags behind the primary can serve stale data to readers, delay failover, and even block VACUUM on the primary. Without monitoring, you only discover lag when a report query returns old numbers or a failover loses recent transactions. This lesson gives you a repeatable playbook to answer three questions: Are my standbys connected? How far behind are they? Is the cluster healthy enough to fail over?

Core concept / mental model

Think of replication as a relay race. The primary (leader) runs the race and hands a baton — a stream of WAL (Write-Ahead Log) records — to each standby (follower). The baton position is the WAL location. The distance between the runner's current position and the last baton pickup is replication lag. Health is whether the standby is actively receiving (streaming) or stalled.

Key terms: - WAL (Write-Ahead Log): The journal of every change; standbys replay it to stay current. - WAL sender: A process on the primary that ships WAL to a standby. - WAL receiver: A process on the standby that receives and applies WAL. - Replication slot: A feature that prevents the primary from discarding WAL that a standby hasn't consumed yet — essential for streaming but needs monitoring to avoid disk bloat. - Lag metrics: Measured in bytes, seconds, or transaction counts — choose wisely.

How it works step by step

1. Primary records every change in WAL files, each with a unique location (e.g., 0/16B3748).
2. A WAL sender on the primary streams WAL to the standby's WAL receiver over a network connection.
3. The standby stores and replays those records, updating its data files.
4. Lag appears when the standby's replay point falls behind the primary's current WAL write position, due to network latency, I/O bottlenecks, or heavy write load.
5. Health degrades when the connection drops or the slot becomes inactive, making the standby useless and potentially bloating primary disk.

Hands-on walkthrough

Let's build a monitoring toolkit in /tmp/pg_monitor.sh step by step.

Check standby status and lag with pg_stat_replication

On the primary, this view shows each connected standby:

SELECT client_addr,
       state,
       sync_state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Expected output (values will differ):

 client_addr | state | sync_state | sent_lsn  | write_lsn | flush_lsn | replay_lsn | replay_lag_bytes
-------------+-------+------------+-----------+-----------+-----------+------------+-----------------
 192.168.1.4 | streaming | async   | 0/16B3800 | 0/16B3800 | 0/16B3798 | 0/16B3790 |            640

replay_lag_bytes is the byte distance between the primary's current WAL position and the standby's last replayed location. state=streaming and sync_state=async (or sync) tells you it's receiving and applying normally.

Convert byte lag to seconds

Bytes don't translate directly to time because write rates vary. A practical approach is to compute a rolling average of bytes per second and estimate seconds of lag, or use the pg_stat_wal_receiver view on the standby for its own perspective:

-- On the standby
SELECT status,
       received_lsn,
       last_msg_send_time,
       last_msg_receipt_time,
       EXTRACT(EPOCH FROM (last_msg_receipt_time - last_msg_send_time)) AS lag_seconds
FROM pg_stat_wal_receiver;

Wrap it in a shell script with alerting

Combine SQL and Bash to get a one-line health check:

#!/usr/bin/env bash
DB="postgres"
LAG_QUERY="SELECT COALESCE(MAX(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), 0) FROM pg_stat_replication;"
THRESHOLD=10485760  # 10 MB

lag_bytes=$(psql -d $DB -tAc "$LAG_QUERY")
if [ $? -ne 0 ]; then
  echo "ERROR: Could not query replication status"
  exit 2
elif [ "$lag_bytes" -gt "$THRESHOLD" ]; then
  echo "ALERT: Replication lag is $lag_bytes bytes (threshold $THRESHOLD)"
  exit 1
else
  echo "OK: Replication lag is $lag_bytes bytes"
  exit 0
fi

Run it with bash /tmp/pg_monitor.sh and you'll get OK or ALERT. For production, hook it into cron or your monitoring agent.

Compare options / when to choose what

Variation 1: Use check_postgres or telegraf to collect pg_stat_replication into Prometheus/Graphite.

Variation 2: Use rep_mode and replication_slot columns in pg_replication_slots to detect inactive slots and WAL accumulation.

Variation 3: In PostgreSQL 17+, use pg_stat_progress_replication to get accurate lag even when a standby is idle.

Troubleshooting & edge cases

• state=startup or catchup — The standby is still applying WAL after a restart. Give it time; if it never reaches streaming, check network or recovery settings.
• No rows in pg_stat_replication — No standby is connected. Verify hot_standby=on on the standby, primary_conninfo from pg_basebackup or pg_create_physical_replication_slot, and that the standby's WAL receiver is running (pg_stat_wal_receiver).
• Replication lag increasing steadily — Likely a slow disk on the standby, network bandwidth limits, or a long-running query blocking WAL replay. Check I/O and pg_stat_activity on the standby.
• Replication slot keeps growing on primary — The standby is not consuming WAL; check if the standby is down or the slot is stuck. pg_replication_slots shows active and restart_lsn. If the slot is inactive, consider dropping it, but only when you don't need it for recovery.
• False alarms from byte lag — When replication is healthy but idle, byte lag can appear high. Use time-based metrics (pg_stat_wal_receiver) or PG 17's progress view for accurate monitoring.

What you learned & what's next

You now know how to monitor replication lag and health using pg_stat_replication, pg_stat_wal_receiver, and shell scripting. You can identify lag in bytes and seconds, detect active streaming, and troubleshoot common edge cases like stuck slots and slow standbys. You also understand the trade-offs between built-in views and external monitoring tools.

Next lesson in the PostgreSQL Tutorial track (step 103) is: Design high-availability architecture — where you'll learn to plan and implement failover, quorum, and load balancing using Patroni or repmgr, building on the health checks you just practiced.

Practice recap

Temporary table to simulate a lagging standby is tricky, but you can still practice: run the primary-side query and observe your real standby if you have one. If not, read the pg_stat_replication docs to understand each column, then create a shell script that checks for empty result sets and alerts when lag exceeds 1 MB. Finally, test your script against a stopped standby to verify alerting works.