When a PostgreSQL replication slot shows `active = false` after a master restart, it typically indicates one of these scenarios:
## Common Causes & Solutions
### 1. **Replica Not Connected**
The most common reason – the replica hasn’t reconnected since the master restart.
**Check:**
“`sql
SELECT slot_name, active, active_pid, restart_lsn
FROM pg_replication_slots;
“`
**Solution:**
– Restart the replica’s replication process
– For physical replication: restart `walreceiver` on replica
– For logical replication: restart the subscription or logical replication worker
### 2. **Replication Slot Retention**
Slots remain inactive until a replica connects and starts consuming WAL.
**Verify with:**
“`sql
SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as lag
FROM pg_replication_slots;
“`
### 3. **PostgreSQL Configuration Issues**
**Check:**
– `max_replication_slots` in `postgresql.conf`:
“`sql
SHOW max_replication_slots;
“`
– Ensure `wal_level` is sufficient (replica or logical)
### 4. **Automatic Cleanup (PostgreSQL 13+)**
PostgreSQL 13+ may mark slots inactive if they’re not being used to prevent WAL accumulation.
### 5. **Replica Configuration**
On the replica, check:
“`bash
# For physical replication
pg_ctl restart -D /path/to/replica/data
# Or restart just the WAL receiver
“`
## Quick Diagnostic Steps
1. **Check slot status:**
“`sql
SELECT slot_name, active, active_pid, restart_lsn, confirmed_flush_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as bytes_behind
FROM pg_replication_slots;
“`
2. **Check replica connection:**
“`sql
SELECT pid, application_name, client_addr, state, sync_state
FROM pg_stat_replication;
“`
3. **Check WAL sender process:**
“`bash
ps aux | grep wal sender
“`
## Recovery Steps
### For Physical Replication:
“`bash
# On replica
pg_ctl restart -D /path/to/replica/data
# Or if using recovery.conf (PostgreSQL < 12) # Ensure primary_conninfo is correct ``` ### For Logical Replication: ```sql -- On publisher ALTER SUBSCRIPTION your_subscription DISABLE; ALTER SUBSCRIPTION your_subscription ENABLE; -- Or restart the worker
SELECT pg_replication_origin_advance(‘your_subscription’, ‘0/0’);
“`
### Force Reactivation:
If the slot is stuck, you might need to:
“`sql
— First, ensure replica is connected
— Then on master:
SELECT pg_drop_replication_slot(‘slot_name’); — WARNING: Drops slot!
— Recreate on replica
“`
## Prevention Tips
1. **Configure automatic reconnection** on replicas
2. **Monitor slot lag** to prevent WAL accumulation
3. **Use replication slot monitoring tools** like pg_monitor
4. **Set `wal_keep_size`** as backup for critical slots
5. **Implement health checks** for replication connections
**Important:** An inactive slot still prevents WAL cleanup until a replica reconnects and advances the confirmed LSN. Monitor `pg_wal` directory size if slots remain inactive for extended periods.
