PostgreSQL Logical Replication & CDC WAL Settings

Supanote Core Team @supanote · 1 min read

plain (sql) 13 hours ago · 26 lines · 6 views

-- PostgreSQL Logical Replication & Change Data Capture (CDC) Setup

-- Step 1: Ensure postgresql.conf settings are applied:
-- wal_level = logical
-- max_wal_senders = 10
-- max_replication_slots = 10

-- Step 2: On PRIMARY Publisher Database:
CREATE PUBLICATION pub_core_events
FOR TABLE users, notes, api_tokens;

-- Step 3: On REPLICA Subscriber Database:
CREATE SUBSCRIPTION sub_core_events
CONNECTION 'host=primary.db.internal port=5432 dbname=production user=replicator password=secure_password'
PUBLICATION pub_core_events
WITH (copy_data = true, create_slot = true);

-- Step 4: Monitor Replication Lag & Status:
SELECT
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    latest_end_time
FROM pg_stat_subscription;

1-- PostgreSQL Logical Replication & Change Data Capture (CDC) Setup

3-- Step 1: Ensure postgresql.conf settings are applied:

4-- wal_level = logical

5-- max_wal_senders = 10

6-- max_replication_slots = 10

8-- Step 2: On PRIMARY Publisher Database:

9CREATE PUBLICATION pub_core_events

10FOR TABLE users, notes, api_tokens;

12-- Step 3: On REPLICA Subscriber Database:

13CREATE SUBSCRIPTION sub_core_events

14CONNECTION 'host=primary.db.internal port=5432 dbname=production user=replicator password=secure_password'

15PUBLICATION pub_core_events

16WITH (copy_data = true, create_slot = true);

18-- Step 4: Monitor Replication Lag & Status:

19SELECT

20 subname,

21 pid,

22 received_lsn,

23 latest_end_lsn,

24 latest_end_time

25FROM pg_stat_subscription;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note