pg_replication_slots

`pg_replication_slots` is a **system catalog view** in PostgreSQL that provides information about all replication slots currently existing on the database cluster. Replication slots are a key feature for managing **streaming replication** and **logical replication**.

—

## **Purpose of Replication Slots**
Replication slots ensure that the primary server retains WAL (Write-Ahead Log) files until they are consumed by all replicas, even if replicas are disconnected temporarily. This prevents **WAL retention issues** and ensures data consistency in replication setups.

—

## **View Structure**
You can query it like any other table:

“`sql
SELECT * FROM pg_replication_slots;
“`

### **Key Columns**

| Column | Type | Description |
|——–|——|————-|
| `slot_name` | `text` | Unique name of the replication slot |
| `plugin` | `text` | For logical slots: output plugin (e.g., `pgoutput`, `wal2json`). For physical slots: `NULL` |
| `slot_type` | `text` | `physical` (for streaming replication) or `logical` (for logical replication) |
| `datoid` | `oid` | Database OID (for logical slots) or `NULL` (for physical slots) |
| `database` | `text` | Database name (logical slots only) |
| `temporary` | `bool` | `true` if slot is temporary (dropped on session end) |
| `active` | `bool` | `true` if slot is actively being used |
| `active_pid` | `int` | PID of the session using this slot, or `NULL` |
| `xmin` | `xid` | Oldest transaction that may still be needed by this slot (helps with vacuum) |
| `catalog_xmin` | `xid` | Oldest transaction affecting system catalogs that may be needed by logical slots |
| `restart_lsn` | `pg_lsn` | WAL location from which the slot will restart streaming |
| `confirmed_flush_lsn` | `pg_lsn` | For logical slots: LSN up which data has been flushed to output |
| `wal_status` | `text` | Status of WAL files for the slot: `reserved`, `extended`, `unreserved`, `lost` |
| `safe_wal_size` | `int8` | Estimated safe WAL size that can be removed without affecting slot |
| `two_phase` | `bool` | `true` if slot is used for two-phase commit decoding (PG14+) |

—

## **Common Use Cases**

### **1. Monitor Replication Slots**
“`sql
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;
“`

### **2. Check for Inactive Slots**
Inactive slots can cause WAL accumulation:
“`sql
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots
WHERE NOT active;
“`

### **3. Identify WAL Retention Impact**
“`sql
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
wal_status
FROM pg_replication_slots;
“`

### **4. Find Logical Slots**
“`sql
SELECT slot_name, database, plugin, active_pid
FROM pg_replication_slots
WHERE slot_type = ‘logical’;
“`

—

## **Important Management Commands**

### **Create a Physical Replication Slot**
“`sql
SELECT pg_create_physical_replication_slot(‘slot_name’);
“`

### **Create a Logical Replication Slot**
“`sql
SELECT pg_create_logical_replication_slot(‘slot_name’, ‘pgoutput’);
“`

### **Drop a Slot**
“`sql
SELECT pg_drop_replication_slot(‘slot_name’);
“`

—

## **Troubleshooting**

### **Problem: WAL files accumulating**
If `pg_wal` directory grows excessively:
“`sql
— Check which slots are retaining WAL
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
ORDER BY wal_retained DESC;
“`

### **Problem: Orphaned slots**
Slots with `active = false` but no connection using them may need cleanup:
“`sql
SELECT slot_name, active, active_pid
FROM pg_replication_slots
WHERE NOT active AND active_pid IS NOT NULL;
“`

### **Problem: Catalog bloat prevention**
Logical slots can prevent vacuum from cleaning up old tuples:
“`sql
— Check which slots are holding back xmin
SELECT slot_name, xmin, catalog_xmin, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL;
“`

—

## **Best Practices**

1. **Monitor regularly**: Include slot monitoring in your health checks
2. **Clean up unused slots**: Always drop slots that are no longer needed
3. **Set `max_slot_wal_keep_size`** (PostgreSQL 13+): Limits WAL retention per slot
4. **Use meaningful names**: Especially in environments with multiple slots
5. **Monitor `pg_replication_slots.wal_status`**: If it shows `”lost”`, the replica cannot catch up

—

## **Related Views/Functions**
– `pg_stat_replication`: Shows active replication connections
– `pg_current_wal_lsn()`: Current WAL location
– `pg_wal_lsn_diff()`: Calculate difference between two LSNs
– `pg_replication_slot_advance()`: Manually advance a slot’s position

—

## **Example: Comprehensive Monitoring Query**
“`sql
SELECT
slot_name,
slot_type,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained,
wal_status,
active_pid,
age(xmin) AS xmin_age,
age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;
“`

This view is essential for managing PostgreSQL replication, ensuring WAL files are properly retained, and preventing replication issues.