I’ll help you understand PostgreSQL’s replication slots. Here’s an overview:
## What are Replication Slots?
Replication slots are a PostgreSQL feature that ensures a primary server retains WAL (Write-Ahead Log) files until they’ve been received by all replicas, preventing premature deletion.
## Common Commands
### View Replication Slots
“`sql
— Basic slot information
SELECT * FROM pg_replication_slots;
— Detailed view
SELECT
slot_name,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn,
wal_status,
safe_wal_size
FROM pg_replication_slots;
“`
### Create a Replication Slot
“`sql
— Physical replication slot
SELECT pg_create_physical_replication_slot(‘slot_name’);
— Logical replication slot
SELECT pg_create_logical_replication_slot(
‘slot_name’,
‘pgoutput’ — or other output plugin
);
“`
### Drop a Replication Slot
“`sql
SELECT pg_drop_replication_slot(‘slot_name’);
“`
## Types of Replication Slots
1. **Physical Replication Slots**
– Used for streaming replication
– Prevent WAL deletion
“`sql
— On primary
SELECT pg_create_physical_replication_slot(‘standby1_slot’);
— In recovery.conf on standby
primary_slot_name = ‘standby1_slot’
“`
2. **Logical Replication Slots**
– Used for logical replication
– Track changes at logical level
“`sql
SELECT pg_create_logical_replication_slot(
‘logical_slot’,
‘test_decoding’
);
“`
## Monitoring and Maintenance
### Check Slot Status
“`sql
— Check if slots are active
SELECT slot_name, active, pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
) AS bytes_behind
FROM pg_replication_slots;
“`
### Check for Stale Slots
“`sql
— Slots consuming excessive WAL
SELECT
slot_name,
pg_size_pretty(
pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
)
) AS lag_size
FROM pg_replication_slots
WHERE active = false;
“`
## Important Considerations
1. **WAL Accumulation**: Inactive slots can cause WAL files to accumulate indefinitely
2. **Disk Space**: Monitor `pg_wal` directory size
3. **Slot Lag**: Check `restart_lsn` vs current WAL position
4. **Active Status**: `active = true` means a client is connected
## Troubleshooting
### Common Issues:
– **WAL directory filling up**: Check for inactive slots
– **Replication lag**: Monitor `confirmed_flush_lsn`
– **Slot not advancing**: Check client connections
### Cleanup:
“`sql
— Drop inactive slots
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE active = false
AND pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1000000000;
— Adjust threshold as needed
“`
Would you like help with a specific replication slot issue or configuration?
