## PostgreSQL Replication Slots

Replication slots are a **persistent record of replication state** in PostgreSQL that ensure WAL (Write-Ahead Log) segments are retained until they’re consumed by all replicas.

### Key Concepts

**Purpose:**
– Prevent WAL deletion before replicas receive them
– Track replication progress across server restarts
– Enable logical replication without losing data

### Types of Replication Slots

#### 1. **Physical Replication Slots**
“`sql
— Create for physical replication
SELECT pg_create_physical_replication_slot(‘slot_name’);

— Use in recovery.conf or primary_conninfo
primary_conninfo = ‘… application_name=replica1’
primary_slot_name = ‘slot_name’
“`

#### 2. **Logical Replication Slots**
“`sql
— Create for logical replication
SELECT pg_create_logical_replication_slot(
‘slot_name’,
‘pgoutput’ — or ‘test_decoding’ for testing
);

— Used with publications/subscriptions
CREATE SUBSCRIPTION sub1
CONNECTION ‘…’

PUBLICATION pub1
WITH (create_slot = true, slot_name = ‘slot_name’);
“`

### Management Commands

#### View Slots
“`sql
— See all replication slots
SELECT * FROM pg_replication_slots;

— Critical columns to monitor:
SELECT
slot_name,
slot_type,
active,
pg_size_pretty(pg_wal_lsn_diff(
restart_lsn,
‘0/0’::pg_lsn
)) as retained_wal,
pg_size_pretty(pg_wal_lsn_diff(
confirmed_flush_lsn,
restart_lsn
)) as pending_wal,
active_pid
FROM pg_replication_slots;
“`

#### Drop Slots
“`sql
— Drop when no longer needed
SELECT pg_drop_replication_slot(‘slot_name’);
“`

### Monitoring & Maintenance

#### Check for Issues
“`sql
— Find inactive slots consuming WAL
SELECT slot_name, active, pg_wal_lsn_diff(
pg_current_wal_lsn(), restart_lsn
) as bytes_behind
FROM pg_replication_slots
WHERE NOT active;

— Monitor 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;
“`

#### Common Problems & Solutions

**Problem 1: Orphaned slots consuming disk**
“`sql
— If replica is down permanently
SELECT pg_drop_replication_slot(‘stale_slot’);
“`

**Problem 2: WAL disk filling up**
“`bash
# Check WAL directory size
du -sh /var/lib/postgresql/*/pg_wal

# Emergency: Temporarily increase wal_keep_size
ALTER SYSTEM SET wal_keep_size = ‘2GB’;
SELECT pg_reload_conf();
“`

### Best Practices

1. **Always monitor slots:**
“`bash
# Nagios/Icinga check
check_postgres –action=replication_slots
“`

2. **Set up alerts for:**
– Inactive slots > 1 hour
– WAL retention > 100GB
– Missing slots for critical replicas

3. **Automate cleanup** (carefully!):
“`sql
— Drop slots for disconnected replicas (use with caution)
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE NOT active
AND pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 100*1024*1024*1024; — 100GB
“`

4. **Configuration recommendations:**
“`ini
# postgresql.conf
max_replication_slots = 10 # Set higher than needed
wal_level = replica # or ‘logical’ for logical slots
hot_standby = on
“`

### Logical Replication Example

“`sql
— Publisher side
CREATE PUBLICATION mypub FOR TABLE users, orders;
SELECT pg_create_logical_replication_slot(‘logical_slot’, ‘pgoutput’);

— Subscriber side
CREATE SUBSCRIPTION mysub
CONNECTION ‘host=primary dbname=mydb’
PUBLICATION mypub
WITH (
create_slot = false,
slot_name = ‘logical_slot’
);
“`

### Important Notes

鈿狅笍 **Warnings:**
– Unused active slots can fill disk with WAL segments
– Never drop a slot still used by a replica
– Logical slots require more resources than physical slots
– Monitor `pg_stat_replication` with slots for complete picture

馃敡 **Recovery from full disk:**
1. Identify problematic slot: `SELECT * FROM pg_replication_slots WHERE NOT active;`
2. If safe to drop: `SELECT pg_drop_replication_slot(‘slot_name’);`
3. If needed temporarily: Increase `wal_keep_size` and investigate replica

Replication slots are essential for reliable replication but require active monitoring to prevent issues.