You’re asking about PostgreSQL replication slots. Here’s an overview of PostgreSQL replication slot status and management:
## **Check Replication Slot Status**
### 1. **View All Replication Slots**
“`sql
SELECT * FROM pg_replication_slots;
“`
### 2. **Detailed Slot Information**
“`sql
SELECT
slot_name,
slot_type,
database,
active,
restart_lsn,
confirmed_flush_lsn,
wal_status,
safe_wal_size
FROM pg_replication_slots;
“`
### 3. **Using psql Command**
“`bash
psql -c “SELECT * FROM pg_replication_slots;”
“`
## **Key Columns to Monitor**
– **`active`**: Whether the slot is currently active (has a connected consumer)
– **`restart_lsn`**: The oldest WAL location needed by this slot
– **`confirmed_flush_lsn`**: Last WAL position confirmed flushed to the replica
– **`wal_status`**: Status of WAL files (reserved/extended/…)
– **`safe_wal_size`**: How much WAL can be kept before causing issues
## **Common Issues & Solutions**
### **Problem: WAL Accumulation**
Slots not being consumed cause WAL files to accumulate:
“`sql
— Check WAL retention
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_size
FROM pg_replication_slots;
“`
### **Solution: Drop Unused Slots**
“`sql
— Drop a specific slot
SELECT pg_drop_replication_slot(‘slot_name’);
— Drop inactive physical slots
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE NOT active AND slot_type = ‘physical’;
“`
## **Monitoring Commands**
### **Check Slot Lag**
“`sql
SELECT
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag,
active,
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) as bytes_pending
FROM pg_replication_slots;
“`
### **Check Disk Space Used by Slots**
“`sql
SELECT
slot_name,
pg_size_pretty(
pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
)
) as retained_wal
FROM pg_replication_slots;
“`
## **Important Notes**
1. **Physical vs Logical Slots**:
– **Physical slots**: Used for physical replication (streaming)
– **Logical slots**: Used for logical replication (publications/subscriptions)
2. **Danger of Unused Slots**: Unused replication slots can cause unlimited WAL accumulation, filling up disk space
3. **Always monitor**:
– Active status of slots
– WAL retention size
– Disk space on WAL directory
Would you like help with a specific replication slot issue or need more details about a particular aspect?
