pg_create_logical_replication_slot

`pg_create_logical_replication_slot` is a **system function** in PostgreSQL used to create a **logical replication slot**. Logical replication slots are essential for streaming logical changes (row-level changes) from a PostgreSQL database to external systems.

—

## **Purpose**
– Track changes at the **logical level** (specific tables, DML operations).
– Ensure changes are retained in WAL until consumed by a downstream client.
– Used by logical replication, CDC tools like **Debezium**, or custom consumers.

—

## **Syntax**
“`sql
SELECT * FROM pg_create_logical_replication_slot(
slot_name text,
plugin_name text,
temporary boolean DEFAULT false,
two_phase boolean DEFAULT false,
failover boolean DEFAULT false
);
“`

—

## **Parameters**

| Parameter | Type | Description |
|———–|——|————-|
| `slot_name` | `text` | Unique name for the replication slot. |
| `plugin_name` | `text` | Logical decoding plugin (e.g., `pgoutput`, `wal2json`, `test_decoding`). |
| `temporary` | `boolean` | If `true`, slot is automatically dropped on session end or error. Default `false`. |
| `two_phase` | `boolean` | If `true`, enables decoding of prepared transactions. Default `false`. |
| `failover` | `boolean` | If `true`, slot is a **failover slot** (physical standby → logical primary). Default `false`. |

—

## **Common Decoding Plugins**

| Plugin | Purpose |
|——–|———|
| `pgoutput` | Built-in plugin for PostgreSQL logical replication. |
| `wal2json` | Outputs changes as JSON (requires installation). |
| `test_decoding` | Example plugin for debugging (outputs text). |
| `decoderbufs` | Used by Debezium for Protobuf output. |

—

## **Examples**

### 1. Create a logical slot with `pgoutput` (for native logical replication)
“`sql
SELECT * FROM pg_create_logical_replication_slot(‘my_slot’, ‘pgoutput’);
“`

### 2. Create a temporary slot for testing
“`sql
SELECT * FROM pg_create_logical_replication_slot(‘temp_slot’, ‘test_decoding’, true);
“`

### 3. Create a slot with `wal2json` (if installed)
“`sql
SELECT * FROM pg_create_logical_replication_slot(‘json_slot’, ‘wal2json’);
“`

### 4. Create a slot with two-phase commit support
“`sql
SELECT * FROM pg_create_logical_replication_slot(‘two_phase_slot’, ‘pgoutput’, false, true);
“`

—

## **Output Columns**
Returns a row with:
– `slot_name`: Name of the created slot.
– `lsn`: The **LSN** (Log Sequence Number) at which the slot starts tracking.

Example output:
“`
slot_name | lsn
———–+———–
my_slot | 0/16B1970
“`

—

## **Important Notes**

### **1. Superuser or Replication Role Required**
Only users with `REPLICATION` privilege or superusers can create replication slots.

### **2. WAL Retention**
Logical slots prevent WAL cleanup until changes are consumed. **Monitor disk usage** to avoid WAL accumulation.

### **3. Check Existing Slots**
“`sql
SELECT * FROM pg_replication_slots;
“`

### **4. Drop a Slot**
“`sql
SELECT pg_drop_replication_slot(‘my_slot’);
“`

### **5. Use Cases**
– **Logical replication** between PostgreSQL databases.
– **Change Data Capture (CDC)** for data pipelines.
– **Auditing** by streaming changes to external systems.

—

## **Example Workflow for Logical Replication**

“`sql
— 1. Create a publication
CREATE PUBLICATION my_pub FOR TABLE users, orders;

— 2. Create a replication slot
SELECT * FROM pg_create_logical_replication_slot(‘my_replication_slot’, ‘pgoutput’);

— 3. On the subscriber side, create a subscription
CREATE SUBSCRIPTION my_sub
CONNECTION ‘host=source_db user=replicator dbname=mydb’
PUBLICATION my_pub
WITH (create_slot = false, slot_name = ‘my_replication_slot’);
“`

—

## **Troubleshooting**

### **Error: “Logical decoding requires wal_level >= logical”**
“`sql
— Check current wal_level
SHOW wal_level;

— Set in postgresql.conf and restart
wal_level = logical
“`

### **Error: “Replication slot already exists”**
Drop the existing slot first or choose a different name.

### **Plugin Not Found**
Ensure the plugin is installed (e.g., via `pgxn` or OS packages) and available in `pg_available_extensions`.

—

## **Related Functions**
– `pg_create_physical_replication_slot()` – For physical replication slots.
– `pg_logical_slot_get_changes()` – Consume changes from a logical slot.
– `pg_drop_replication_slot()` – Remove a slot.

—

Let me know if you need help with a specific use case or configuration!