PostgreSQL logical replication and change feeds

From WAL to a changed row

The publisher is the database supplying the changes. A WAL sender reads its write-ahead log through logical decoding, which reconstructs table changes from the storage records. The built-in output plugin, pgoutput, selects the published data and encodes the row-change protocol. An apply worker in the subscriber maps those changes to local tables.

Start with P7 at 20.00 on both sides. The publisher commits 22.00 before the subscriber’s apply transaction commits that change locally. Pausing apply can prolong the interval between those two outcomes without undoing the publisher’s commit.

Only after local apply commits can a fresh target snapshot return 22.00. An older Repeatable Read reader can keep seeing 20.00 even after apply catches up. A message received, a transaction applied and a price visible to this reader are three different observations.

The subscriber stores its own tuples and maintains its own indexes. It can use a different physical layout or PostgreSQL major version because P7 identifies the row, rather than a publisher heap address such as ctid. PostgreSQL physical replicas and promotion follows the different contract of a standby replaying the publisher’s storage history. A products-only logical copy is neither that standby nor a backup of the shop.

Publications, subscriptions and slots

Three objects make the copy work. A publication on the publisher chooses changes from tables in one database. A subscription on the subscriber records the publisher connection and the publications to consume. A logical replication slot on the publisher keeps the restart state and the history needed to continue that consumer.

The publication describes the selection; it does not hold a private queue containing only those rows. The slot keeps requirements against the publisher’s WAL and catalogue history. Each independent consumer normally needs its own slot, so one slow consumer can retain history after another has caught up.

Once selected tables are synchronised, one subscription preserves the publisher’s transaction order for its selected changes. An order and its lines split across unrelated subscriptions do not acquire a shared target commit boundary. Data left out of a publication cannot be made consistent merely by waiting longer.

Replica identity

An insert brings a new row. An update or delete must also say which earlier row it changes. PostgreSQL calls the identifying old-row information the table’s replica identity. By default it uses the primary key.

CREATE TABLE public.products (
  product_id text PRIMARY KEY,
  name text NOT NULL,
  price numeric(8,2) NOT NULL CHECK (price >= 0),
  stock integer NOT NULL CHECK (stock >= 0)
);

The price can change while product_id = 'P7' still finds the target. If an update changes the key itself, the subscriber needs the earlier key to find the row before assigning the new one. Identifying the row by its new price would not do that job.

A table can instead select a qualifying unique index with ALTER TABLE … REPLICA IDENTITY USING INDEX …. The index must be unique, non-partial, non-deferrable and use only columns marked NOT NULL. Choose an identity whose target equivalent can find the same row; for an identity other than FULL, the subscriber needs an identity using the same or fewer columns.

A table without a suitable identity can still publish inserts. Publishing its updates or deletes is different: attempting one can fail on the publisher, even before a subscriber receives anything. In an isolated PostgreSQL 18.6 check, updating a published keyless P7 row was refused; adding its primary key allowed the update.

REPLICA IDENTITY FULL uses the entire old row as a fallback. That sends more old-row information, and finding it on the target can be expensive without a suitable supporting B-tree or hash index. Types without the required default operator classes add restrictions. It is a deliberate fallback for a table that cannot have a useful key, rather than a general cure for a stopped subscription.

Create a publication and subscription

The ordinary path starts with a populated publisher and an empty, compatible target table. Run this setup on two separate PostgreSQL 18 instances: database shop on the publisher and catalogue on the subscriber. The connection host and authentication are deployment inputs; run each command on the named side after verifying the connection.

Prepare both databases

On the publisher, set wal_level = logical and restart if changing it. Allow enough max_replication_slots for the subscriptions and temporary table synchronisation slots, and enough max_wal_senders for them plus physical standbys. On the subscriber, budget max_active_replication_origins, max_logical_replication_workers and max_worker_processes for apply and synchronisation. max_sync_workers_per_subscription controls concurrent table copies. These are shared capacity budgets; a limit sufficient for one steady worker may prevent its initial copy.

The publisher connection role needs LOGIN, REPLICATION, permission to connect through pg_hba.conf and SELECT for the initial copy. PostgreSQL 18 also requires pgoutput in output_plugin_libraries. Creating the publication needs database CREATE and ownership rights on the named table. Creating the subscription needs database CREATE and the privileges of pg_create_subscription. Under the default apply model, its owner must be able to SET ROLE to the target table owner.

