Logical Replication

Logical replication works at the row level rather than the WAL byte level. It uses a publish/subscribe model where the publisher sends row changes and the subscriber applies them. This gives you selective replication, cross-version compatibility, and use cases that physical replication cannot serve.

How It Differs from Streaming Replication

The key insight: logical replication decodes WAL records into logical row operations (INSERT, UPDATE, DELETE) and sends those instead of raw bytes.

Setting Up Logical Replication

Step 1: Configure the Publisher

# postgresql.conf on the publisher
wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

Restart PostgreSQL after changing wal_level.

Step 2: Create a Publication

-- Publish all changes for specific tables
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- Publish all tables in the database
CREATE PUBLICATION all_pub FOR ALL TABLES;

-- Publish only INSERT operations (no updates or deletes)
CREATE PUBLICATION inserts_only FOR TABLE events
WITH (publish = 'insert');

Step 3: Configure the Subscriber

The subscriber is a separate PostgreSQL instance. The target tables must already exist with compatible schemas.

-- On the subscriber: create matching tables first
CREATE TABLE orders (
    id serial PRIMARY KEY,
    customer_id int NOT NULL,
    amount numeric NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id serial PRIMARY KEY,
    order_id int NOT NULL,
    product_id int NOT NULL,
    quantity int NOT NULL
);

Step 4: Create a Subscription

-- On the subscriber
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=publisher-host port=5432 dbname=mydb user=replicator password=secret'
PUBLICATION orders_pub;

This triggers an initial table synchronization (copying existing data) and then streams ongoing changes.

Step 5: Verify

On the publisher:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';

On the subscriber:

SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription_rel;

Publication Options

Filtering by Operation

-- Only replicate inserts and updates, not deletes
CREATE PUBLICATION filtered_pub FOR TABLE orders
WITH (publish = 'insert, update');

Row Filters (PostgreSQL 15+)

-- Only replicate orders from a specific region
CREATE PUBLICATION regional_pub FOR TABLE orders
WHERE (region = 'us-west');

Column Lists (PostgreSQL 15+)

-- Only replicate specific columns
CREATE PUBLICATION partial_pub FOR TABLE orders (id, customer_id, status);

Adding and Removing Tables

ALTER PUBLICATION orders_pub ADD TABLE shipments;
ALTER PUBLICATION orders_pub DROP TABLE order_items;

-- On the subscriber, refresh to pick up changes
ALTER SUBSCRIPTION orders_sub REFRESH PUBLICATION;

Use Case: Zero-Downtime Major Version Upgrades

This is the most common reason teams adopt logical replication.

The Process

1. Set up a new PostgreSQL instance on the target major version.
2. Create the schema on the new instance (use pg_dump --schema-only).
3. Create a publication on the old instance for all tables.
4. Create a subscription on the new instance.
5. Wait for the initial sync to complete and replication to catch up.
6. Verify data consistency.
7. Stop writes to the old instance.
8. Wait for final replication to complete.
9. Point the application to the new instance.
10. Drop the subscription and publication.

-- On the old instance (PostgreSQL 14)
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- On the new instance (PostgreSQL 16)
CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=old-host port=5432 dbname=mydb user=replicator password=secret'
PUBLICATION upgrade_pub;

-- Monitor progress
SELECT * FROM pg_stat_subscription;

This approach avoids the downtime of pg_upgrade and the hours-long dump/restore cycle for large databases.

Use Case: Selective Replication

Replicate only specific tables to a reporting database or a data warehouse.

-- Publisher: analytics-relevant tables only
CREATE PUBLICATION analytics_pub FOR TABLE
    orders, customers, products, order_items;

-- Subscriber (analytics database): can have different indexes,
-- materialized views, and additional columns
CREATE SUBSCRIPTION analytics_sub
CONNECTION 'host=prod-host port=5432 dbname=proddb user=replicator password=secret'
PUBLICATION analytics_pub;

The subscriber can have its own indexes optimized for analytical queries without affecting production.

Use Case: Data Distribution

Send data from a central database to regional databases.

