Understand PUBLICATION, SUBSCRIPTION, replication slots, logical vs physical replication, and DDL limitations
0 / 5 completed
1 / 5
How does logical replication in PostgreSQL differ from physical (streaming) 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.
2 / 5
What is a PUBLICATION in PostgreSQL logical replication?
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.
3 / 5
What does a SUBSCRIPTION do?
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.
4 / 5
Why does logical replication require a replication slot?
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.
5 / 5
What is a common limitation of PostgreSQL logical replication to be aware of?
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.