Keep these roles and the connection credentials deliberate. There are no per-publication access privileges, and a column list is not a security boundary against a malicious subscriber. Other publications can expose more data. Publication selection defines the copy’s behaviour; database access must establish who is trusted to consume it.

Create public.products from the definition above on both sides. On the publisher only, load the three starting rows and create the publication:

INSERT INTO public.products VALUES
  ('P3', 'Green tea, 50 g', 4.50, 10),
  ('P7', 'Blue mug',       20.00, 0),
  ('P9', 'Teapot',         35.00, 4);

CREATE PUBLICATION catalogue
  FOR TABLE public.products;

On the subscriber, leave the table empty and create the subscription. Replace the example connection information with the verified publisher and feed-role password. A subscription owned by a non-superuser requires password authentication by default.

CREATE SUBSCRIPTION catalogue_copy
  CONNECTION 'host=publisher.example dbname=shop
              user=catalogue_feed password=REPLACE_WITH_FEED_PASSWORD'
  PUBLICATION catalogue
  WITH (streaming = off);

The normal defaults create a slot named catalogue_copy, enable the subscription and copy existing rows. Here streaming = off keeps in-progress transactions on the publisher until fully decoded, making the small example easier to follow. PostgreSQL 18 otherwise defaults to parallel streaming; it still preserves transaction visibility.

Copy existing rows, then follow changes

The success of CREATE SUBSCRIPTION means the definition was created, not that all rows arrived. Each table synchronisation worker takes a consistent snapshot, copies its table and catches up with changes since that snapshot. A price update made during that interval belongs in the following changes, so it is not lost merely because the earlier snapshot copied 20.00.

The snapshot and the following stream fit together. An arbitrary dump followed by a newly created slot does not provide that boundary: a change after the dump’s snapshot but before the slot starts can be absent from both. copy_data = false merely skips the copy; it does not prove a preloaded target is aligned.

On the subscriber, check every selected table’s state:

SELECT r.srrelid::regclass AS table_name, r.srsubstate
FROM pg_subscription_rel AS r
JOIN pg_subscription AS s ON s.oid = r.srsubid
WHERE s.subname = 'catalogue_copy';

r means the table is ready for normal replication. Other states describe work still being prepared, copied or synchronised; visible rows alone are insufficient. Once products is ready, update P7 on the publisher:

UPDATE public.products
SET price = 22.00
WHERE product_id = 'P7';

Then use a fresh subscriber query:

SELECT product_id, price
FROM public.products
WHERE product_id = 'P7';

The isolated PostgreSQL 18.6 run copied all three rows and reached r; the later subscriber query reached 22.00. This establishes the small example’s copy and update, not a catch-up time for another workload.

Choose which changes to publish

The full products copy includes stock. A catalogue may only need product identity, name and price. For a separate copy prepared with that target schema, a publication can select those columns:

CREATE PUBLICATION catalogue_projection
  FOR TABLE public.products (product_id, name, price);

The initial copy follows this column list too. Updates and deletes require the identity columns in the list, so P7 remains available to identify the target. With no list, newly added publisher columns become published automatically; explicitly naming today’s columns creates a different future contract.

Operations are a separate choice. WITH (publish = 'insert, update, delete') excludes later truncates. It does not restrict which existing rows the initial copy includes. TRUNCATE is a table operation, unaffected by row filters or column lists, rather than many filtered deletes.

Row filters and changing membership

A row filter selects rows using a publication WHERE clause. For updates and deletes, its columns must be covered by replica identity. A price filter therefore cannot simply be attached to our P7-only identity. Consider an independent full-row copy with REPLICA IDENTITY FULL and filter WHERE (price >= 21.00). This selection is separate from the working catalogue subscription.

For that filtered copy, changing P7 from 20.00 to 22.00 inserts it on the subscriber: it has entered the selected set. A separate change from 22.00 to 20.00 removes it with a delete. When both old and new rows match, replication sends an update; when neither matches, it sends no row change. The subscriber maintains the selected set, so a publisher update need not arrive as an update.

In PostgreSQL 18 the initial copy also applies the filter. If several subscribed publications filter the same table for an operation, their filters combine with OR; one unfiltered publication can include everything.

Changing a filter does not rebuild rows already copied, even after a publication refresh. Expanding a price range can leave newly eligible, unchanged rows missing; narrowing it can leave old target rows behind. Plan reconciliation or resynchronisation for the new selection.

