Bidirectional Replication & Conflict Resolution

Active-active PostgreSQL logical replication is conflict-prone by design: two primaries each accept writes with no global transaction order to arbitrate them, so the same row can be changed on both sides before either change crosses the wire. This guide, part of the replication topologies and failover operations reference, specifies how a two-way topology actually works on PostgreSQL 14–17 — why conflicts are structural rather than incidental, the exact conflict classes you will meet, how PG 16’s origin = none subscription filter breaks the apply loop, what replica identity the resolution logic requires, and where last-writer-wins ends and application-arbitrated reconciliation must begin.

The reason this matters in production is that a bidirectional topology fails quietly and then catastrophically. A single unresolved update-update conflict does not raise an error by default on native PostgreSQL — the apply worker either overwrites one side’s value or, on a constraint violation, stops entirely and pins the replication slot, freezing restart_lsn while pg_wal grows on the partner node. Left unattended, the two databases diverge silently: an account balance reads one value in region A and another in region B, and no query on either node reveals the split until a customer or an auditor finds it. Everything below is built to make that divergence either impossible or immediately visible.

Prerequisites & Configuration Objects #

A two-way topology is two independent publisher/subscriber pairs pointed at each other, so every object required by the publication and subscription model must exist twice — once per direction — and the two halves must agree on table set, replica identity, and origin handling. The server prerequisites are the same as any logical publisher: wal_level = logical on both nodes (a restart-only change), sufficient max_replication_slots and max_wal_senders to hold the outbound slot plus headroom, and max_logical_replication_workers high enough to run the inbound apply worker alongside any table-sync workers.

-- On BOTH nodes: enable logical decoding and reserve slot/worker headroom.
ALTER SYSTEM SET wal_level = 'logical';               -- restart required
ALTER SYSTEM SET max_replication_slots = 10;          -- outbound slot + spare
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_logical_replication_workers = 8; -- apply + table sync
ALTER SYSTEM SET max_worker_processes = 16;           -- must exceed the line above
-- track_commit_timestamp powers last-update-wins detection (PG needs a restart).
ALTER SYSTEM SET track_commit_timestamp = on;

track_commit_timestamp is the one prerequisite specific to conflict resolution rather than to replication generally. It records the commit time of every transaction in a way that survives restart and is exposed through pg_last_committed_xact() and the pg_xact_commit_timestamp() function, which is what a deterministic timestamp-based resolution reads to decide which of two concurrent updates is newer. Turn it on before the first bidirectional write, because it is not retroactive — rows committed while it was off have no recorded timestamp.

The replica identity requirement is stricter here than in one-way replication. On the publisher side an UPDATE or DELETE must carry enough of the old row for the partner’s apply worker to locate the target tuple, and for conflict resolution to compare old against new. REPLICA IDENTITY DEFAULT ships only the primary key, which is enough to find the row but not enough to detect that a column changed underneath you; REPLICA IDENTITY FULL ships every column so the apply side can see the pre-image and arbitrate. Choose it per table with the trade-offs described under choosing replica identity for updates and deletes.

-- Every table in a two-way set needs a stable, non-overlapping key.
ALTER TABLE app.account REPLICA IDENTITY FULL;   -- carry the pre-image for arbitration
-- A UNIQUE index also works as an explicit identity when FULL is too heavy:
ALTER TABLE app.ledger REPLICA IDENTITY USING INDEX ledger_pkey;

The last structural prerequisite is a write-partitioning strategy that prevents most conflicts before resolution ever runs. Sequences must not overlap — give each node a disjoint range (ALTER SEQUENCE ... START ... INCREMENT 2 with offsets 0 and 1, or switch to UUIDv7 keys) so two independent inserts never mint the same primary key. Native PostgreSQL provides no built-in sequence coordination across nodes; the pglogical extension and the BDR/Spock lineage built on it add conflict handlers and distributed sequences, but on stock PostgreSQL you engineer key disjointness yourself.

Step-by-Step Implementation #

The build order matters: create the tables and identities, then the publications, then the subscriptions with origin filtering, and only then remove any write freeze. Applying the two directions out of order is the most common way to seed a loop.

