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.
//
Search across all documentation pages
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.
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.
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.| Object | Runs on | Purpose |
|---|---|---|
| Publication | Publisher | Table/column/row filter for changes |
| Subscription | Subscriber | Connection + apply workers |
| Logical slot | Publisher | Retains WAL until subscriber confirms |
| Apply worker | Subscriber | Inserts/updates/deletes rows |
INSERT, UPDATE, DELETE, TRUNCATE (PostgreSQL 18 supports truncate in publications).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.
REPLICA IDENTITY FULL (wider WAL).wal_level change without restart - Logical decoding unavailable. Fix: Restart PostgreSQL after raising wal_level.| Alternative | Use When | Don't Use When |
|---|---|---|
| Physical streaming | Full cluster HA, lowest overhead | Cross-major-version cutover |
| ETL / CDC (Debezium) | Transform in flight | Simple table mirror suffices |
| Foreign Data Wrapper | Occasional cross-DB reads | Need continuous near-real-time sync |
Subscriber must be same or newer major than publisher for logical replication.
Yes, but avoid writes to published tables unless you handle conflicts deliberately.
No. It mirrors row changes; it does not replace base backups and WAL archive for PITR.
Multiple subscribers can use the same publication; each creates its own logical slot.
Run logical publisher on Patroni primary; after failover, slots move with primary. Verify slot sync settings in Patroni 3.x.
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+.
Reviewed by Chris St. John·Last updated Jul 19, 2026