`pg_drop_replication_slot` is a **system administration function** in PostgreSQL used to remove a replication slot. Here’s a comprehensive explanation:
## What is a Replication Slot?
A replication slot is a feature that ensures a primary server retains WAL (Write-Ahead Log) files until they’ve been consumed by all connected replicas, preventing premature deletion of WAL files needed for replication.
## Function Syntax
“`sql
pg_drop_replication_slot(slot_name text)
“`
## Usage Examples
### 1. Basic Usage
“`sql
— Drop a replication slot named ‘my_slot’
SELECT pg_drop_replication_slot(‘my_slot’);
“`
### 2. Check Existing Slots First
“`sql
— List all replication slots
SELECT * FROM pg_replication_slots;
— Then drop the specific slot
SELECT pg_drop_replication_slot(‘slot_name’);
“`
### 3. Drop Slot with Active Connection
If a slot has an active connection, you may need to:
“`sql
— First, terminate any connections using the slot
SELECT pg_terminate_backend(active_pid)
FROM pg_replication_slots
WHERE slot_name = ‘my_slot’;
— Then drop the slot
SELECT pg_drop_replication_slot(‘my_slot’);
“`
## Important Considerations
### **Permissions**
– Requires **superuser** privileges or membership in the `pg_drop_replication_slot` role (PostgreSQL 10+)
– Regular users cannot execute this function
### **When to Use**
1. **Cleaning up unused slots** – Orphaned slots can cause WAL accumulation
2. **Changing replication configuration** – When reconfiguring logical replication
3. **Troubleshooting** – When replication slots are causing issues
4. **Migration/upgrades** – As part of maintenance procedures
### **Risks and Warnings**
– **Data loss risk**: If a replica is still using the slot, dropping it may break replication
– **WAL accumulation**: Slots prevent WAL deletion; dropping frees up space
– **No undo**: This operation cannot be rolled back
## Common Scenarios
### Scenario 1: Orphaned Logical Replication Slot
“`sql
— Check for inactive logical slots
SELECT slot_name, active, pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = ‘logical’;
— Drop if confirmed orphaned
SELECT pg_drop_replication_slot(‘orphaned_slot’);
“`
### Scenario 2: Physical Replication Slot Cleanup
“`sql
— For physical replication (streaming replication)
SELECT pg_drop_replication_slot(‘standby1_slot’);
“`
## Best Practices
1. **Always verify first**:
“`sql
— Check slot status before dropping
SELECT slot_name, active, pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS replication_lag
FROM pg_replication_slots;
“`
2. **Monitor WAL growth**:
“`sql
— Check if slots are causing WAL accumulation
SELECT slot_name,
pg_size_pretty(
pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
)
) AS pending_wal
FROM pg_replication_slots;
“`
3. **Use in maintenance windows** when possible
## Alternative Approach (for logical slots)
For logical replication slots, you can also use:
“`sql
— Using the replication protocol
DROP_REPLICATION_SLOT slot_name;
“`
## Troubleshooting
### Error: “replication slot is active”
“`sql
— Find which process is using the slot
SELECT pid, application_name, client_addr
FROM pg_stat_replication
WHERE pid IN (
SELECT active_pid
FROM pg_replication_slots
WHERE slot_name = ‘problem_slot’
);
“`
### Error: “permission denied”
– Verify you’re connecting as a superuser
– Check role memberships in PostgreSQL 10+
## Related Functions
– `pg_create_physical_replication_slot()` – Create physical slots
– `pg_create_logical_replication_slot()` – Create logical slots
– `pg_replication_slot_advance()` – Move slot forward
## System Impact
– Immediately allows WAL cleanup if slot was retaining files
– Frees system resources associated with the slot
– Breaks any replication depending on the slot
Always ensure no active replicas are using a slot before dropping it, and consider taking backups before major replication configuration changes.
