Logical Replication

Physical replication ships bytes. Logical replication ships rows. A publication on the source names a set of tables; a subscription on the target takes an initial snapshot of each one and then applies the inserts, updates and deletes as they happen. The same WAL is the transport in both cases, but here it is decoded back into row changes before it goes on the wire, which is what makes everything else in this topic possible.

Rows instead of bytes buys three things physical replication cannot offer: the target may run a different major version, it may hold only some of the tables, and it is a fully writable database rather than a read-only mirror. That combination makes logical replication the tool for near-zero-downtime major upgrades, selective analytics feeds and change data capture, and, for reasons that become obvious three sections down, a poor foundation for high availability.

Publications and Subscriptions

The prerequisite is wal_level = logical, which adds the information the decoder needs and requires a restart, so it belongs in the initial configuration rather than in the middle of a project. After that the setup is two statements: one on the publisher naming what to send, one on the subscriber naming where to get it. A publication can list tables explicitly, take every table in a schema with FOR TABLES IN SCHEMA, or take the whole database with FOR ALL TABLES, and by default it publishes all four of INSERT, UPDATE, DELETE and TRUNCATE.

-- on pg-primary
CREATE PUBLICATION cartwheel_orders FOR TABLE orders, order_items;

-- on the analytics cluster, where both tables already exist
CREATE SUBSCRIPTION cartwheel_orders_sub
  CONNECTION 'host=pg-primary dbname=cartwheel user=replicator'
  PUBLICATION cartwheel_orders;

-- back on pg-primary: what CREATE SUBSCRIPTION built there
SELECT slot_name, plugin, slot_type, database FROM pg_replication_slots;

      slot_name       |  plugin  | slot_type | database
----------------------+----------+-----------+-----------
 cartwheel_orders_sub | pgoutput | logical   | cartwheel

The second statement does four things and only one of them is visible in its own text. It creates a logical replication slot on the publisher, named after the subscription and using the built-in pgoutput plugin. It copies the existing contents of every published table, because copy_data defaults to true. It enables itself. Then it streams. What it does not do is create the tables: the target schema must already be there, in a compatible shape, put there by hand or by pg_dump --schema-only. Since 18 the streaming option defaults to parallel, so a large in-progress transaction is applied by parallel workers as it arrives instead of being buffered until commit. The slot is the object to remember: it lives on the publisher, it is created by a statement run on a different machine, and it retains WAL there for as long as it exists.

Replica Identity

When an update arrives, the subscriber has to find the row it refers to. Replica identity is what decides which columns identify a row for that purpose, and its default is the table's primary key. A published table with no primary key and no explicit replica identity cannot support UPDATE or DELETE in a publication that replicates them, and the failure lands somewhere useful: attempting one of those operations raises an error on the publisher. So the first thing that breaks in practice is a production write failing on the source, rather than a divergence on the target that nothing reports.

There are three fixes and they are not equivalent. Give the table a primary key, which is the correct answer for almost every table in a schema like Cartwheel's. Point the identity at a unique index on non-null columns with ALTER TABLE … REPLICA IDENTITY USING INDEX. Or set REPLICA IDENTITY FULL, which writes the entire old row into WAL for every update and delete and makes the subscriber match on the whole row. That is expensive on both machines, and it has a sharp edge: with FULL, updates and deletes cannot be applied on the subscriber if the table has a column of a type with no default B-tree or hash operator class, such as point or box.

What Is Not Replicated

The list is short, specific, and each item has caused an outage somewhere. DDL is not replicated, so every schema change has to be applied to both sides, in an order you choose deliberately. Sequence data is not replicated: the values in an identity column arrive as ordinary column data, while the sequence object on the subscriber still sits wherever it started, which is how a cutover ends with a new primary handing out orders.id values that already exist. Large objects are not replicated at all. Only tables can be published: a view, a materialized view or a foreign table raises an error. TRUNCATE is replicated, but applying it fails on the subscriber when a truncated table has foreign-key links to tables outside the subscription. And a partitioned table like delivery_events replicates from its leaf partitions by default, so every monthly partition must exist on the target as a valid destination.

