Replication Slot Management

In this lesson, you will learn how to manage physical and logical replication slots to ensure data consistency between your primary and standby databases while preventing catastrophic storage exhaustion.

The Problem: The "Ghost" WAL Files

Replication slots are PostgreSQL's way of ensuring that a primary server does not delete Write-Ahead Log (WAL) files that a standby still needs to catch up. When a standby falls behind, the primary holds onto those WAL files indefinitely. If the standby remains disconnected for too long, these files accumulate, eventually consuming all available disk space on the primary—a classic "disk-full" scenario that can crash the entire system.

Inspecting Your Slots

Before taking action, you must audit the current status of your slots. You can view which slots exist, whether they are active, and how far behind they are using the following query:

SELECT slot_name, slot_type, active, restart_lsn, 
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS replication_lag_size
FROM pg_replication_slots;

The restart_lsn indicates the oldest WAL segment the slot requires. If active is f (false) and the replication_lag_size is growing rapidly, you have a "zombie" slot that is anchoring your WAL files to the disk.

Management Strategies

To prevent storage issues, you must proactively manage these slots:

1. Monitoring: Always monitor the replication_lag_size of your slots. If a slot becomes inactive, investigate why the standby stopped communicating immediately.
2. Removal: If a standby is decommissioned, you must manually drop the associated slot to allow the primary to purge those old WAL files:

   SELECT pg_drop_replication_slot('your_slot_name');
   

3. Configuring Limits: In PostgreSQL 13 and later, consider using the max_slot_wal_keep_size configuration parameter. This setting acts as a safety valve: if the WAL volume retained by a slot exceeds this limit, the primary will allow the slot to become invalidated, sacrificing replication to save the primary’s disk from filling up.

Effective management requires balancing the need for data consistency against the physical reality of your storage limits.

Key Takeaway: Replication slots ensure data integrity, but orphaned or lagging slots are a primary cause of disk exhaustion; monitor them constantly and drop them immediately when they are no longer required.