postgres-impl-logical-replication
postgres-impl-logical-replication
Quick Reference :
Logical replication ships individual row changes (not raw WAL blocks) from a publisher to a subscriber by decoding the WAL. It is selective per table, works across major versions and platforms, and is the basis for zero-downtime upgrades and selective data distribution. The publisher needs wal_level = logical. A publication (CREATE PUBLICATION) names a set of tables and which DML to send. A subscription (CREATE SUBSCRIPTION) on the other database connects, creates a replication slot on the publisher, and begins applying changes ; by default it also copies the existing rows first (copy_data = true).
Two facts cause most production incidents. First : REPLICA IDENTITY. To replicate UPDATE and DELETE, the subscriber must be able to locate the old row. With the default REPLICA IDENTITY DEFAULT and NO primary key, UPDATE/DELETE are silently NOT replicated (and on the publisher, raise an error when the publication includes those operations). A table without a PK needs REPLICA IDENTITY FULL or USING INDEX. Second : a replication slot retains WAL on the publisher until its subscriber consumes it. A subscription that is dropped on the subscriber side without dropping the slot, or a subscriber that stays offline, makes the publisher accumulate WAL without bound until the disk fills.
Two things logical replication does NOT do. DDL is not replicated : every schema change (ALTER TABLE, new columns, type changes) must be applied by hand on BOTH sides, publisher-side first for additive changes. Sequences are not replicated : a serial/identity column's values arrive as data, but the sequence object on the subscriber stays at its start value, which matters at failover. Version features : row filters, column lists, and FOR TABLES IN SCHEMA arrived in v15 ; logical decoding from a standby, bidirectional replication, and parallel apply in v16 ; pg_createsubscriber and failover slots in v17.