Logical replication

The half of replication that is not a byte-for-byte copy, defined by the PostgreSQL documentation against the half that is (T1):

"Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication."

That single contrast carries most of the consequences. Physical replication ships WAL records describing changes to disk blocks, so a standby is an exact copy — same version, same platform, whole cluster, read-only. Logical replication decodes the same WAL into row changes identified by primary key, so the receiving side can be a different major version, a different platform, a subset of tables, or a database that is also accepting its own writes.

Publish and subscribe

"Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations."

Startup is a snapshot followed by a stream: "PostgreSQL takes a snapshot of the table's data on the publisher database and copies it to the subscriber. Once complete, changes on the publisher since the initial copy are sent continually to the subscriber. The subscriber applies the data in the same order as the publisher so that transactional consistency is guaranteed for publications within a single subscription."

Note the scope of that guarantee — within a single subscription. Two subscriptions are two independent streams with no ordering between them, which is the kind of detail that decides whether a consolidation setup is correct.

The documented use cases are worth listing because they are not what physical replication is for: sending incremental changes for a subset of a database, firing triggers on the subscriber, consolidating several databases into one for analytics, replicating between different PostgreSQL major versions, replicating across platforms, and exposing replicated data to a different set of users.

Major-version replication is the one with operational weight. A physical standby cannot span a major upgrade, so the near-zero-downtime upgrade path for a large cluster is: logically replicate into a new-version instance, wait for it to catch up, switch traffic.

Where it sits among this spoke's pages

The corpus reaches this mechanism from three directions at once, which is why it needed its own page.

• patroni runs physical streaming replication and nothing else — its maximum_lag_on_failover is a byte count of WAL, and its standbys are exact copies precisely so one can be promoted. Logical replication cannot serve that job.
• citus and the sharded stores use the physical kind per group, per the same reasoning.
• The catalog's High-Availability section holds Spock ("logical multi-master replication"), BDR ("BiDirectional Replication multimaster"), pglogical and pgcat, all of which build on this decoding to allow two nodes that both take writes. That is the direction physical replication structurally cannot go, and it buys conflict resolution as a new problem — the single-engine version of the merge-semantics question event-sourcing and local-first-architecture argue about at the client.
• ferretdb-style translation layers and every change-data-capture pipeline read the same decoded stream. turso's CDC feature is the embedded-engine analogue.

The restrictions, honestly

The documentation places the restrictions in a section this ingest did not read in full; it names DDL, sequences, TRUNCATE and large objects as the areas covered. Schema changes are not replicated is the one that shapes real deployments — a migration must be applied to both sides, in a compatible order, by something outside the replication system. That is the seam every schema-migration tool in awesome-postgres' Utilities section has to work with, and this page does not yet have a source for how any of them do it.

Also open: replication slots and the disk they can consume when a subscriber falls behind, conflict handling on the subscriber, and the performance cost of logical decoding against physical shipping.

Related

write-ahead-logging · postgresql · patroni · citus · ferretdb · turso · event-sourcing · local-first-architecture · awesome-postgres · operational-databases