Postgres replication lag monitoring script

Read replica yang lag jam-an = laporan dashboard salah, user lihat data lama. Tanpa monitoring, ketahuan saat customer komplain. Snippet ini script cek lag dari primary + replica, push metric ke Prometheus pushgateway, dan alert via Slack kalau threshold lewat.

Kode

#!/usr/bin/env bash
# /opt/monitoring/check_replication_lag.sh
# Cek replication lag setiap node replica, alert kalau lewat threshold

set -euo pipefail

readonly PRIMARY_HOST="${PRIMARY_HOST:-pg-primary.tangerang.id}"
readonly REPLICAS=(
  "pg-replica-1.tangerang.id"
  "pg-replica-2.tangerang.id"
  "pg-replica-3.tangerang.id"
)
readonly DB_NAME="${DB_NAME:-tokopedia_prod}"
readonly DB_USER="${DB_USER:-monitor_user}"
readonly THRESHOLD_SECONDS="${THRESHOLD_SECONDS:-30}"
readonly THRESHOLD_BYTES="${THRESHOLD_BYTES:-104857600}"  # 100 MB
readonly PUSHGATEWAY="${PUSHGATEWAY:-http://pushgateway:9091}"
readonly SLACK_WEBHOOK="${SLACK_WEBHOOK:-}"
readonly INSTANCE_LABEL="${INSTANCE_LABEL:-prod-jakarta}"

log() {
  echo "[$(date -Is)] $*" >&2
}

# Query primary untuk dapat WAL position + status replication
get_primary_status() {
  PGPASSWORD="${DB_PASSWORD}" psql -h "${PRIMARY_HOST}" -U "${DB_USER}" -d "${DB_NAME}" \
    -tA -F'|' <<'SQL'
SELECT
  application_name,
  client_addr::TEXT,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS lag_sent_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS lag_flush_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_replay_bytes,
  COALESCE(EXTRACT(EPOCH FROM (now() - reply_time)), 0)::INT AS lag_seconds
FROM pg_stat_replication
ORDER BY application_name;
SQL
}

# Query replica untuk dapat replay lag (sisi replica)
get_replica_lag_seconds() {
  local host=$1
  PGPASSWORD="${DB_PASSWORD}" psql -h "${host}" -U "${DB_USER}" -d "${DB_NAME}" \
    -tA <<'SQL'
SELECT
  CASE
    WHEN pg_is_in_recovery() = false THEN 0
    WHEN now() - pg_last_xact_replay_timestamp() IS NULL THEN 0
    ELSE EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT
  END AS replay_lag_seconds
SQL
}

push_metric() {
  local replica=$1
  local lag_bytes=$2
  local lag_seconds=$3
  local state=$4

  cat <<EOF | curl -s --data-binary @- \
    "${PUSHGATEWAY}/metrics/job/pg_replication/instance/${INSTANCE_LABEL}/replica/${replica}"
# TYPE pg_replication_lag_bytes gauge
pg_replication_lag_bytes ${lag_bytes}
# TYPE pg_replication_lag_seconds gauge
pg_replication_lag_seconds ${lag_seconds}
# TYPE pg_replication_state gauge
pg_replication_state{state="${state}"} 1
EOF
}

alert_slack() {
  local replica=$1
  local lag_bytes=$2
  local lag_seconds=$3
  local state=$4

  [ -z "${SLACK_WEBHOOK}" ] && return 0

  local color="danger"
  if (( lag_seconds < THRESHOLD_SECONDS * 2 )); then
    color="warning"
  fi

  local lag_mb=$((lag_bytes / 1024 / 1024))

  curl -s -X POST -H 'Content-Type: application/json' \
    -d "$(cat <<JSON
{
  "attachments": [{
    "color": "${color}",
    "title": ":warning: Postgres replication lag — ${replica}",
    "fields": [
      {"title": "Lag (seconds)", "value": "${lag_seconds}s", "short": true},
      {"title": "Lag (data)", "value": "${lag_mb} MB", "short": true},
      {"title": "State", "value": "${state}", "short": true},
      {"title": "Threshold", "value": "${THRESHOLD_SECONDS}s / 100MB", "short": true}
    ],
    "footer": "${INSTANCE_LABEL}",
    "ts": $(date +%s)
  }]
}
JSON
)" \
    "${SLACK_WEBHOOK}" > /dev/null
}

