Logical Replication Basics

Logical replication decodes WAL into row-level changes and applies them on a subscriber. Unlike physical streaming, it can replicate selected tables across databases or major versions.

Recipe

Publisher on PostgreSQL 18.4 primary; subscriber on same or newer major.

-- Publisher
ALTER SYSTEM SET wal_level = logical;
SELECT pg_reload_conf();  -- restart if wal_level was not logical
 
CREATE PUBLICATION app_pub FOR TABLE orders, order_items;

-- Subscriber
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=primary.db.internal dbname=appdb user=repl_user password=secret'
  PUBLICATION app_pub
  WITH (copy_data = true, create_slot = true);

When to reach for this: Major version upgrades, selective table sync, or feeding analytics stores without cloning the full cluster.

Working Example

End-to-end logical replication with status checks:

-- Publisher: ensure primary key or REPLICA IDENTITY on replicated tables
ALTER TABLE orders REPLICA IDENTITY FULL;  -- if no PK on legacy table
 
CREATE PUBLICATION orders_pub FOR TABLE orders;

-- Subscriber
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=primary.db.internal port=5432 dbname=appdb user=repl_user password=secret sslmode=verify-full'
  PUBLICATION orders_pub;

-- Monitor on publisher
SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;  -- empty on publisher
 
SELECT slot_name, plugin, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';
 
-- Monitor on subscriber
SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

What this demonstrates:

• wal_level = logical enables logical decoding on the publisher.
• Publications define which tables (and optionally rows/columns) replicate.
• Subscriptions create a logical slot on the publisher and apply changes locally.

Deep Dive

Components

What Replicates

• DML on published tables: INSERT, UPDATE, DELETE, TRUNCATE (PostgreSQL 18 supports truncate in publications).
• DDL does not replicate automatically.
• Sequences require manual sync or PostgreSQL 18+ sequence publishing options.

Initial Sync

copy_data = true (default) runs CREATE TABLE AS or parallel copy on subscribe. Large tables need maintenance window or copy_data = false with manual seed.

Gotchas

• No primary key and default REPLICA IDENTITY - Updates/deletes may not replicate correctly. Fix: Add PK or REPLICA IDENTITY FULL (wider WAL).
• DDL on publisher only - Subscriber schema drifts. Fix: Expand-contract migrations on both sides or use migration tooling.
• Logical slot bloat when subscriber down - Publisher disk fills. Fix: Alert on inactive logical slots; set retention policy.
• wal_level change without restart - Logical decoding unavailable. Fix: Restart PostgreSQL after raising wal_level.
• Conflicting writes on subscriber - Apply worker stops on conflict. Fix: Subscriber read-only for same tables, or use conflict handlers (version-dependent).

Alternatives

FAQs

Related

• Publications & Subscriptions - filters and column lists
• Logical vs Physical - decision guide
• Upgrade with Logical Replication - blue/green major upgrade
• Replication Slots - logical slot disk risk

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.