DBAtlas · Database operations atlas

Monitor WAL retained by replication slots and safely drop an abandoned slot

PostgreSQLReplication

Command

SELECT slot_name, slot_type, database, active, active_pid, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       pg_size_pretty(safe_wal_size) AS safe_wal_size,
       xmin, catalog_xmin
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;
-- Safe drop: drops only if not active (returns 0 rows if active)
SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_name = '<slot>' AND NOT active;

What it does

Shows how much WAL each slot retains on the primary, wal_status (reserved/extended/unreserved/lost), the remaining safe margin under max_slot_wal_keep_size (safe_wal_size), and the xmin/catalog_xmin values that hold back VACUUM; the second query drops the slot only if it is not in use.

When to use it

When the pg_wal directory grows, on a disk alert and in routine monitoring; for slot cleanup after a standby or subscriber has been permanently removed.

Caution

An inactive slot retains WAL without limit and can fill the disk and stop the primary; a slot holding xmin/catalog_xmin also blocks dead tuple cleanup and creates bloat/wraparound risk. Dropping a slot makes it impossible for its standby/subscriber to resume where it left off (a rebuild is required); verify its owner before dropping. Run on the primary (pg_current_wal_lsn() fails on a standby).

Open in DBAtlas →

Related