PostgreSQL Replication Slots and Retained WAL
Shows inactive slots and the WAL they retain. Critical for preventing an abandoned slot from filling the disk.
SELECT slot_name, slot_type, active, restart_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;Inactive replication slots retain WAL files on disk indefinitely, risking complete filesystem saturation.
- 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.
PostgreSQL Disk Full Emergency
Covers safe capacity recovery for data, WAL and temp disks, warns against deleting database files and verifies recovery.
PostgreSQL Transaction ID Wraparound Emergency: Freeze Plan
Steps to save a database from going read-only when XID age reaches a critical level. Clears blockers and runs VACUUM FREEZE in the right order.
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.