Resolving Conflicts in Bidirectional Replication

Resolving an apply conflict means moving a stalled logical replication worker past the exact change it cannot apply while guaranteeing the two nodes converge…

Resolving an apply conflict means moving a stalled logical replication worker past the exact change it cannot apply while guaranteeing the two nodes converge on the same row state — the operational heart of the bidirectional replication and conflict resolution guide. It matters because a single unresolved conflict on native PostgreSQL stops the apply worker cold, pins the partner’s replication slot, and lets pg_wal grow until the disk fills, so the recovery path has to be fast, deterministic, and safe to run at 3 a.m. without deepening the divergence.

This page is the concrete mechanics: how to read pg_stat_subscription_stats and the apply-error log to identify the failing change, how to advance past it with ALTER SUBSCRIPTION ... SKIP (lsn = ...) or pg_replication_origin_advance(), and how to encode a deterministic last-update-wins rule so the skip does not just paper over a lost write. All behavior is validated against PostgreSQL 14–17, with the durable stats view requiring PG 15+.

Apply-conflict recovery path from stall to resume Four states run left to right. The apply worker is streaming changes until a conflict such as a duplicate key or a missing row is raised at a specific LSN. With disable_on_error set, the subscription is disabled rather than retrying. An operator resolves it by running ALTER SUBSCRIPTION SKIP with that LSN or by calling pg_replication_origin_advance. A return path shows the worker resuming with the conflicting transaction skipped. Apply worker streaming changes Conflict raised duplicate key / row not found Subscription disabled disable_on_error SKIP (lsn = …) or origin_advance operator resolves at LSN X on error resume; conflicting transaction skipped, then re-enable
A conflict disables the subscription at a known LSN; the operator skips that transaction or advances the origin, then re-enables the worker so it resumes past the failure.

Conflict Type Semantics

Every apply conflict maps to a distinct detection signal and a distinct correct resolution; treating them as one generic “skip it” is how writes get lost. The table pairs each conflict class with the log signature that identifies it and the resolution that actually converges the two nodes rather than merely unblocking apply.

Conflict type Detection signal Deterministic resolution
Insert / primary-key collision duplicate key value violates unique constraint; apply_error_count rises, pid goes NULL Compare version/updated_at; keep the newer row, SKIP the losing insert, then fix disjoint sequences so it cannot recur
Update / update on same row Divergent column values; no error under REPLICA IDENTITY DEFAULT Last-update-wins on a monotonic column applied in a BEFORE trigger; the older write is discarded deterministically on both nodes
Update / delete (row deleted remotely) logical replication target relation ... row not found (as an update) If delete wins, SKIP the update; if the row must survive, model deletes as a soft-delete flag so both become column writes
Delete / delete (already gone) row not found on a DELETE Idempotent — the intended end state already holds; SKIP the redundant delete safely
Missing-row / applied out of order row not found on an update to a not-yet-inserted row Ensure a single origin ordering per key; reconcile by re-fetching the row from the authoritative node
Exclusion / check constraint violates ... constraint in the apply log Resolve the domain conflict in application logic; a blind skip drops a legitimate change

Two properties make a resolution deterministic. First, it must reach the same decision on both nodes independently — a comparison of two immutable values (commit timestamp, version counter) does that; a comparison against “current” state does not, because current differs per node. Second, it must be idempotent, so replaying the same change after a restart produces no additional effect. A last-update-wins rule keyed on a version bigint that every writer increments satisfies both: whichever change carries the higher version wins on both sides, and re-applying it is a no-op.

Diagnostic Patterns

Start at the durable counters, then drill to the specific LSN. pg_stat_subscription_stats (PG 15+) survives worker restarts, so a rising apply_error_count is the first durable evidence that a conflict is looping; pg_stat_subscription shows whether the worker is currently alive.

sql
-- Which subscription is failing, and is its worker still running?
-- ALERT: pid IS NULL on an enabled subscription, or apply_error_count climbing.
SELECT s.subname,
       s.subenabled,
       st.pid,                       -- NULL = apply worker stopped
       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
ORDER BY ss.apply_error_count DESC NULLS LAST;

The counter tells you a conflict exists; the server log tells you which change. With log_min_messages = warning or lower, the apply worker logs the failing relation, the conflict type, and — critically — the finish_lsn of the transaction you must skip:

code
ERROR: duplicate key value violates unique constraint "account_pkey"
DETAIL: Key (account_id)=(4711) already exists.
CONTEXT: processing remote data for replication origin "pg_16451"
         during "INSERT" for replication target relation "app.account"
         in transaction 90352 finished at 0/1A2B3C8

