To delete a PostgreSQL replication slot, you have several options depending on your PostgreSQL version and requirements:
## 1. **Using SQL Command (PostgreSQL 9.4+)**
“`sql
— Check existing replication slots
SELECT slot_name, slot_type, active FROM pg_replication_slots;
— Delete a specific replication slot
SELECT pg_drop_replication_slot(‘slot_name’);
“`
## 2. **Using pg_ctl (for physical replication slots)**
“`bash
# For physical replication slots
pg_ctl -D /path/to/data/directory promote
# Or if you need to force remove
pg_ctl -D /path/to/data/directory kill
“`
## 3. **Using pg_replication_slot_advance (PostgreSQL 10+)**
“`sql
— Advance the slot position before dropping (useful for logical slots)
SELECT pg_replication_slot_advance(‘slot_name’, ‘0/FFFFFFFF’);
SELECT pg_drop_replication_slot(‘slot_name’);
“`
## 4. **Important Considerations**
### **Before Deleting:**
“`sql
— Check if slot is active
SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
FROM pg_replication_slots
WHERE slot_name = ‘your_slot_name’;
— Check replication lag
SELECT
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag
FROM pg_replication_slots;
“`
### **Common Issues & Solutions:**
1. **Slot is active/in use:**
“`sql
— Check what’s using the slot
SELECT * FROM pg_stat_replication WHERE slot_name = ‘your_slot’;
— Stop the replication connection first
SELECT pg_terminate_backend(pid) FROM pg_stat_replication
WHERE slot_name = ‘your_slot’;
“`
2. **Logical replication slot with pending changes:**
“`sql
— Check confirmed_flush_lsn
SELECT slot_name, confirmed_flush_lsn FROM pg_replication_slots;
— May need to consume changes before dropping
“`
## 5. **Automated Cleanup Script**
“`bash
#!/bin/bash
# Script to safely drop inactive replication slots
DB_NAME=”your_database”
SLOT_NAME=”your_slot”
# Check if slot exists and is inactive
IS_ACTIVE=$(psql -d $DB_NAME -t -c \
“SELECT active FROM pg_replication_slots WHERE slot_name = ‘$SLOT_NAME'”)
if [ “$IS_ACTIVE” = “f” ]; then
echo “Dropping inactive slot: $SLOT_NAME”
psql -d $DB_NAME -c “SELECT pg_drop_replication_slot(‘$SLOT_NAME’);”
else
echo “Slot $SLOT_NAME is active. Cannot drop.”
fi
“`
## 6. **Using pgAdmin or Other GUI Tools**
– In pgAdmin: Navigate to Databases → Your DB → Replication Slots
– Right-click on the slot → Drop/Cascade
## **Warnings:**
– ⚠️ **Never drop an active replication slot** – this can break replication
– ⚠️ Dropping a physical replication slot can cause WAL accumulation
– ⚠️ For logical slots, ensure all changes are consumed first
– ⚠️ Monitor `pg_replication_slots` and WAL directory size after deletion
## **Best Practices:**
1. Always check if the slot is active before dropping
2. Monitor WAL growth after dropping physical slots
3. Keep track of which applications use which slots
4. Consider using `max_replication_slots` to limit slot creation
Would you like help with a specific replication slot issue or need guidance on monitoring replication slots?