-- Central publisher with row filters (PostgreSQL 15+)
CREATE PUBLICATION us_west_pub FOR TABLE orders
WHERE (region = 'us-west');

CREATE PUBLICATION us_east_pub FOR TABLE orders
WHERE (region = 'us-east');

-- Each regional subscriber gets only its data

Logical Decoding for CDC (Change Data Capture)

Logical decoding is the underlying mechanism that logical replication uses. You can access it directly for CDC — streaming database changes to external systems like Kafka, Elasticsearch, or data lakes.

Using the test_decoding Plugin

-- Create a logical replication slot
SELECT pg_create_logical_replication_slot('cdc_slot', 'test_decoding');

-- Make some changes
INSERT INTO orders (customer_id, amount, status)
VALUES (42, 99.99, 'pending');

-- Read the decoded changes
SELECT * FROM pg_logical_slot_get_changes('cdc_slot', NULL, NULL);

    lsn    | xid  |                          data
-----------+------+----------------------------------------------------------
 0/1234568 | 1001 | BEGIN 1001
 0/1234570 | 1001 | table public.orders: INSERT: id[integer]:1 customer_id...
 0/1234580 | 1001 | COMMIT 1001

Popular CDC Tools

• Debezium: streams changes from PostgreSQL to Kafka using the pgoutput or wal2json logical decoding plugin.
• wal2json: outputs changes as JSON for consumption by any system.

-- Create a slot using wal2json
SELECT pg_create_logical_replication_slot('json_slot', 'wal2json');

Important: Slot Management

Logical replication slots retain WAL on the publisher. If the consumer falls behind or stops, WAL accumulates.

-- Monitor slot lag
SELECT slot_name, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- Drop an unused slot to stop WAL retention
SELECT pg_drop_replication_slot('unused_slot');

Handling DDL Changes

Logical replication does not replicate DDL. When you change the schema:

1. Apply the DDL change on the subscriber first (if adding a column).
2. Apply the DDL change on the publisher.
3. Or apply on the publisher first, then the subscriber (if dropping a column).

-- Adding a column: subscriber first
-- On subscriber:
ALTER TABLE orders ADD COLUMN priority int DEFAULT 0;
-- On publisher:
ALTER TABLE orders ADD COLUMN priority int DEFAULT 0;

-- Dropping a column: publisher first
-- On publisher:
ALTER TABLE orders DROP COLUMN old_field;
-- On subscriber:
ALTER TABLE orders DROP COLUMN old_field;

The order matters to prevent replication errors from schema mismatches.

Sequences & Identity Columns

Logical replication does not replicate sequence values. The subscriber's sequences advance independently based on inserts it receives. After failover, you must manually advance sequences:

-- On the new primary (former subscriber)
SELECT setval('orders_id_seq', (SELECT max(id) FROM orders) + 1000);

The +1000 buffer prevents ID collisions during the transition.

Common Pitfalls

• Forgetting that DDL is not replicated. Schema changes on the publisher do not propagate. You must apply them manually on both sides in the right order.
• Not monitoring replication slot WAL retention. A stalled subscriber causes WAL to accumulate on the publisher, just like with physical replication slots.
• Missing REPLICA IDENTITY on tables without a primary key. UPDATE and DELETE need a way to identify rows. Without a primary key, set REPLICA IDENTITY FULL (slower) or add a primary key.
• Expecting sequences to stay in sync. They do not. Plan for sequence management during failover.
• Not creating the target schema before the subscription. The subscription will fail if the tables do not exist on the subscriber.
• Running initial sync on a large table without planning for the I/O impact. The initial COPY can be resource-intensive on both publisher and subscriber.

Key Takeaways

• Logical replication works at the row level, enabling selective table replication, cross-version replication, and writable subscribers.
• The primary use case is zero-downtime major version upgrades.
• Publications control what is replicated (tables, operations, row filters, column lists). Subscriptions control where it goes.
• DDL is not replicated. Manage schema changes manually on both sides.
• Logical decoding is the foundation for CDC, streaming changes to Kafka, data lakes, and other external systems.
• Monitor replication slots aggressively — stalled consumers cause WAL accumulation.