To create a logical replication slot in PostgreSQL, you have several methods. Here are the most common approaches:
## 1. **Using SQL Command**
“`sql
— Create a logical replication slot
SELECT * FROM pg_create_logical_replication_slot(
‘slot_name’,
‘output_plugin’
);
— Example with pgoutput (PostgreSQL 10+ built-in)
SELECT * FROM pg_create_logical_replication_slot(
‘my_replication_slot’,
‘pgoutput’
);
— Example with wal2json (requires extension)
SELECT * FROM pg_create_logical_replication_slot(
‘my_json_slot’,
‘wal2json’
);
“`
## 2. **Using pg_recvlogical (Command Line)**
“`bash
# Create slot using pg_recvlogical
pg_recvlogical -d your_database \
–slot=my_slot \
–create-slot \
–plugin=pgoutput \
–start
“`
## 3. **Using psql with Parameters**
“`bash
# Connect and create slot
psql -d your_database -c \
“SELECT * FROM pg_create_logical_replication_slot(‘my_slot’, ‘pgoutput’);”
“`
## **Common Output Plugins:**
– **`pgoutput`** (PostgreSQL 10+ built-in) – for native logical replication
– **`wal2json`** – outputs changes as JSON (requires installation)
– **`decoderbufs`** – used by Debezium
– **`test_decoding`** – for testing/debugging
## **Check Existing Slots:**
“`sql
— View all replication slots
SELECT * FROM pg_replication_slots;
— View logical slots only
SELECT slot_name, plugin, slot_type, active
FROM pg_replication_slots
WHERE slot_type = ‘logical’;
“`
## **Important Notes:**
1. **Prerequisites:**
“`sql
— Ensure wal_level is ‘logical’
SHOW wal_level;
— If not ‘logical’, modify postgresql.conf:
— wal_level = logical
— Then restart PostgreSQL
“`
2. **Permissions:**
“`sql
— User must have REPLICATION privilege
ALTER USER your_user WITH REPLICATION;
“`
3. **Cleanup:**
“`sql
— Drop a replication slot when no longer needed
SELECT pg_drop_replication_slot(‘slot_name’);
“`
## **Example Workflow:**
“`sql
— 1. Check current configuration
SHOW wal_level;
— 2. Create the slot
SELECT * FROM pg_create_logical_replication_slot(
‘inventory_slot’,
‘pgoutput’
);
— 3. Verify creation
SELECT slot_name, plugin, active, confirmed_flush_lsn
FROM pg_replication_slots;
— 4. Use with a publication
CREATE PUBLICATION inventory_pub FOR TABLE users, orders;
“`
## **Troubleshooting:**
If you encounter errors:
– Ensure `max_replication_slots` is sufficient in postgresql.conf
– Check `max_wal_senders` is configured
– Verify user has REPLICATION privilege
– Confirm PostgreSQL version supports logical replication (10+)
Would you like help with a specific output plugin or replication scenario?
