How to fix Replication slot is inactive in PostgreSQL

Connect to PostgreSQL and query the pg_replication_slots system view to identify which slots are inactive:

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

Slots where the active column shows f (false) are inactive. Check the slot_type: physical slots are used by streaming replication, while logical slots are used by logical replication subscribers and CDC tools.

Check how much WAL is being held by each slot:

SELECT slot_name,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

This shows how much WAL each slot is preventing from being removed. If an inactive slot shows gigabytes of retained WAL, it's likely the culprit causing disk space issues.

Before dropping a slot, verify whether the replica or subscriber will reconnect:

- For a standby replica: Check if the standby server is still needed. If yes, restart it or fix connectivity. If no, proceed to drop.
- For a logical subscriber: Verify if the consuming application (Debezium, ETL tool, etc.) will resume. If yes, restart the application.
- For permanent removal: If the replica was decommissioned, the slot must be dropped.

If you're unsure, leave the slot inactive for now and focus on preventing new ones from accumulating.

Only drop a slot if it's truly inactive and will not be used again. You cannot drop an active slot:

-- Drop a single inactive slot
SELECT pg_drop_replication_slot('slot_name');

Or drop multiple inactive slots at once:

-- Drop ALL inactive slots (use with caution!)
SELECT slot_name, pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE active = false;

After dropping inactive slots, PostgreSQL will immediately reclaim the WAL files, freeing up disk space.

WARNING: Only use the second query if you are certain all inactive slots are truly unused. Verify each slot individually if in doubt.

If the replica or subscriber is still needed, restore connectivity:

For streaming physical replication (standby server):
1. Verify network connectivity between primary and standby
2. Check standby recovery.conf or postgresql.conf for correct primary_conninfo settings
3. Restart the standby server:

sudo systemctl restart postgresql

4. Monitor the standby connection:

SELECT * FROM pg_stat_replication;

For logical replication (subscribers):
1. Restart the subscriber:

sudo systemctl restart [subscriber-service]

2. Or enable a disabled subscription:

ALTER SUBSCRIPTION subscription_name ENABLE;

3. Check subscriber status:

SELECT * FROM pg_stat_subscription;

For Debezium or external CDC tools:
1. Restart the Debezium connector or CDC process
2. Verify connection credentials are correct
3. Check application logs for connection errors

Prevent future disk space issues by configuring PostgreSQL to automatically limit or invalidate inactive slots:

-- Limit WAL retention to 10GB per slot (prevents unbounded growth)
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

This parameter ensures that if a slot falls too far behind, it will be invalidated automatically rather than consuming unlimited disk space.

For PostgreSQL 13+, you can also set an idle timeout to automatically invalidate slots after a certain time:

ALTER SYSTEM SET slot_wait_timeout = '24h';  -- Example: 24 hours
SELECT pg_reload_conf();

Set up monitoring alerts:

-- Alert if any slot retains more than 500MB of WAL
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 500 * 1024 * 1024;

Run this query periodically (every 5-15 minutes) and alert your operations team if it returns any rows.