PostgreSQL Logical Replication

Logical replication decodes WAL into row-level changes and applies them on a subscriber. Unlike streaming replication, the subscriber can run a different major version, hold a subset of tables, and have its own indexes and extra columns.

Setting it up

-- Publisher: wal_level = logical, then restart.
CREATE PUBLICATION shop_pub FOR TABLE orders, order_items, customers;
 
-- Or everything, if that is genuinely what you want:
-- CREATE PUBLICATION shop_pub FOR ALL TABLES;

-- Subscriber: schema must already exist.
CREATE SUBSCRIPTION shop_sub
    CONNECTION 'host=primary.internal dbname=shop user=replicator password=… sslmode=verify-full'
    PUBLICATION shop_pub
    WITH (copy_data = true, streaming = on);

copy_data = true performs an initial snapshot of each table before streaming changes.

Replica identity

Updates and deletes need a way to identify the row on the subscriber. The default is the primary key.

What is not replicated

Logical replication carries table data only. It does not carry:

• DDL. Schema changes must be applied to the subscriber separately, usually before the publisher.
• Sequence values. After a cutover, advance sequences manually or the first insert collides.
• Large objects.
• TRUNCATE, unless the publication includes it.

These omissions are the usual cause of a failed cutover, so build them into the runbook.

Monitoring

-- Publisher: how far behind each subscriber is.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots WHERE slot_type = 'logical';
 
-- Subscriber: is the worker running, and is it erroring?
SELECT subname, pid, received_lsn, latest_end_lsn FROM pg_stat_subscription;
SELECT * FROM pg_stat_subscription_stats;

A subscription that hits a conflict — a duplicate key, a missing table — stops and retries the same transaction indefinitely. It will not skip ahead on its own; you must resolve the conflict on the subscriber or skip the transaction explicitly.

Major-version upgrade with logical replication

This is the pattern that gives a near-zero-downtime major upgrade:

1. Build a new cluster on the target version and create the schema.
2. Create publication and subscription; wait for the initial copy and for lag to reach near zero.
3. Verify row counts and checksums for the largest tables.
4. Stop application writes briefly.
5. Confirm lag is zero, advance all sequences, and switch the application to the new cluster.
6. Keep the old cluster intact until the new one has proven itself, and know how to fail back.

See Major Version Migrations.