To add another table, first prepare its compatible target definition, add it to the publication on the publisher, then run ALTER SUBSCRIPTION catalogue_copy REFRESH PUBLICATION on the subscriber. The normal refresh copies newly subscribed tables; check each new table reaches r. Adding publication membership alone does not complete a copy.

Keep the subscriber compatible

Tables match by schema and table name; columns match by name. The target need not have identical column order, indexes or types, provided the transferred values can be converted. The ordinary text transfer permits more type differences than binary transfer. Target-only columns need suitable defaults or nullability for incoming inserts.

Schema definitions and DDL are not replicated. If the publisher begins sending a new column before the target can accept it, apply stops. For a compatible additive change, prepare the target column first, then change the publisher. That order prevents this particular mismatch; it is not permission to reorder arbitrary schema changes. PostgreSQL schema changes under load handles the wider lock and compatibility decisions.

Other state also stays outside the row stream. Values supplied for serial or identity columns can arrive, but their sequences do not advance with those copied values. Object definitions, roles and large objects need separate treatment. Our text-keyed products example creates no order sequence. Before a target becomes a writer, sequence preparation is part of the migration’s full inventory.

Constraints, triggers and local writes

Apply runs with session_replication_role = replica. Ordinary triggers do not fire by default, including the triggers normally enforcing foreign keys. Replica-enabled or always-enabled triggers can run; ordinary application behaviour is not automatically replayed. Initial copying has COPY-like trigger behaviour and differs from ongoing row apply.

Other target constraints can stop incoming changes: a duplicate primary key or failed check is enough. Local application writes to replicated tables can also be overwritten by later source updates, or leave a target row missing when an update arrives. A missing update or delete target is counted as a conflict but skipped rather than treated as a stopping error. A running worker therefore does not establish equal data.

Keep one application write authority for these tables. If truncating related tables, keep the affected relationship within one subscription: a target foreign key to a table outside that subscription can make the truncate fail. Splitting tables is a consistency decision as well as a transport decision.

Replication progress and retained WAL

A stale P7 price can mean initial copying, a disconnected worker, a stopped apply transaction or a reader’s old snapshot. Start by separating them. On the subscriber, combine table readiness with worker and error evidence:

SELECT subname, worker_type, pid, relid,
       received_lsn, latest_end_lsn,
       last_msg_receipt_time
FROM pg_stat_subscription
WHERE subname = 'catalogue_copy';

SELECT subname, apply_error_count, sync_error_count
FROM pg_stat_subscription_stats
WHERE subname = 'catalogue_copy';

The statistics view identifies apply, parallel apply and table synchronisation workers. received_lsn is receipt evidence; latest_end_lsn is the last position reported to the upstream sender. Neither alone proves which price a reader sees. Inspect the subscriber log for an error, inspect pg_replication_origin_status for tracked source apply progress, and make a fresh target read. Message receipt times can show silence, but an idle source with no business changes makes them a poor freshness measure.

On the publisher, inspect the exact subscription slot:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
         AS wal_distance_bytes,
       wal_status, safe_wal_size, inactive_since,
       invalidation_reason, catalog_xmin
FROM pg_replication_slots
WHERE slot_name = 'catalogue_copy';

confirmed_flush_lsn records how far this logical consumer has confirmed receipt. restart_lsn is the oldest WAL position it may still require. It can remain earlier because decoding needs more history than the latest acknowledged transaction alone. Confirmed receipt therefore does not release every earlier byte the decoder may need.

An inactive slot can retain WAL and catalogue rows while no consumer is connected. Disabling our subscription left P7 at 22.00 while the source changed back to 20.00; the native run showed its inactive slot still retaining a restart position. Re-enabling it applied the pending price. The experiment bounds the state transition, not how long a production publisher could safely retain history.

Monitor the WAL distance alongside free storage, wal_status, inactivity, safe_wal_size and invalidation reason. A byte distance is neither feed payload size nor catch-up time. max_slot_wal_keep_size can limit slot retention at checkpoints, but required WAL can then be removed and the slot become unusable. PostgreSQL 18’s idle_replication_slot_timeout can invalidate idle slots at a checkpoint too. A null safe_wal_size can mean unlimited configured retention or a lost slot; read it with the other fields.