That finished at 0/1A2B3C8 is the LSN the next section skips. On PG 15+ you can also read it without grepping logs, because the disabled subscription exposes the last error through the stats subsystem and the finish_lsn is what ALTER SUBSCRIPTION ... SKIP expects verbatim. Confirm the partner slot is not yet in danger while you work:

sql
-- On the partner (publisher for this direction): is our stall pinning WAL?
-- ALERT: retained_wal approaching max_slot_wal_keep_size while we recover.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Safe Deployment Sequence

Recover the worker in a fixed order so you never skip more than the one offending transaction. Keep disable_on_error = true on production subscriptions so a conflict lands you here deliberately instead of retrying in a hot loop.

1. Confirm the exact LSN. Read finish_lsn from the log line or the last-error stats. Skipping the wrong LSN either skips nothing or discards a good transaction, so verify it before you type it.

2. Reconcile the row first if a write would be lost. If the conflict is a genuine update-update or insert collision, apply the winning value by hand on the node that should hold it, so that skipping the incoming change leaves the correct state rather than the stale one.

sql
-- Example: last-update-wins says the remote value should win. Apply it
-- explicitly before skipping, so the skip does not revert to the old row.
UPDATE app.account
SET balance = 500.00, version = 44, updated_at = '2026-07-17T09:15:02Z'
WHERE account_id = 4711 AND version < 44;

3. Skip the conflicting transaction. ALTER SUBSCRIPTION ... SKIP (lsn = ...) tells the apply worker to skip exactly the transaction that finishes at that LSN, then continue. It affects one transaction only and clears itself afterward.

sql
-- Skip precisely the transaction that could not apply.
ALTER SUBSCRIPTION sub_from_a SKIP (lsn = '0/1A2B3C8');
-- Re-enable if disable_on_error stopped it.
ALTER SUBSCRIPTION sub_from_a ENABLE;

4. Use pg_replication_origin_advance() only for a stuck origin. When the subscription cannot even start — a corrupted or duplicated origin position, or a slot recreated after failover — advance the origin cursor past the bad range instead. This is heavier than SKIP because it moves the whole origin, so it is a recovery tool, not a per-conflict one.

sql
-- Last resort: move the origin cursor forward. Requires the subscription
-- to be disabled first; the name comes from pg_replication_origin.roname.
ALTER SUBSCRIPTION sub_from_a DISABLE;
SELECT pg_replication_origin_advance('pg_16451', '0/1A2B400');
ALTER SUBSCRIPTION sub_from_a ENABLE;

5. Verify convergence, then fix the root cause. Re-run the divergence checksum from the parent guide on both nodes and confirm apply_error_count has stopped rising. Then eliminate the recurrence — disjoint sequences for a key collision, REPLICA IDENTITY FULL plus a version column for update conflicts — because a skip that is not paired with a root-cause fix guarantees you will be back tomorrow.

Pipeline Integration

The apply worker and any external consumer must agree on the same resolution rule, or they will converge to different answers. In a Python ETL that mirrors the two-way tables, encode the identical last-update-wins comparison the database uses, keyed on the same version column, and make the write idempotent so a retried or replayed change never regresses the row.

python
# Deterministic last-update-wins upsert, safe to replay under at-least-once.
# The WHERE clause makes the write a no-op unless the incoming version is newer.
UPSERT = """
INSERT INTO app.account (account_id, balance, version, updated_at)
VALUES (%(id)s, %(balance)s, %(version)s, %(updated_at)s)
ON CONFLICT (account_id) DO UPDATE
   SET balance    = EXCLUDED.balance,
       version    = EXCLUDED.version,
       updated_at = EXCLUDED.updated_at
 WHERE EXCLUDED.version > app.account.version;   -- older writes lose, deterministically
"""

def apply_change(conn, row):
    backoff = 0.5
    while True:
        try:
            with conn.cursor() as cur:
                cur.execute(UPSERT, row)
            conn.commit()
            return
        except psycopg2.OperationalError:
            conn.rollback()
            time.sleep(backoff + random.random() * 0.25)  # jitter
            backoff = min(backoff * 2, 30)

For failover handling, a conflict recovery that used pg_replication_origin_advance() must be reconciled against the slot state after any promotion, because an origin advanced past changes the new primary never persisted can leave the reconnecting subscription with a gap. The native resume-and-resync path for that case is covered in subscription sync procedures, and continuous visibility into apply_error_count and slot lag belongs in the collector described under async monitoring integration so the next conflict pages you before the disk does.

Authoritative references