1. Create matching publications on both nodes. Each node publishes the same table set. Keep the publication explicit rather than FOR ALL TABLES so a table added on one node cannot silently join the topology on only one side; the mechanics of scoping are covered under creating publications.

-- Run identically on Node A and Node B.
CREATE PUBLICATION bidi_app
  FOR TABLE app.account, app.ledger
  WITH (publish = 'insert,update,delete');

2. Create each subscription with origin = none. This is the load-bearing step. On PG 16+ the subscription option origin = none tells the apply worker to request only changes that originated locally on the remote node and to skip any change the remote merely forwarded from elsewhere. Without it, a row A sends to B is applied on B, re-decoded from B’s WAL, and sent back to A, then to B, forever. The full behavior of the option is detailed in preventing replication loops with origin filtering.

-- On Node B: subscribe to A, taking only A-origin changes.
CREATE SUBSCRIPTION sub_from_a
  CONNECTION 'host=node-a dbname=app application_name=sub_from_a sslmode=verify-full'
  PUBLICATION bidi_app
  WITH (origin = none, copy_data = false, streaming = on);

-- On Node A: the mirror-image subscription to B.
CREATE SUBSCRIPTION sub_from_b
  CONNECTION 'host=node-b dbname=app application_name=sub_from_b sslmode=verify-full'
  PUBLICATION bidi_app
  WITH (origin = none, copy_data = false, streaming = on);

3. Seed initial data on exactly one node with copy_data = false. The initial table copy is not origin-aware — a fresh copy_data = true on both directions would duplicate every existing row. Load the baseline into one node before the subscriptions go live, or use one directional copy_data = true while the reverse subscription stays copy_data = false, following the controls in subscription sync procedures.

4. Verify origin tracking is live. Confirm each apply worker registered a replication origin and that roname matches the subscription. This is the provenance record that makes origin = none work.

-- Both nodes: one origin row per active subscription.
SELECT roname, roident FROM pg_replication_origin;
SELECT external_id, pg_replication_origin_progress(external_id, false) AS progress
FROM (SELECT roname AS external_id FROM pg_replication_origin) o;

5. Choose and encode a resolution rule per table. Native PostgreSQL has no conflict handler, so the rule lives in your schema and your apply path. For tables where the newest write should win, add a monotonic updated_at timestamptz or a version bigint and make every write set it; the deterministic comparison and the ALTER SUBSCRIPTION ... SKIP recovery step are covered in resolving conflicts in bidirectional replication. For tables where a blind overwrite would lose money — balances, inventory counts — the resolution must be application-arbitrated: model the value as an append-only set of deltas so the merge is commutative and no concurrent write is ever discarded.

Parameter Reference Table #

These are the settings that change conflict behavior specifically; each row calls out what the value means in a two-way topology as distinct from ordinary one-way replication.

Diagnostic Queries #

Divergence is invisible unless you instrument for it, so the query set below watches for the three states that precede a split: an apply worker that stopped on a conflict, an error counter climbing, and a slot pinning WAL because the partner cannot drain it. Run them on both nodes — a two-way topology is only as healthy as its worse half.

-- Apply-side error and liveness, per subscription. PG 15+ for the stats view.
-- ALERT: pid IS NULL on an enabled subscription (worker stopped on a conflict).
-- ALERT: apply_error_count increasing between scrapes.
SELECT s.subname,
       s.subenabled,
       st.pid,                         -- NULL = apply worker not running
       ss.apply_error_count,
       ss.sync_error_count,
       ss.stats_reset
FROM pg_subscription s
LEFT JOIN pg_stat_subscription       st ON st.subname = s.subname
LEFT JOIN pg_stat_subscription_stats ss ON ss.subname = s.subname;

-- Outbound slot health on each node: is the partner draining our changes?
-- ALERT: active = false (partner apply worker down) or retained > a few GB.
SELECT slot_name,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))          AS retained_wal,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))  AS unconfirmed
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- Origin progress: how far each node has confirmed applying the partner's stream.
-- A frozen remote_lsn while the partner keeps writing means apply has stalled.
SELECT ro.roname,
       pg_replication_origin_progress(ro.roname, false) AS applied_lsn
FROM pg_replication_origin ro;

The single most important cross-node check is a divergence probe on the tables you cannot afford to split. A periodic checksum of a key range on both nodes, compared out of band, catches a resolution rule that is quietly discarding writes long before a human notices a wrong number.

