Replication

Logical replication

Logical replication decodes the WAL into row-level changes (insert this row, update that key) and sends them for selected tables to subscribers, which apply them to their own, writable tables.

In plain words

Physical replication copies the whole cluster, block by block. Logical replication is choosier: you publish some tables on one server and subscribe to them on another. What travels is not disk blocks but row changes: "insert this row", "update the row with id 42".

Because the subscriber applies changes like ordinary SQL, it is a normal read-write database. It can have different indexes, extra tables, even a different major version, which is why logical replication is the usual tool for low-downtime upgrades and for feeding data to other systems.

The flip side: the two sides can disagree. If the subscriber already has a row with the same key, the change cannot be applied and replication stops until someone fixes it.

For DBAs and operators

Moving parts

Publisher needs wal_level = logical (restart). A walsender runs the pgoutput plugin on a logical slot named after the subscription.
Logical decoding reads WAL, reassembles each transaction in the reorder buffer (spilling to pg_replslot/ above logical_decoding_work_mem) and emits it at commit, in commit order. Large transactions can be streamed before commit; since PostgreSQL 18 new subscriptions default to streaming = parallel, applied by parallel apply workers.
On the subscriber the apply worker (one per subscription) applies changes; tablesync workers do the initial COPY per table, each with its own slot that is dropped when the sync is done, then hand over. Progress is tracked by a replication origin.

-- publisher
CREATE PUBLICATION pub FOR TABLE orders;
-- subscriber (table must already exist there)
CREATE SUBSCRIPTION sub CONNECTION 'host=pub dbname=app user=rep'
  PUBLICATION pub;

Replica identity

UPDATE and DELETE must say which row they mean. The table's REPLICA IDENTITY decides how the row is identified: DEFAULT (primary key), USING INDEX (a unique, non-null index), FULL (whole old row, expensive on the subscriber without a usable index) or NOTHING. Without one, the publisher refuses the statement itself:

ERROR: cannot update table "orders" because it does not have a replica identity and publishes updatesHINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

Conflicts

PostgreSQL 18 detects and names conflicts in the log and counts them in pg_stat_subscription_stats (confl_insert_exists, confl_update_exists, confl_update_missing, confl_delete_missing, confl_multiple_unique_conflicts, and the *_origin_differs kinds, which need track_commit_timestamp). Errors stop the apply worker, which restarts and retries the same transaction forever while the slot retains WAL on the publisher:

ERROR: conflict detected on relation "public.orders": conflict=insert_existsDETAIL: Key already exists in unique index "orders_pkey", modified in transaction 812.Key (id)=(7); existing local row (7, …); remote row (7, …).LOG: background worker "logical replication apply worker" (PID 4321) exited with exit code 1

Missing rows for UPDATE/DELETE are only logged (the change is skipped). Fix an error by correcting the local data, or skip the remote transaction with ALTER SUBSCRIPTION sub SKIP (lsn = '0/1A2B3C8') using the "finished at" LSN from the CONTEXT line. disable_on_error = true disables the subscription instead of retrying.

What is not replicated

DDL, sequence values and large objects (TRUNCATE is replicated: it is in the default publish list). Apply schema changes on the subscriber first. Since PostgreSQL 18 stored generated columns can be published (publish_generated_columns).

Failover slots (PostgreSQL 17+)

Create the subscription with failover = true and set sync_replication_slots = on on a physical standby (with hot_standby_feedback, primary_slot_name and a dbname in primary_conninfo): the slot is copied to the standby (synced = true in pg_replication_slots), so after promotion the subscriber can continue. synchronized_standby_slots on the primary makes walsenders wait so the logical consumer never gets ahead of that standby.

SELECT subname, apply_error_count, sync_error_count,
       confl_insert_exists, confl_update_missing
  FROM pg_stat_subscription_stats;
SELECT * FROM pg_stat_subscription;   -- per worker LSNs

For developers: inside the source

Decoding itself (ReorderBuffer, snapshot builder) is described on logical decoding; this is the publication/subscription layer on top.

Publisher: pgoutput

pgoutput.c: _PG_output_plugin_init() registers the callbacks; pgoutput_startup() parses proto_version, publication_names, streaming, binary, origin. get_rel_sync_entry() caches a RelationSyncEntry per table (published actions, row filter, column list), invalidated by relcache/syscache callbacks. pgoutput_change() evaluates pgoutput_row_filter(), sends a Relation message first via maybe_send_schema(), then writes the change with logicalrep_write_insert() etc. from proto.c.
The "does not have a replica identity" error comes from CheckCmdReplicaIdentity() in execReplication.c, called from CheckValidResultRel() in execMain.c when the publisher's executor opens a published table for UPDATE/DELETE.

Subscriber: workers

ApplyLauncherMain() scans pg_subscription and starts workers with logicalrep_worker_launch(); each occupies a LogicalRepWorker slot (worker_internal.h) of type WORKERTYPE_APPLY, WORKERTYPE_TABLESYNC or WORKERTYPE_PARALLEL_APPLY.
worker.c: ApplyWorkerMain() → run_apply_worker() → start_apply() → LogicalRepApplyLoop(), which feeds each message to apply_dispatch() (apply_handle_begin(), apply_handle_insert(), apply_handle_update(), apply_handle_commit(), apply_handle_stream_start() …). Inserts go through ExecSimpleRelationInsert(); UPDATE/DELETE locate the local row with FindReplTupleInLocalRel() → RelationFindReplTupleByIndex(), or RelationFindReplTupleSeq() when no usable index exists. Workers run with session_replication_role = replica. send_feedback() reports write/flush/apply LSNs; get_flush_position() reports a remote transaction as flushed only once its local commit record is flushed, so the slot never advances past data the subscriber could lose.
tablesync.c: TablesyncWorkerMain() → LogicalRepSyncTableStart() → copy_table(). Per-table state lives in pg_subscription_rel.srsubstate: i init → d datasync → f finished copy → s syncdone → r ready (w/c exist only in shared memory during the handover in process_syncing_tables()).
origin.c: replorigin_session_setup()/replorigin_advance() persist progress (pg_replication_origin_status).
conflict.c: ReportApplyConflict() formats the conflict=… messages from the ConflictType enum (CT_INSERT_EXISTS … CT_MULTIPLE_UNIQUE_CONFLICTS) and bumps pg_stat_subscription_stats via pgstat_report_subscription_conflict().
applyparallelworker.c: streamed transactions go to a parallel apply worker (pa_allocate_worker(), ParallelApplyWorkerMain()) through a shared-memory queue; if the queue stays full the leader switches to spooling to a file (pa_switch_to_partial_serialize()). The file header comment explains the deadlock handling.

Experiment

TAP suite src/test/subscription/t/: 001_rep_changes.pl, 004_sync.pl, 015_stream.pl, 028_row_filter.pl, 035_conflicts.pl.
Commands are in commands/publicationcmds.c (CreatePublication()) and commands/subscriptioncmds.c (CreateSubscription(), which connects through the walreceiver API to create the remote slot).

Parameters that shape this

wal_level max_replication_slots max_wal_senders max_logical_replication_workers max_sync_workers_per_subscription max_parallel_apply_workers_per_subscription logical_decoding_work_mem max_active_replication_origins track_commit_timestamp sync_replication_slots synchronized_standby_slots