Here’s a comprehensive guide on `pg_create_logical_replication_slot` in PostgreSQL:
## Overview
`pg_create_logical_replication_slot` is a system function used to create a **logical replication slot** in PostgreSQL. Logical replication slots track changes in the database for logical decoding and replication.
## Syntax
“`sql
SELECT * FROM pg_create_logical_replication_slot(
slot_name name,
plugin name,
temporary boolean DEFAULT false,
two_phase boolean DEFAULT false,
failover boolean DEFAULT false
);
“`
## Parameters
| Parameter | Type | Description |
|———–|——|————-|
| `slot_name` | `name` | Unique name for the replication slot |
| `plugin` | `name` | Logical decoding output plugin (e.g., `pgoutput`, `wal2json`, `test_decoding`) |
| `temporary` | `boolean` | If true, slot is automatically dropped on session end (default: false) |
| `two_phase` | `boolean` | Enable two-phase commit decoding (default: false) |
| `failover` | `boolean` | Enable failover support (default: false) |
## Common Output Plugins
### 1. **pgoutput** (Built-in, for logical replication)
“`sql
SELECT * FROM pg_create_logical_replication_slot(
‘my_slot’,
‘pgoutput’
);
“`
### 2. **test_decoding** (For testing/development)
“`sql
SELECT * FROM pg_create_logical_replication_slot(
‘test_slot’,
‘test_decoding’
);
“`
### 3. **wal2json** (Outputs changes as JSON)
“`sql
— First install the extension
CREATE EXTENSION IF NOT EXISTS wal2json;
SELECT * FROM pg_create_logical_replication_slot(
‘json_slot’,
‘wal2json’
);
“`
## Examples
### Basic Usage
“`sql
— Create a logical replication slot
SELECT * FROM pg_create_logical_replication_slot(
‘my_replication_slot’,
‘pgoutput’
);
— Output:
— slot_name | lsn
— —————–+————
— my_replication_slot | 0/16B1970
“`
### With Two-Phase Commit Support
“`sql
SELECT * FROM pg_create_logical_replication_slot(
‘two_phase_slot’,
‘pgoutput’,
false, — temporary
true — two_phase
);
“`
### Temporary Slot
“`sql
— Temporary slot (dropped when session ends)
SELECT * FROM pg_create_logical_replication_slot(
‘temp_slot’,
‘test_decoding’,
true — temporary
);
“`
## Viewing Replication Slots
“`sql
— Check existing slots
SELECT * FROM pg_replication_slots;
— Get specific information
SELECT
slot_name,
plugin,
slot_type,
active,
confirmed_flush_lsn
FROM pg_replication_slots;
“`
## Using the Slot
### 1. **Decoding Changes**
“`sql
— Get changes from the slot
SELECT * FROM pg_logical_slot_get_changes(
‘my_slot’,
NULL, — start LSN (NULL = from beginning)
NULL, — end LSN (NULL = all available)
‘include-xids’, ‘1’,
‘skip-empty-xacts’, ‘1’
);
“`
### 2. **Peek Changes** (without consuming)
“`sql
SELECT * FROM pg_logical_slot_peek_changes(
‘my_slot’,
NULL,
NULL
);
“`
## Dropping a Slot
“`sql
— Drop when no longer needed
SELECT pg_drop_replication_slot(‘my_slot’);
“`
## Important Considerations
### 1. **WAL Retention**
– Logical slots prevent WAL removal until changes are consumed
– Monitor disk usage with:
“`sql
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag
FROM pg_replication_slots;
“`
### 2. **Permissions**
– Requires `REPLICATION` privilege:
“`sql
GRANT REPLICATION TO my_user;
“`
### 3. **Configuration**
Ensure these settings in `postgresql.conf`:
“`ini
wal_level = logical
max_replication_slots = 10 # or higher
max_wal_senders = 10 # or higher
“`
### 4. **Monitoring**
“`sql
— Check slot activity
SELECT
slot_name,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag
FROM pg_replication_slots;
“`
## Common Use Cases
### 1. **Logical Replication Setup**
“`sql
— Publisher side
SELECT pg_create_logical_replication_slot(‘subscription_slot’, ‘pgoutput’);
— Subscriber side
CREATE SUBSCRIPTION my_subscription
CONNECTION ‘host=192.168.1.100 port=5432 dbname=mydb’
PUBLICATION my_publication
WITH (create_slot = false, slot_name = ‘subscription_slot’);
“`
### 2. **Change Data Capture (CDC)**
“`sql
— Create slot for CDC
SELECT pg_create_logical_replication_slot(‘cdc_slot’, ‘wal2json’);
— Consume changes in application
— (Application connects and reads changes using the slot)
“`
### 3. **Audit Trail**
“`sql
— Track all DML changes
SELECT pg_create_logical_replication_slot(‘audit_slot’, ‘test_decoding’);
“`
## Troubleshooting
### Slot Not Consuming Changes
“`sql
— Check if slot is active
SELECT active FROM pg_replication_slots WHERE slot_name = ‘my_slot’;
— Force advance if stuck
SELECT pg_replication_slot_advance(‘my_slot’, pg_current_wal_lsn());
“`
### WAL Accumulation
“`sql
— Check WAL retention
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
FROM pg_replication_slots;
“`
## Best Practices
1. **Name slots meaningfully** (e.g., `app_cdc_slot`, `reporting_subscription_slot`)
2. **Monitor slot lag** regularly
3. **Drop unused slots** to prevent WAL accumulation
4. **Use temporary slots** for one-time operations
5. **Set appropriate `max_replication_slots`** in configuration
6. **Regularly consume changes** from active slots
This function is essential for setting up logical replication, CDC systems, and other change-tracking mechanisms in PostgreSQL.
