Skip to main content

PostgreSQL Logical Replication

This guide explains how PostgreSQL logical replication works and how EZ-CDC uses it for CDC.

How Logical Replication Works​

PostgreSQL's logical replication decodes the Write-Ahead Log (WAL) into a stream of logical changes:

How PostgreSQL logical replication decodes WAL into logical changes

Key Concepts​

Write-Ahead Log (WAL)​

The WAL is PostgreSQL's transaction log:

  • Every change is written to WAL before being applied
  • WAL ensures durability (survives crashes)
  • Logical replication reads from WAL
-- View WAL location
SELECT pg_current_wal_lsn();
-- Returns: 0/1A3E5F8

-- View WAL file
SELECT pg_walfile_name(pg_current_wal_lsn());
-- Returns: 000000010000000000000001

Replication Slots​

Replication slots track consumption position:

-- Create a slot
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');

-- View slots
SELECT slot_name, plugin, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- Drop a slot (when done)
SELECT pg_drop_replication_slot('my_slot');

Important: Replication slots prevent WAL from being deleted. If a consumer stops, WAL will accumulate.

Publications​

Publications define which tables to replicate:

-- Create publication for specific tables
CREATE PUBLICATION my_pub FOR TABLE orders, customers;

-- Create publication for all tables
CREATE PUBLICATION my_pub FOR ALL TABLES;

-- Add table to publication
ALTER PUBLICATION my_pub ADD TABLE products;

-- View publication
SELECT * FROM pg_publication;

-- View tables in publication
SELECT * FROM pg_publication_tables WHERE pubname = 'my_pub';

Output Plugins​

Output plugins format the logical stream:

PluginDescriptionUsed By
pgoutputNative PostgreSQL formatEZ-CDC
wal2jsonJSON formatSome tools
test_decodingText formatTesting

EZ-CDC uses pgoutput (native, efficient).

Message Types​

The logical stream contains these message types:

Relation Messages​

Describe table structure:

Relation: public.orders
Columns:
  - id: int4
  - customer_id: int4
  - total: numeric
  - created_at: timestamptz

Insert Messages​

Insert: public.orders
Values: {id: 1, customer_id: 100, total: 99.99, created_at: '2024-01-15'}

Update Messages​

Update: public.orders
Key: {id: 1}
New Values: {total: 149.99}

Delete Messages​

Delete: public.orders
Key: {id: 1}

Begin/Commit Messages​

Transaction boundaries:

Begin: xid=12345
  Insert: ...
  Update: ...
Commit: xid=12345, lsn=0/1A3E5F8

How EZ-CDC Uses Logical Replication​

1. Setup Phase​

When a job starts, EZ-CDC:

-- 1. Create publication (if not exists)
CREATE PUBLICATION ezcdc_pub_123 FOR TABLE orders, customers;

-- 2. Create replication slot (if not exists)
SELECT pg_create_logical_replication_slot('ezcdc_slot_123', 'pgoutput');

2. Streaming Phase​

EZ-CDC connects using the replication protocol:

-- Replication connection
START_REPLICATION SLOT ezcdc_slot_123 LOGICAL 0/0
  (proto_version '1', publication_names 'ezcdc_pub_123')

3. Acknowledgment​

EZ-CDC confirms processed LSNs:

-- After processing, confirm position
-- This allows PostgreSQL to reclaim WAL space

4. Cleanup​

When job is deleted:

-- Drop replication slot
SELECT pg_drop_replication_slot('ezcdc_slot_123');

-- Drop publication
DROP PUBLICATION ezcdc_pub_123;

Monitoring Replication​

Replication Lag​

-- Check lag in bytes
SELECT
  slot_name,
  pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots
WHERE slot_name LIKE 'ezcdc_%';

Active Connections​

-- View replication connections
SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn
FROM pg_stat_replication;

WAL Retention​

-- Check WAL retention due to slots
SELECT
  slot_name,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Performance Considerations​

WAL Generation​

Heavy write workloads generate more WAL:

  • Normal: 10-100 MB/hour
  • Heavy: 1-10 GB/hour
  • Very Heavy: 10+ GB/hour

Disk Usage​

Monitor disk for WAL accumulation:

-- Check WAL directory size
SELECT pg_size_pretty(sum(size))
FROM pg_ls_waldir();

Connection Overhead​

Each replication connection uses:

  • One max_wal_senders slot
  • Memory for decoding (~64MB default)

Troubleshooting​

"replication slot does not exist"​

The slot was deleted or never created:

-- Check existing slots
SELECT slot_name FROM pg_replication_slots;

-- Recreate if needed (job will do this automatically)
SELECT pg_create_logical_replication_slot('ezcdc_slot_123', 'pgoutput');

"all replication slots are in use"​

Increase max_replication_slots:

# postgresql.conf
max_replication_slots = 20

"WAL segment has been removed"​

The slot fell too far behind and WAL was removed:

  1. Drop the old slot
  2. Job will recreate and start fresh (data loss possible)

Prevention: Monitor lag and ensure consumers keep up.

High WAL retention​

An inactive slot is preventing WAL cleanup:

-- Find problematic slots
SELECT slot_name, active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
FROM pg_replication_slots
WHERE NOT active;

-- Drop inactive slots if safe
SELECT pg_drop_replication_slot('inactive_slot');

Next Steps​