Check PostgreSQL replication lag with a monitoring script

Replication lag is the amount of WAL the primary has produced that a standby has not applied yet. You can measure it in two ways: in bytes (the distance between two WAL positions, LSNs) and in time. This script does both and works on either side of the replication:

• On the primary it reads pg_stat_replication: one row per connected standby with its state, sync mode, replay position and replay_lag. It also alerts when fewer standbys are connected than expected.
• On a standby it reads pg_stat_wal_receiver, the gap between received and replayed WAL, and the time since the last replayed transaction.

The script

#!/usr/bin/env bash
set -Euo pipefail

WARN_BYTES=${WARN_BYTES:-16777216}
CRIT_BYTES=${CRIT_BYTES:-268435456}
WARN_SECONDS=${WARN_SECONDS:-30}
CRIT_SECONDS=${CRIT_SECONDS:-300}
EXPECTED_STANDBYS=${EXPECTED_STANDBYS:-1}

PSQL=(psql -X -At -F '|' -v ON_ERROR_STOP=1 -d "${PGDATABASE:-postgres}")
LABELS=(OK WARNING CRITICAL UNKNOWN)
worst=0
messages=()

raise() {
  if (( $1 > worst )); then worst=$1; fi
}

grade() {
  local bytes=$1 seconds=$2
  if (( bytes >= CRIT_BYTES || seconds >= CRIT_SECONDS )); then echo 2
  elif (( bytes >= WARN_BYTES || seconds >= WARN_SECONDS )); then echo 1
  else echo 0
  fi
}

unknown() {
  echo "UNKNOWN: $*"
  exit 3
}

in_recovery=$("${PSQL[@]}" -c 'SELECT pg_is_in_recovery()') || unknown "cannot query server"

if [[ $in_recovery == t ]]; then
  row=$("${PSQL[@]}" -c "
    SELECT coalesce(r.status, 'not-connected'),
           coalesce(extract(epoch FROM now() - pg_last_xact_replay_timestamp())::bigint, 0),
           coalesce(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()), 0)::bigint
    FROM (SELECT 1) AS one
    LEFT JOIN pg_stat_wal_receiver AS r ON true") || unknown "cannot query standby"
  IFS='|' read -r status delay backlog <<< "$row"
  raise "$(grade "$backlog" "$delay")"
  if [[ $status != streaming ]]; then raise 2; fi
  messages+=("standby: receiver=$status last_replay=${delay}s replay_backlog=${backlog}B")
else
  rows=$("${PSQL[@]}" -c "
    SELECT application_name,
           coalesce(host(client_addr), 'local'),
           state,
           sync_state,
           coalesce(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn), 0)::bigint,
           coalesce(extract(epoch FROM replay_lag), 0)::bigint
    FROM pg_stat_replication
    ORDER BY application_name") || unknown "cannot query primary"
  count=0
  while IFS='|' read -r name addr state sync bytes seconds; do
    [[ -z $addr ]] && continue
    count=$((count + 1))
    raise "$(grade "$bytes" "$seconds")"
    if [[ $state != streaming ]]; then raise 1; fi
    messages+=("$name ($addr): state=$state sync=$sync lag=${bytes}B/${seconds}s")
  done <<< "$rows"
  if (( count < EXPECTED_STANDBYS )); then
    raise 2
    messages+=("only $count of $EXPECTED_STANDBYS expected standby(s) connected")
  fi
fi

echo "${LABELS[$worst]}: replication"
if (( ${#messages[@]} > 0 )); then
  printf '  %s\n' "${messages[@]}"
fi
exit "$worst"

Run it

chmod +x pg-replication-lag.sh
sudo -u postgres ./pg-replication-lag.sh
EXPECTED_STANDBYS=2 WARN_SECONDS=10 CRIT_SECONDS=60 ./pg-replication-lag.sh
echo $?

Example output on a primary:

OK: replication
  db2 (10.0.0.12): state=streaming sync=async lag=0B/0s

The exit codes follow the Nagios plugin convention (0 OK, 1 WARNING, 2 CRITICAL, 3 UNKNOWN), so the script plugs into Nagios, Icinga, Zabbix external checks or a simple cron job that mails on non-zero exit.

Configuration

• WARN_BYTES / CRIT_BYTES: lag thresholds in bytes (defaults 16 MiB and 256 MiB).
• WARN_SECONDS / CRIT_SECONDS: time thresholds (defaults 30 and 300 seconds).
• EXPECTED_STANDBYS: how many standbys must be connected to the primary (default 1).
• Standard libpq variables such as PGHOST, PGPORT, PGUSER and PGDATABASE choose the server.

Run it as a role with pg_monitor rather than a superuser: CREATE ROLE monitoring LOGIN PASSWORD 'CHANGE_ME' IN ROLE pg_monitor;. Without such privileges some columns of the statistics views are hidden and the check may report wrong values.

Interpreting the numbers

• On the primary, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) is the number of bytes the standby still has to replay.
• replay_lag is the time between the primary flushing WAL and the standby reporting it applied. It becomes NULL after a short time on an idle, fully caught-up system; the script treats NULL as 0.
• On a standby, the time since pg_last_xact_replay_timestamp() grows whenever the primary has no write traffic, even if nothing is behind. On quiet systems raise the time thresholds or rely on the byte backlog, or generate a small periodic write on the primary.
• A standby state other than streaming (such as catchup) raises a warning; a standby whose WAL receiver is not streaming is critical.

Growing byte lag with a steady primary usually means the standby’s disk or CPU cannot keep up with replay, or the network between the two is saturated. Compare sent_lsn, write_lsn, flush_lsn and replay_lsn in pg_stat_replication to see at which stage it falls behind.

Official documentation

• Streaming replication monitoring
• pg_stat_replication and pg_stat_wal_receiver
• WAL and recovery functions