PostgreSQL Logical Replication

Logical vs physical: physical replication ships raw WAL and replicates the entire cluster, requiring identical major versions. Logical replication uses logical decoding to turn WAL into INSERT/UPDATE/DELETE row events scoped to a publication, enabling selective, table-level, cross-version replication and even replicating into a database with a different schema arrangement.

PUBLICATION: CREATE PUBLICATION mypub FOR TABLE orders, customers; (or FOR ALL TABLES) defines which tables' changes are streamed. You can restrict to specific operations and, in newer versions, add row filters and column lists. The publisher emits changes for exactly the published tables.

SUBSCRIPTION: CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub; on the subscriber connects to the publisher, performs an initial data copy, then streams ongoing changes. It relies on a replication slot on the publisher to retain WAL until the subscriber confirms receipt, ensuring no changes are missed.

Replication slots: a slot durably records the last WAL position a subscriber has confirmed, so the publisher retains needed WAL even across restarts. The caveat: if a subscriber is down or lagging, its slot holds WAL indefinitely, which can exhaust disk on the publisher — monitoring slot lag is essential operationally.

Limitations: classic logical replication propagates row changes (DML) but does not automatically replicate DDL such as ALTER TABLE, and sequence values were not replicated in older versions. As a result, schema migrations must be applied on publisher and subscriber in a coordinated way; otherwise replication can break when columns no longer match.