Here’s a comprehensive guide on `pg_drop_replication_slot` with examples:
## What is `pg_drop_replication_slot`?
`pg_drop_replication_slot` is a PostgreSQL system function that removes a replication slot. Replication slots are used in logical and physical replication to track replication progress and prevent WAL removal.
## Basic Syntax
“`sql
SELECT pg_drop_replication_slot(‘slot_name’);
“`
## Examples
### 1. **Drop a Physical Replication Slot**
“`sql
— Check existing slots first
SELECT slot_name, slot_type, active, wal_status
FROM pg_replication_slots;
— Drop the slot
SELECT pg_drop_replication_slot(‘physical_slot_1’);
“`
### 2. **Drop a Logical Replication Slot**
“`sql
— Drop a logical replication slot
SELECT pg_drop_replication_slot(‘logical_slot_1’);
“`
### 3. **Drop with Error Handling**
“`sql
— Drop only if exists (PostgreSQL 13+)
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = ‘my_slot’) THEN
PERFORM pg_drop_replication_slot(‘my_slot’);
RAISE NOTICE ‘Slot dropped successfully’;
ELSE
RAISE NOTICE ‘Slot does not exist’;
END IF;
END $$;
“`
### 4. **Complete Example with Setup and Cleanup**
“`sql
— Create a logical replication slot first
SELECT pg_create_logical_replication_slot(
‘test_slot’,
‘pgoutput’
);
— Verify it exists
SELECT slot_name, plugin, slot_type
FROM pg_replication_slots
WHERE slot_name = ‘test_slot’;
— Drop the slot
SELECT pg_drop_replication_slot(‘test_slot’);
— Verify it’s gone
SELECT COUNT(*)
FROM pg_replication_slots
WHERE slot_name = ‘test_slot’;
“`
### 5. **Using in a Script with Variables**
“`sql
— Using a variable for slot name
\set slot_name ‘my_replication_slot’
— Drop the slot
SELECT pg_drop_replication_slot(:’slot_name’);
“`
## Important Considerations
### **Prerequisites**
– You must be a superuser or have the `REPLICATION` privilege
– The slot must not be active (no active connections using it)
### **Check if Slot is Active Before Dropping**
“`sql
— Check if slot is active
SELECT slot_name, active, pid
FROM pg_replication_slots
WHERE slot_name = ‘slot_to_drop’;
— If active, you might need to stop the replication first
SELECT pg_terminate_backend(pid)
FROM pg_replication_slots
WHERE slot_name = ‘slot_to_drop’ AND active;
— Then drop the slot
SELECT pg_drop_replication_slot(‘slot_to_drop’);
“`
### **Common Errors and Solutions**
“`sql
— ERROR: replication slot “slot_name” is active
— Solution: Stop the replication consumer first
— ERROR: replication slot “slot_name” does not exist
— Solution: Check spelling or if slot was already dropped
— ERROR: permission denied to drop replication slot
— Solution: Connect as superuser or user with REPLICATION privilege
“`
## Practical Use Cases
### **1. Cleanup After Failed Replication**
“`sql
— After a failed logical replication setup
SELECT pg_drop_replication_slot(‘failed_replication_slot’);
“`
### **2. Migration/Upgrade Script**
“`sql
— Drop old slots before migration
SELECT pg_drop_replication_slot(‘old_slot_name’);
“`
### **3. Automated Cleanup Script**
“`sql
— Drop all inactive slots
DO $$
DECLARE
slot_record RECORD;
BEGIN
FOR slot_record IN
SELECT slot_name
FROM pg_replication_slots
WHERE NOT active
LOOP
EXECUTE ‘SELECT pg_drop_replication_slot(‘ || quote_literal(slot_record.slot_name) || ‘)’;
RAISE NOTICE ‘Dropped slot: %’, slot_record.slot_name;
END LOOP;
END $$;
“`
## Best Practices
1. **Always verify before dropping:**
“`sql
SELECT * FROM pg_replication_slots WHERE slot_name = ‘your_slot’;
“`
2. **Monitor WAL growth:** Dropping a slot allows PostgreSQL to remove old WAL files
3. **Use in maintenance windows:** Dropping active slots will terminate replication
4. **Keep backups:** Consider taking a base backup before dropping critical replication slots
## Related Functions
– `pg_create_physical_replication_slot()` – Create physical replication slot
– `pg_create_logical_replication_slot()` – Create logical replication slot
– `pg_replication_slot_advance()` – Advance a replication slot
Remember that dropping a replication slot is irreversible and will break any replication using that slot. Always ensure no active consumers are using the slot before dropping it.