The second thing that breaks in practice is a property rather than an omission: the subscriber is a live, writable database. Nothing prevents a person, a cron job or a misconfigured application from writing to a replicated table there, and the first change from the publisher that collides with such a write stops replication until a person resolves it. Physical replicas refuse writes for you; here the discipline comes from role grants, which is a materially weaker guarantee. Since 18 the values of generated columns can also be published, controlled by the publication's publish_generated_columns option, which closes one of the older gaps in what crosses the wire.

The Major-Version Upgrade Path

Because rows are version-independent in a way that pages are not, a logical subscriber can run a newer major version than its publisher, and that single fact turns a major upgrade from a maintenance window into a cutover measured in seconds. Build the new cluster, subscribe it to the old one, let it catch up, stop writes, wait for the last changes to arrive, advance the sequences by hand because nothing else will, and repoint the application. pg_createsubscriber, added in 17, shortens the expensive part: it converts an existing physical standby into a logical subscriber, so the initial copy of 700 GB never happens at all, and 18 added --all, --clean and --enable-two-phase to it. Cartwheel's own move from 17 to 18 is walked step by step in Chapter 14; what belongs here is only the reason the option exists.

Selective and Transformed Feeds

The everyday use is narrower than an upgrade and more common. One schema fed to an analytics cluster. One tenant's tables copied into a separate database. A feed that carries the columns a downstream team is allowed to see and no others. Row filters and column lists, both available since 15, do the narrowing on the publisher, so the excluded data never crosses the network in the first place.

CREATE PUBLICATION analytics_orders
  FOR TABLE orders (id, public_id, placed_at, status, total)
             WHERE (placed_at >= '2026-01-01'),
      TABLE order_items;

The column list drops customer_id, so the analytics cluster physically cannot join an order back to a person. The row filter keeps the copy to the current year rather than all 40 million rows. Two rules govern both. A column list on a publication that replicates UPDATE or DELETE must contain the replica identity columns, so orders.id leads the list, and the same table cannot appear in two publications of one subscription with different column lists, a situation CREATE SUBSCRIPTION refuses but a later ALTER PUBLICATION can still create. The initial copy respects both the filter and the list, so the target starts narrow rather than starting wide and narrowing later. This is also the layer underneath change data capture: Debezium and its relatives consume the logical decoding stream directly instead of running a Postgres subscriber at the other end.

The Operational Weight

Every subscription owns a replication slot on the publisher, with exactly the failure mode the physical slot had two topics ago and a larger surface, because a subscription stalls on an ordinary Tuesday rather than only on a decommissioning that was never cleaned up. Monitoring has to cover both ends. On the publisher, pg_replication_slots gives restart_lsn, confirmed_flush_lsn and the wal_status that says how close the retained WAL is to being a problem; pg_stat_replication_slots adds the decoding work, including how much has spilled to disk past logical_decoding_work_mem. On the subscriber, pg_stat_subscription gives received_lsn, latest_end_lsn, the worker type and the time of the last message, which on a healthy subscription is never more than a few seconds old.

Conflicts are the other standing cost. A duplicate key on the subscriber raises an error naming the conflict type and the transaction's finish LSN, and replication stays stopped until a human resolves it: fix the data, skip the transaction with ALTER SUBSCRIPTION … SKIP, or advance the origin with pg_replication_origin_advance(). Setting disable_on_error turns an apply worker that would otherwise retry forever into a disabled subscription, which is quieter in the log and no less in need of a person. Since 18 the conflicts are logged and counted in pg_stat_subscription_stats under names like insert_exists and update_missing, so they can be alerted on rather than discovered. And 17's failover slots, meaning failover = true on the subscription plus sync_replication_slots on the standby, let a logical slot survive a physical failover of the publisher. Before that, promoting a standby orphaned every subscription pointing at the old primary.