Logical replication in PostgreSQL: publications, subscriptions and how it differs from streaming replication

What it is

Physical (streaming) replication sends write-ahead log records to a standby that replays them block by block; the standby documentation states that the hardware architecture must be the same and that log shipping between different major releases is in general not possible. Logical replication, per the cited chapter, replicates data objects and their changes based on their replication identity (usually a primary key) in a publish-and-subscribe model: a publisher defines a publication over tables (optionally limited to INSERT, UPDATE, DELETE or TRUNCATE, with row filters and column lists), a subscriber creates a subscription, an initial snapshot copies the existing rows, and later changes are applied in commit order, which the documentation calls transactional replication. Each subscription consumes changes through a replication slot on the publisher.

Why it matters

A streaming standby is a complete copy for failover and read scaling; it cannot be written to and cannot be selective. Logical replication carries a subset of tables to a database that may be a different major version, a different platform, a consolidation target for analytics, or a subscriber that also holds its own tables. The chapter's use-case list includes replicating between major versions, which makes an upgrade with seconds of switchover possible.

How to apply

• Give every published table a primary key; a table without a suitable key needs REPLICA IDENTITY FULL, which the documentation describes as a fallback with inefficient row lookups on the subscriber.
• Copy the schema by hand first (pg_dump --schema-only): the restrictions page states that the database schema and DDL commands are not replicated, and recommends applying additive schema changes to the subscriber first.
• Plan for sequences: sequence data is not replicated, so before a switchover set the subscriber's sequences beyond the publisher's current values.
• Watch the slot. A subscriber that is down keeps its slot, and the slot keeps WAL on the publisher until the disk fills; max_slot_wal_keep_size bounds the retention at the price of invalidating a slot that falls too far behind.
• Treat replicated tables as read-only on the subscriber. The conflicts page states that incoming changes are applied even if the row was changed locally, and that incoming data violating a constraint (a local row with the same unique key, say) stops replication until resolved by hand.

Pitfalls

Large objects are not replicated, and views, materialized views and foreign tables cannot be published. A schema change on the publisher that the subscriber's table cannot accept stops replication until the subscriber is fixed. Logical replication is not a backup: a DELETE or TRUNCATE on the publisher is applied faithfully.