pg_create_physical_replication_slot

`pg_create_physical_replication_slot` is a **system function** in PostgreSQL used to create a **physical replication slot** for **streaming replication** or **WAL archiving**.

## 馃搶 Purpose
– Tracks WAL (Write-Ahead Log) retention on the primary server
– Ensures WAL files aren’t deleted before being consumed by replicas
– Essential for **reliable replication** and **backup management**

## 馃摑 Basic Syntax
“`sql
SELECT pg_create_physical_replication_slot(

slot_name name,
immediately_reserve boolean DEFAULT false,
temporary boolean DEFAULT false,
two_phase boolean DEFAULT false
);
“`

## 馃敡 Parameters

| Parameter | Type | Default | Description |
|———–|——|———|————-|
| `slot_name` | name | (required) | Unique name for the replication slot |
| `immediately_reserve` | boolean | false | Immediately reserve WAL from current position |
| `temporary` | boolean | false | Create a temporary slot (auto-drops on error/disconnect) |
| `two_phase` | boolean | false | Enable two-phase commit decoding (v14+) |

## 馃搳 Common Usage Examples

### 1. **Basic Physical Replication Slot**
“`sql
— Create a standard physical replication slot
SELECT pg_create_physical_replication_slot(‘replica_slot_1’);
“`

### 2. **With Immediate WAL Reservation**
“`sql
— Reserve WAL immediately (useful for PITR backups)
SELECT pg_create_physical_replication_slot(
‘backup_slot’,
true — immediately_reserve
);
“`

### 3. **Temporary Replication Slot**
“`sql
— Temporary slot for one-time operations
SELECT pg_create_physical_replication_slot(
‘temp_backup_slot’,
false, — immediately_reserve
true — temporary
);
“`

## 馃攳 View Existing Slots
“`sql
— Check all replication slots
SELECT * FROM pg_replication_slots;

— View specific physical slots
SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots
WHERE slot_type = ‘physical’;
“`

## 馃棏锔?Drop a Replication Slot
“`sql
— Drop when no longer needed
SELECT pg_drop_replication_slot(‘replica_slot_1’);
“`

## 鈿狅笍 Important Considerations

### **WAL Accumulation Warning**
“`sql
— Monitor WAL retention
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_size,
active
FROM pg_replication_slots;
“`

### **Best Practices**
1. **Monitor slot usage** – Orphaned slots can cause unlimited WAL growth
2. **Drop unused slots** – Always drop slots not actively used by replicas
3. **Set `max_slot_wal_keep_size`** (v13+) to prevent disk filling
4. **Use meaningful names** for easier management

### **Common Errors**
“`sql
— ERROR: replication slot “slot_name” already exists
— Solution: Drop existing slot first or use different name

— ERROR: all replication slots are in use
— Solution: Increase max_replication_slots in postgresql.conf
“`

## 馃洜锔?Configuration Requirements
In `postgresql.conf`:
“`ini
# Enable replication
wal_level = replica # or logical

# Allow replication connections
max_wal_senders = 10
max_replication_slots = 10 # Must be > 0
“`

## 馃搱 Real-World Example
“`sql
— Setup for streaming replica
— On PRIMARY server:
SELECT pg_create_physical_replication_slot(‘replica1_slot’);

— On REPLICA’s recovery.conf (or postgresql.auto.conf in v12+):
primary_slot_name = ‘replica1_slot’
“`

## 馃攧 Related Functions
– `pg_create_logical_replication_slot()` – For logical replication
– `pg_drop_replication_slot()` – Remove a slot
– `pg_replication_slot_advance()` – Manually advance slot position

## 馃幆 When to Use
– **Streaming replication** setups
– **WAL archiving** with tools like pg_basebackup
– **Point-in-Time Recovery** (PITR) preparations
– **Backup solutions** like Barman, WAL-G

**Note:** Requires `SUPERUSER` or `REPLICATION` privileges. Always ensure proper monitoring to prevent WAL disk space issues.