-- Run on both nodes; compare the hashes in your monitoring job.
-- A persistent mismatch on a settled key range = the nodes have diverged.
SELECT md5(string_agg(account_id::text || ':' || balance::text || ':' || version::text, ','
           ORDER BY account_id)) AS range_hash
FROM app.account
WHERE account_id BETWEEN 1 AND 100000;

Failure Modes & Gotchas #

The apply loop that never ends. Signature: CPU pinned on both apply workers, WAL volume rising with no new application writes, the same rows re-applied endlessly. Root cause: a subscription created with origin = any (the default) on a two-way set, so each node re-sends what it just received. Remediation: recreate both subscriptions with origin = none (PG 16+); on PG 15 and earlier, native two-way replication has no origin filter at all, and you must isolate the two directions by table set or move to pglogical, which tracks origin explicitly.

Update-update conflict silently overwrites. Signature: no error, but a column’s value flips back and forth between two settled states, or the two nodes hold different values for the same key. Root cause: two sessions updated the same row concurrently; with REPLICA IDENTITY DEFAULT and no resolution rule, whichever change applies last wins non-deterministically. Remediation: set REPLICA IDENTITY FULL, add a version/updated_at column, and encode last-update-wins in the apply path — the deterministic procedure is in resolving conflicts in bidirectional replication.

Insert-insert primary key collision stalls apply. Signature: ERROR: duplicate key value violates unique constraint in the apply worker log, pid goes NULL, the slot on the partner starts pinning WAL. Root cause: both nodes minted the same key from overlapping sequences. Remediation: recover the stuck worker by skipping the offending LSN, then fix the root cause with disjoint sequence ranges or UUIDv7 keys so the collision cannot recur. Set disable_on_error = true so the stall pages you instead of retrying in a tight loop.

Update-delete / missing-row conflict. Signature: ERROR: logical replication target relation ... row not found, or an update that vanishes. Root cause: one node deleted a row that the other node concurrently updated; the update arrives with nothing to apply to. Remediation: decide the intent — if delete wins, the update is correctly dropped and you only need to skip the error; if the row must survive, model deletes as soft-deletes (a deleted_at flag) so an update and a delete are both just column writes that a last-update-wins rule can order.

Divergence from a non-deterministic default. Signature: a DEFAULT now() or nextval() column holds different values on the two nodes for the same logical row. Root cause: defaults are evaluated per node at apply time when a column is absent from the change. Remediation: for any column that participates in identity or conflict resolution, always send its value explicitly (via REPLICA IDENTITY FULL and a full column list) rather than relying on a default the partner will re-evaluate.

Integration Touchpoints #

Bidirectional replication reuses every primitive from the one-way path and adds the constraint that both ends are live. The slot, publication, and subscription objects are exactly those built under logical replication setup and management; the difference is that each node is simultaneously the publisher for one direction and the subscriber for the other, so the security boundaries and permissions apply symmetrically — each node needs a REPLICATION-capable role reachable by the other, over TLS, with no broad replication all line in pg_hba.conf.

Failover interacts sharply with a two-way set. If one node is promoted or rebuilt, its outbound slot and its replication origin must both be re-established or the surviving node either loses its change source or re-applies history; the slot side of that recovery is the subject of the failover and slot recovery guide, and schema changes in a live two-way topology must roll out to both nodes in a compatible order, as covered in online schema change management. When a CDC consumer reads from one node of a bidirectional pair, it must filter by origin too, or it will emit each logical change twice — once as the local write and once as the replicated echo — which is exactly the deduplication the event routing and Kafka integration layer has to handle downstream.

Frequently Asked Questions #

Related guides #

• Resolving conflicts in bidirectional replication — detecting apply conflicts, ALTER SUBSCRIPTION ... SKIP, and deterministic last-update-wins.
• Preventing replication loops with origin filtering — how origin = none and replication origins track provenance and break the echo.
• Failover and slot recovery — re-establishing slots and origins when one node of the pair is promoted or rebuilt.
• Online schema change management — rolling compatible DDL across both nodes of a live two-way set.
• Replication Topologies & Failover Operations — the parent guide this two-way topology sits within.