I’ll help you understand PostgreSQL replication slots and show you how to work with them. Here’s a comprehensive demo:
## **PostgreSQL Replication Slots Demo**
### **1. What are Replication Slots?**
Replication slots are a PostgreSQL feature that:
– Prevent WAL (Write-Ahead Log) files from being removed while they’re still needed by replicas
– Track replication progress for each consumer
– Ensure data isn’t lost even if replicas disconnect temporarily
### **2. Check Current Replication Slots**
“`sql
— View existing replication slots
SELECT * FROM pg_replication_slots;
— More detailed view
SELECT
slot_name,
slot_type,
active,
database,
restart_lsn,
confirmed_flush_lsn,
wal_status
FROM pg_replication_slots;
“`
### **3. Create a Replication Slot**
“`sql
— Create a physical replication slot (for physical replication)
SELECT pg_create_physical_replication_slot(‘physical_slot_demo’);
— Create a logical replication slot (for logical replication)
SELECT pg_create_logical_replication_slot(
‘logical_slot_demo’,
‘pgoutput’ — or ‘test_decoding’ for testing
);
“`
### **4. Monitor Replication Slot Activity**
“`sql
— Check WAL retention
SELECT
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_bytes,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag
FROM pg_replication_slots;
— Check if slots are active
SELECT slot_name, active, active_pid FROM pg_replication_slots;
“`
### **5. Use Replication Slot with Logical Replication**
“`sql
— Create a table for testing
CREATE TABLE demo_table (
id SERIAL PRIMARY KEY,
data TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
— Create a publication
CREATE PUBLICATION demo_pub FOR TABLE demo_table;
— Create logical slot for the publication
SELECT pg_create_logical_replication_slot(
‘logical_demo_slot’,
‘pgoutput’
);
— Check what’s in the slot
SELECT * FROM pg_logical_slot_get_changes(
‘logical_demo_slot’,
NULL,
NULL
);
“`
### **6. Streaming Changes from a Slot**
“`bash
# Use pg_recvlogical to stream changes
pg_recvlogical -d your_database \
–slot logical_demo_slot \
–start \
–file – \
-f –
# Or use SQL to peek at changes
SELECT * FROM pg_logical_slot_peek_changes(
‘logical_demo_slot’,
NULL,
NULL,
‘include-xids’, ‘1’,
‘include-timestamp’, ‘1’
);
“`
### **7. Manage Replication Slots**
“`sql
— Drop a replication slot
SELECT pg_drop_replication_slot(‘slot_name’);
— Force drop an active slot (use with caution)
SELECT pg_drop_replication_slot(‘slot_name’, true);
— Check for inactive slots consuming WAL
SELECT
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as wal_held,
active
FROM pg_replication_slots
WHERE NOT active;
“`
### **8. Common Issues and Solutions**
“`sql
— Check for replication lag
SELECT
client_addr,
usename,
application_name,
state,
sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as replay_lag_bytes
FROM pg_stat_replication;
— Find stuck slots
SELECT
slot_name,
active,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as bytes_behind
FROM pg_replication_slots
WHERE NOT active
AND pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 0;
“`
### **9. Practical Example: Setup Streaming Replication**
“`bash
# On primary server
psql -c “SELECT pg_create_physical_replication_slot(‘standby_slot’);”
# On standby’s recovery.conf (PostgreSQL 12+ in postgresql.conf)
primary_conninfo = ‘host=primary_host port=5432 user=replicator password=secret’
primary_slot_name = ‘standby_slot’
“`
### **10. Monitoring Script**
“`bash
#!/bin/bash
# monitor_slots.sh
echo “=== Replication Slots Status ===”
psql -c ”
SELECT
slot_name,
slot_type,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as wal_held,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag,
wal_status
FROM pg_replication_slots;
”
echo -e “\n=== WAL Directory Size ===”
du -sh $PGDATA/pg_wal/
“`
### **Important Notes:**
1. **Monitor slot activity** – Inactive slots can cause WAL accumulation
2. **Set `max_slot_wal_keep_size`** in postgresql.conf to prevent unlimited WAL retention
3. **Regularly check** for orphaned or inactive slots
4. **Use monitoring tools** like pg_stat_replication with pg_replication_slots
### **Troubleshooting:**
“`sql
— If WAL is growing too much:
— 1. Check which slots are inactive
— 2. Either restart the consumer or drop the slot
— 3. Monitor pg_wal directory size
— Emergency: If disk is full due to WAL
— Temporarily increase disk space
— Identify and drop problematic slots
— Consider increasing wal_keep_size as temporary fix
“`
Would you like me to explain any specific aspect in more detail or help you set up a particular replication scenario?