main() {
  local has_alert=0

  log "Cek primary status @ ${PRIMARY_HOST}"
  primary_status=$(get_primary_status)

  if [ -z "${primary_status}" ]; then
    log "WARN: tidak ada replica terkoneksi ke primary"
    exit 1
  fi

  while IFS='|' read -r app_name client_addr state lag_sent lag_flush lag_replay lag_seconds; do
    log "Replica ${app_name} (${client_addr}): state=${state}, replay_lag=${lag_replay}b, lag=${lag_seconds}s"

    push_metric "${app_name}" "${lag_replay:-0}" "${lag_seconds:-0}" "${state}"

    # Alert jika exceed threshold
    if [ "${lag_seconds:-0}" -gt "${THRESHOLD_SECONDS}" ] \
       || [ "${lag_replay:-0}" -gt "${THRESHOLD_BYTES}" ] \
       || [ "${state}" != "streaming" ]; then
      log "ALERT: ${app_name} lag=${lag_seconds}s, bytes=${lag_replay}, state=${state}"
      alert_slack "${app_name}" "${lag_replay:-0}" "${lag_seconds:-0}" "${state}"
      has_alert=1
    fi
  done <<< "${primary_status}"

  # Cross-check dari sisi replica
  for replica in "${REPLICAS[@]}"; do
    log "Cross-check replay lag @ ${replica}"
    replay_lag=$(get_replica_lag_seconds "${replica}" 2>/dev/null || echo "ERR")

    if [ "${replay_lag}" = "ERR" ]; then
      log "ERROR: tidak bisa connect ke ${replica}"
      alert_slack "${replica}" "0" "999" "DOWN"
      has_alert=1
      continue
    fi

    log "  ${replica}: replay_lag=${replay_lag}s"
  done

  if [ "${has_alert}" -eq 1 ]; then
    exit 2
  fi
  log "All replicas healthy"
}

main "$@"

# /etc/cron.d/pg_replication_check — run setiap 1 menit
* * * * * monitor /opt/monitoring/check_replication_lag.sh \
  >> /var/log/pg_replication.log 2>&1

Pemakaian

# Run manual untuk debugging
DB_PASSWORD=secret \
SLACK_WEBHOOK=https://hooks.slack.com/services/T.../B... \
THRESHOLD_SECONDS=10 \
/opt/monitoring/check_replication_lag.sh

# Output:
# [2026-07-13T10:00:00] Cek primary status @ pg-primary.tangerang.id
# [2026-07-13T10:00:00] Replica replica-1: state=streaming, replay_lag=24820b, lag=0s
# [2026-07-13T10:00:00] Replica replica-2: state=streaming, replay_lag=18420b, lag=1s
# [2026-07-13T10:00:01] Cross-check replay lag @ pg-replica-1.tangerang.id
# [2026-07-13T10:00:01]   pg-replica-1.tangerang.id: replay_lag=0s
# [2026-07-13T10:00:02] All replicas healthy

-- Query monitoring manual (tanpa script)
-- Dari PRIMARY
SELECT
  application_name AS replica,
  state,
  sync_state,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag,
  EXTRACT(EPOCH FROM (now() - reply_time))::INT AS lag_seconds
FROM pg_stat_replication;

-- replica  | state    | sync_state | replay_lag | lag_seconds
-- replica1 | streaming| async      | 1.2 MB     | 2
-- replica2 | streaming| sync       | 16 kB      | 0

-- Dari REPLICA
SELECT
  pg_is_in_recovery() AS is_replica,
  pg_last_wal_receive_lsn() AS received,
  pg_last_wal_replay_lsn() AS replayed,
  EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS replay_lag_sec;

# Grafana alert rule
groups:
  - name: postgres-replication
    rules:
      - alert: PostgresReplicationLagHigh
        expr: pg_replication_lag_seconds > 30
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replica {{ $labels.replica }} lag > 30s"
          description: "Replication lag {{ $value }}s, threshold 30s"

      - alert: PostgresReplicationDown
        expr: pg_replication_state{state!="streaming"} == 1
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Replica {{ $labels.replica }} not streaming"

Kapan dipakai

• Production Postgres dengan streaming replication.
• Multi-region replica untuk read scaling.
• Setup HA dengan auto-failover (Patroni, Stolon).
• Compliance audit yang butuh evidence replication healthy.

Catatan

• bytes vs seconds — pg_wal_lsn_diff paling akurat tapi lebih sulit di-interpret. reply_time enak untuk dashboard.
• state values: streaming (normal), catchup (lag besar setelah disconnect), backup (basebackup ongoing). Selain streaming = warning.
• sync_state: async / sync / quorum. Untuk financial, target sync replica.
• read-only replica harus pakai pg_last_xact_replay_timestamp() untuk lag — bisa NULL kalau tidak ada transaction di primary.
• alert frequency — jangan alert setiap menit. Pakai for: 5m di Prometheus rule untuk avoid noise dari blip.
• PG password jangan di-commit ke git. Pakai .pgpass file dengan permission 0600 atau env var dari secret manager.
• monitor_user role minimal — hanya pg_read_all_stats, jangan kasih superuser.

Lag yang konsisten naik tanpa drop = primary write rate > replica replay rate. Solution: tune WAL writer (wal_buffers, commit_delay) atau scale replica hardware.