A retention limit protects publisher resources by accepting a possible loss of resumability. It needs a reinitialisation plan, not just an alert when the limit is reached.

Repair a stopped subscription

Find the failed transaction and its reason before changing progress. A missing target column calls for compatible schema. A permission failure calls for the correct apply identity. A duplicate target key calls for a decision about the conflicting data, rather than repeated worker restarts.

In a separate native counterexample, we paused apply and inserted P11, Tea strainer at 8.00 with stock 0, locally on the subscriber. Inserting P11 on the publisher then stopped apply at the incoming insert: the target already had that key. After pausing, removing the deliberately introduced target conflict and re-enabling, the incoming source row applied. This is the reason to keep local writers off the copy; removing a real conflicting row requires deciding which facts to preserve.

The subscriber log identifies the relation, operation and remote transaction; pg_stat_subscription_stats records errors and conflict counts. Check those together with the source transaction’s intended changes. After repair, verify the worker advances and the affected data matches. Since missing-row conflicts can be skipped, include data reconciliation rather than treating the absence of an error as proof.

ALTER SUBSCRIPTION … SKIP can omit a whole remote transaction using its finish LSN. It omits its other changes too, including changes that caused no conflict. Skipping is a decision to replace or forgo those facts, not a routine restart technique. If the slot is lost or the original target alignment is unknown, create a new coordinated copy and reconcile it. A replacement slot starts new history; it cannot recreate changes no longer available.

Pause and retire deliberately

ALTER SUBSCRIPTION catalogue_copy DISABLE stops workers but leaves the subscription and its remote slot. If the consumer is being retired, normal DROP SUBSCRIPTION catalogue_copy contacts the publisher and drops the associated slot. Verify that both the local subscription and the publisher’s main and table-sync slots are gone.

If the publisher is unreachable, disable the subscription and disassociate its slot with ALTER SUBSCRIPTION catalogue_copy SET (slot_name = NONE) before dropping locally. That permits local removal but leaves remote cleanup to do when the publisher returns. A forgotten slot can continue retaining resources long after the consumer was considered removed.

Using the stream as a change feed

The built-in subscriber applies changes to PostgreSQL tables. A different logical-decoding consumer might update a search projection or transport outbox rows. It takes responsibility for the starting snapshot, transaction boundaries, restart state and acknowledgement of durable work.

Logical decoding can redeliver recent changes after a publisher crash because slot progress is persisted at checkpoints. A consumer can also stop between applying a change and recording progress. The ordering of those two durable writes determines what a restart can lose or repeat.

Saving the projection first risks repetition. Saving progress first risks omission. A destination transaction that commits the projection and its restart record together solves this local gap, with acknowledgements tied to that durable outcome. PostgreSQL’s replication origins support crash-safe apply progress; they do not create an atomic transaction with an external service.

Setting P7’s price to 22.00 twice is harmless for this projection. Appending a history record or sending a notification twice is a different effect. The shop’s order-103-ack outbox record explicitly means “acknowledge the pending order received, total 29.00”. A products update cannot infer that intention. A feed can transport the outbox row, while Outboxes and database-backed jobs owns stable work identity, receiver deduplication and recovery of uncertain external outcomes.

Changing the publisher or the write authority

There are two different transitions. Continuing the same feed after the publisher fails needs its logical restart state on the intended new publisher. Making the logical subscriber the shop’s writer needs acceptance of a different database as the authority.

For feed continuity, PostgreSQL can synchronise failover-enabled logical slots to a physical standby. The subscription’s failover = true option enables its slot for this; the standby also needs slot synchronisation, a physical slot, hot_standby_feedback and a database name in its primary connection. synchronized_standby_slots can prevent the logical consumer running ahead of the intended standby.

Configuration alone is insufficient because slot synchronisation is asynchronous. On that exact standby, verify every required slot exists, is synced, persistent and not invalidated, including completed table-sync slots where relevant. Ordinary physical promotion does not by itself preserve a logical consumer’s restart contract. PostgreSQL physical replicas and promotion owns fencing, promotion and returning the former primary.

For a migration, supply evidence that every required table is ready, the target has applied through the final source boundary, schema is compatible and sequences have been prepared. A correct P7 price or zero reported lag cannot authorise target writes: orders, roles or external work may still be missing. Upgrading and migrating PostgreSQL owns draining source writers, full acceptance, write handover and the point after which a simple return is no longer possible.