skip to content

A PostgreSQL 18 subscription stops applying changes and logs conflict=insert_exists; how do you diagnose the cause and resume replication safely?

level: seniorimportance: should knowfreq 42%

answer

  1. unique key already there locally
  2. worker restarts, same transaction
  3. log line and confl_* counters
  4. fix the row or SKIP lsn
  5. stop local writes

basics

~20 s

An incoming insert hit a unique key already present on the subscriber, so the apply worker errors and retries in a loop. Read the logged key and finish LSN, fix the local row or skip that transaction, then stop local writes.

solid answer

~40 s

PostgreSQL 18 logs `conflict detected on relation "sales.orders": conflict=insert_exists` with the key, the existing local row and the remote row, plus a `CONTEXT` line naming the replication origin and the remote transaction's finish LSN; `pg_stat_subscription_stats.confl_insert_exists` counts it. The worker exits and is restarted every `wal_retrieve_retry_interval`, so the whole subscription stalls while the publisher's slot keeps WAL. Find out why the row exists: usually someone wrote to the replicated table on the subscriber, or the table was not empty before the initial copy. Then either fix the local data so the insert succeeds, or skip the remote transaction with `ALTER SUBSCRIPTION ... SKIP (lsn = ...)`, accepting that all its changes are lost. Afterwards revoke write privileges on replicated tables.

code

console · 4 lines
console
ERROR:  conflict detected on relation "sales.orders": conflict=insert_exists
DETAIL:  Key already exists in unique index "orders_pkey", which was modified locally in transaction 8812 at 2026-10-02 09:14:07.31+00.
Key (order_id)=(500231); existing local row (500231, 'EU', 'draft'); remote row (500231, 'EU', 'paid').
CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "sales.orders" in transaction 77310 finished at 0/3A1F2C8

go deeper

for a junior

Remember that rows written locally on a subscriber can collide with replicated ones, and that a collision stops replication until someone fixes it.

for a middle

Explain the conflict types, which ones stop replication and which are skipped, and where 18 logs and counts them.

for a senior

Walk the incident: read the key and finish LSN, decide which row is right, fix data or SKIP with its whole-transaction cost, and lock down local writes.

for a principal

Set the policy around it: who may write to replicated tables, which conflict counters page someone, and when drift justifies a full re-sync.

## Why logical replication has conflicts The apply worker on the subscriber executes each decoded change as an ordinary row operation, as the table owner, against tables that the subscriber is otherwise free to modify. If an incoming row violates a constraint there, that is a **conflict**. Nothing in PostgreSQL stops local writes to a subscribed table, so a conflict nearly always means the analytics side changed data it should only receive. The other usual cause is a table that already held rows before the initial copy. ## Conflict types in PostgreSQL 18 PostgreSQL 18 names and counts these cases: | Conflict | Trigger | What the apply worker does | |---|---|---| | `insert_exists` | incoming insert violates a `NOT DEFERRABLE` unique constraint | error; replication stops | | `update_exists` | an updated value violates a unique constraint | error; replication stops | | `multiple_unique_conflicts` | the row violates several unique constraints | error; replication stops | | `update_missing`, `delete_missing` | the target row is not found | change skipped, logged | | `update_origin_differs`, `delete_origin_differs` | the local row was last changed by another origin | change applied, logged | Other violations (check, foreign-key and exclusion constraints, missing privileges, row-level security on the target) also stop replication, without a named type. The `*_origin_differs` cases are detected only when `track_commit_timestamp` is on at the subscriber; it defaults to off and needs a restart. It is also what puts the local row's origin and commit time into the log. ## Reading the evidence - **The subscriber's log**: the `ERROR` line with `conflict=insert_exists`, a `DETAIL` with `Key (order_id)=(...)`, the existing local row and the remote row, and a `CONTEXT` line such as `processing remote data for replication origin "pg_16395" ... finished at 0/3A1F2C8`. That LSN is the remote transaction's **finish LSN**. - **`pg_stat_subscription_stats`**: `apply_error_count` and the per-type `confl_*` counters, new in 18, show how often and which kind. - **The behaviour**: the worker exits, the launcher restarts it after `wal_retrieve_retry_interval` (default 5 s), and it fails on the same transaction again. Every table in the subscription stops advancing, and the slot on the orders server retains WAL meanwhile. ## Why the local row exists The log's `DETAIL` usually answers this once you know what to look for: 1. **Local writes.** An analyst, ETL job or migration inserted into `sales.orders` on the analytics server; with `track_commit_timestamp` on, the DETAIL says the row was modified locally and when. 2. **A non-empty table at subscribe time.** `copy_data = true` copied rows into a table that already held some, or a subscription was dropped and re-created over old data. 3. **Overlapping subscriptions.** Two subscriptions deliver the same table, so the second insert of each row collides with the first. ## Resolving it 1. **Decide which row is right.** If the local row was written by mistake on the analytics server, delete or correct it there; the worker's next attempt succeeds and nothing is lost. 2. **Skip the remote transaction** when the remote change must be discarded: `ALTER SUBSCRIPTION analytics_sub SKIP (lsn = '0/3A1F2C8')`. The worker drops **every** change in that transaction, not just the conflicting row, then clears `pg_subscription.subskiplsn`. That can leave the subscriber inconsistent, so check the affected tables afterwards. 3. The lower-level alternative is disabling the subscription and calling `pg_replication_origin_advance()` with the origin name and the LSN just past the finish LSN. 4. To stop the retry loop while you investigate, set `disable_on_error = true` (default false) on the subscription; the next error disables it until `ALTER SUBSCRIPTION ... ENABLE`. ## The parallel-streaming wrinkle In 18 `CREATE SUBSCRIPTION` defaults to `streaming = parallel`, so large in-progress transactions may be applied by parallel apply workers. When the error happens in one of those, the finish LSN may not be logged. Set `streaming = on` (or `off`) with `ALTER SUBSCRIPTION ... SET`, let the conflict recur, and read the LSN from the log. ## Preventing a repeat - Revoke `INSERT`, `UPDATE` and `DELETE` on replicated tables from every role on the analytics server except the subscription's. - Make sure tables are empty before an initial copy, or create the subscription with `copy_data = false` deliberately. - Alert on `confl_*` counters rising, especially `confl_update_missing` and `confl_delete_missing`: those changes are skipped, so the copy drifts without replication ever stopping.

  • Why are update_missing and delete_missing more dangerous over time than insert_exists?
    They do not stop replication: the change is skipped and only logged and counted. A subscriber that lost rows, or had them deleted locally, keeps running while its copy drifts further from the publisher. insert_exists halts the subscription and gets noticed at once. Alert on `confl_update_missing` and `confl_delete_missing` rising.
  • Does PostgreSQL 18 resolve the conflict automatically, for example last writer wins?
    Not for errors. `insert_exists`, `update_exists` and `multiple_unique_conflicts` stop replication until you change the data, skip the transaction or advance the origin. The `*_origin_differs` cases are applied regardless of the local row's origin and only logged. A resolution policy is your decision, informed by the log's origin and commit timestamp details.

  • The apply worker skips the conflicting row and carries on with the transaction.
  • ALTER SUBSCRIPTION ... SKIP discards only the one conflicting row.
  • PostgreSQL 18 resolves insert_exists automatically with last writer wins.
  • A stuck subscription is harmless to the publisher.
  • Conflicts appear in pg_stat_replication on the publisher.

Assumes PostgreSQL 18: named conflict log lines, the confl_* counters in pg_stat_subscription_stats and the streaming = parallel default are all new in 18.