Measure PostgreSQL Streaming Replication Lag
Shows WAL lag in bytes and time on the primary, and replay delay on the standby. Used to monitor replication health and RPO risk.
-- 1. Primary Üzerinde (Run on Primary)
SELECT application_name, client_addr, state, sync_state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- 2. Standby Üzerinde (Run on Standby)
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 10).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
PostgreSQL Replication Lag Response
Separately measures WAL generation, transfer, flush and replay to identify network, disk or query causes of lag.
PostgreSQL WAL Directory Filling Up
Separates WAL growth due to slots, archiving, replicas and write volume and gives safe recovery steps without deleting WAL files manually.
Other POSTGRESQL Diagnostic Scripts
View All (100)Find Long-Running Queries and Idle-in-Transaction Sessions in PostgreSQL
Lists queries running longer than a minute, their wait events, and idle-in-transaction sessions that block vacuum. The first script to run during a performance incident.
PostgreSQL Lock Chain: Who Is Blocking Whom?
Uses pg_blocking_pids to pair blocked and blocking sessions with their queries and wait times. Shows the root cause of a lock chain in seconds.
PostgreSQL Dead Tuples, Bloat and Autovacuum Health Check
Shows dead tuple ratio and last vacuum/analyze times per table. Helps you find tables where autovacuum settings are not keeping up.
Find Unused Indexes in PostgreSQL
Lists never-scanned indexes that are neither unique nor primary keys, with their sizes. Surfaces cleanup candidates to cut write overhead and disk usage.