Postgres replication slot lag query

Replication slot yang lupa di-drop saat decommission replica adalah pembunuh produksi yang silent. WAL terus terkumpul (Postgres simpan supaya bisa kirim ke slot itu), 24 jam kemudian disk primary penuh, semua write berhenti. Snippet ini query monitoring + cleanup pattern.

Kode

-- ==========================================
-- Query 1: status semua replication slot
-- ==========================================
SELECT
  slot_name,
  slot_type,           -- physical (replica) atau logical (CDC)
  database,            -- untuk logical slot
  active,              -- true = ada consumer, false = STALE
  active_pid,
  restart_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS wal_retained_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained_pretty,
  confirmed_flush_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- Output:
--  slot_name      | type    | active | wal_retained
--  replica_jkt    | physical| t      | 24 MB         ← healthy
--  replica_sby    | physical| t      | 1.2 GB        ← warning, replica lag
--  cdc_kafka      | logical | t      | 380 MB        ← healthy untuk CDC
--  old_replica_xx | physical| f      | 42 GB         ← STALE! drop ini

-- ==========================================
-- Query 2: ringkasan health + alert flag
-- ==========================================
SELECT
  slot_name,
  slot_type,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
  CASE
    WHEN NOT active THEN 'CRITICAL: slot inactive, drop atau aktifkan consumer'
    WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 10737418240
      THEN 'CRITICAL: > 10 GB WAL retained, replica too far behind'
    WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824
      THEN 'WARNING: > 1 GB WAL retained'
    ELSE 'OK'
  END AS status
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- ==========================================
-- Query 3: disk usage WAL dan estimated free
-- ==========================================
SELECT
  pg_size_pretty(pg_size_bytes(setting)) AS max_wal_size,
  pg_size_pretty(
    (SELECT SUM(pg_stat_file('pg_wal/' || name, true).size)::BIGINT
     FROM pg_ls_waldir())
  ) AS current_wal_size,
  (SELECT count(*) FROM pg_ls_waldir()) AS wal_file_count
FROM pg_settings WHERE name = 'max_wal_size';

-- ==========================================
-- Query 4: cross-reference slot dengan replica yang aktif
-- ==========================================
SELECT
  s.slot_name,
  s.slot_type,
  s.active,
  s.active_pid,
  r.application_name AS replica_connected,
  r.client_addr,
  r.state,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn)) AS retained
FROM pg_replication_slots s
LEFT JOIN pg_stat_replication r
  ON r.pid = s.active_pid
ORDER BY s.slot_name;

-- ==========================================
-- Query 5: logical slot — replication slot untuk CDC
-- ==========================================
SELECT
  slot_name,
  plugin,
  confirmed_flush_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_pretty
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- ==========================================
-- Action: cleanup stale slot
-- ==========================================
-- HATI-HATI: pastikan slot benar-benar tidak terpakai
-- Cek bahwa replica yang dimaksud sudah decommission

-- 1. Identify candidate stale slot
SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE active = false;

-- 2. Verify no replica connection from that IP
SELECT * FROM pg_stat_replication
WHERE application_name = '<slot_name>';
-- Empty result = aman drop

-- 3. Drop slot
SELECT pg_drop_replication_slot('old_replica_xx');

-- 4. Verify WAL akan di-cleanup di checkpoint berikut
SELECT pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), MIN(restart_lsn))
) AS new_max_retained
FROM pg_replication_slots;

-- ==========================================
-- Setup: create slot baru untuk replica
-- ==========================================
-- Physical slot untuk streaming replica
SELECT pg_create_physical_replication_slot('replica_yogya');

-- Logical slot untuk Debezium / CDC
SELECT pg_create_logical_replication_slot('cdc_pesanan', 'pgoutput');

-- Cek slot baru
SELECT slot_name, active FROM pg_replication_slots
WHERE slot_name IN ('replica_yogya', 'cdc_pesanan');

Pemakaian

#!/usr/bin/env bash
# /opt/monitoring/check_slot_lag.sh

set -euo pipefail

readonly DB_HOST="${DB_HOST:-pg-primary.tangerang.id}"
readonly THRESHOLD_GB=5

# Query slot dan parse
result=$(psql -h "${DB_HOST}" -U monitor_user -d postgres -tA -F'|' <<'SQL'
SELECT
  slot_name,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS bytes
FROM pg_replication_slots
ORDER BY bytes DESC;
SQL
)

threshold_bytes=$((THRESHOLD_GB * 1073741824))
alert=false

while IFS='|' read -r slot active bytes; do
  bytes=${bytes:-0}
  if [ "${bytes}" -gt "${threshold_bytes}" ] || [ "${active}" = "f" ]; then
    echo "ALERT: slot=${slot}, active=${active}, retained=${bytes} bytes"
    alert=true
  fi
done <<< "${result}"

if [ "${alert}" = "true" ]; then
  # Push alert ke Slack / PagerDuty
  exit 2
fi

echo "All slot healthy"

-- View persisted untuk Grafana datasource
CREATE OR REPLACE VIEW v_replication_health AS
SELECT
  slot_name,
  slot_type::TEXT,
  active::INT AS active_int,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS wal_retained_bytes,
  COALESCE(
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn),
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS lag_bytes,
  now() AS measured_at
FROM pg_replication_slots;

GRANT SELECT ON v_replication_health TO grafana_reader;

# Prometheus alert rule
groups:
  - name: postgres-slot
    rules:
      - alert: PostgresSlotInactive
        expr: pg_replication_slot_active == 0
        for: 5m
        labels: { severity: critical }
        annotations:
          summary: "Replication slot {{ $labels.slot_name }} inactive"
          description: "Slot inactive > 5m, WAL menumpuk. Verify atau drop."

      - alert: PostgresSlotWalRetainedHigh
        expr: pg_replication_slot_wal_retained_bytes > 10737418240  # 10 GB
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "Slot {{ $labels.slot_name }} retain > 10 GB WAL"

Kapan dipakai

• Production Postgres dengan streaming replication.
• CDC pipeline (Debezium → Kafka).
• Setelah decommission replica — verify slot di-drop.
• Investigasi disk primary yang growing tanpa data growth.
• Quarterly health check.

Catatan

• active = false → STALE kalau lebih dari beberapa jam. Replica yang sehat punya consumer terus terhubung.
• logical slot untuk CDC. Plugin biasanya pgoutput (built-in) atau wal2json. Logical slot bisa skip table tertentu via publication.
• restart_lsn lag = WAL yang masih dipegang slot. Setelah replica confirm flush, restart_lsn maju.
• drop slot fast kalau replica disconnect permanent. WAL accumulation linear dengan write rate primary.
• max_slot_wal_keep_size (PG 13+) — set limit max WAL yang slot bisa retain. Aman dari disk full, tradeoff: slot bisa “lost” kalau replica behind.
• logical decoding lambat untuk burst write — consumer harus consume dengan rate cocok. Kalau Kafka backpressure, slot lag naik.
• streaming vs file-based archive — slot hanya untuk streaming. WAL archive (archive_command) terpisah.

Tools seperti pgwatch2 atau pgmetrics monitor ini otomatis. Tapi tetap penting paham underlying query — saat tools sendiri yang down, pegang query ini bisa save the day.