stat-wal-receiver

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'pg_stat_wal_receiver' 
AND column_name = 'flushed_lsn'

SELECT
  trim(both '''' from substring(conninfo from 'host=([^ ]*)')) as upstream_host,
  slot_name,
  status,
  (receive_start_lsn - '0/0') % (2^52)::bigint as receive_start_lsn,
  (flushed_lsn - '0/0') % (2^52)::bigint as flushed_lsn,
  receive_start_tli,
  received_tli,
  extract(epoch from last_msg_send_time) as last_msg_send_time,
  extract(epoch from last_msg_receipt_time) as last_msg_receipt_time,
  (latest_end_lsn - '0/0') % (2^52)::bigint as latest_end_lsn,
  extract(epoch from latest_end_time) as latest_end_time,
  substring(slot_name from 'repmgr_slot_([0-9]*)') as upstream_node
FROM pg_catalog.pg_stat_wal_receiver

-- Same query but without flushed_lsn column

pg_stat_wal_receiver_receive_start_lsn{upstream_host="primary.example.com",slot_name="replica_1",status="streaming"} 123456789
pg_stat_wal_receiver_flushed_lsn{upstream_host="primary.example.com",slot_name="replica_1",status="streaming"} 123456750
pg_stat_wal_receiver_receive_start_tli{upstream_host="primary.example.com",slot_name="replica_1",status="streaming"} 1
pg_stat_wal_receiver_received_tli{upstream_host="primary.example.com",slot_name="replica_1",status="streaming"} 1
pg_stat_wal_receiver_last_msg_send_time{upstream_host="primary.example.com",slot_name="replica_1",status="streaming"} 1638360125
pg_stat_wal_receiver_last_msg_receipt_time{upstream_host="primary.example.com",slot_name="replica_1",status="streaming"} 1638360125

# WAL receive lag (LSN difference)
pg_stat_wal_receiver_receive_start_lsn - pg_stat_wal_receiver_flushed_lsn

# Time since last message received
time() - pg_stat_wal_receiver_last_msg_receipt_time

# Receiver not streaming
pg_stat_wal_receiver_receive_start_lsn{status!="streaming"}

# No recent messages
time() - pg_stat_wal_receiver_last_msg_receipt_time > 60

# Message delivery time
pg_stat_wal_receiver_last_msg_receipt_time - pg_stat_wal_receiver_last_msg_send_time

- alert: WALReceiverDown
  expr: pg_stat_wal_receiver_receive_start_lsn{status!="streaming"} > 0
  annotations:
    summary: "WAL receiver not streaming"
    description: "Replica {{ $labels.instance }} WAL receiver status: {{ $labels.status }}"

- alert: WALReceiverStale
  expr: time() - pg_stat_wal_receiver_last_msg_receipt_time > 60
  annotations:
    summary: "No WAL received in 60 seconds"
    description: "Replica {{ $labels.instance }} hasn't received WAL for {{ $value }}s"

- alert: HighReplicationLatency
  expr: |
    pg_stat_wal_receiver_last_msg_receipt_time - 
    pg_stat_wal_receiver_last_msg_send_time > 5
  annotations:
    summary: "High network latency in replication"
    description: "Network latency: {{ $value }}s"

SELECT 
  status,
  conninfo,
  slot_name,
  sender_host,
  sender_port,
  received_lsn,
  received_tli,
  last_msg_send_time,
  last_msg_receipt_time,
  latest_end_lsn,
  latest_end_time
FROM pg_stat_wal_receiver;