논리 복제의 충돌 해결

Unlike physical replication, logical replication applies changes as SQL-level operations on the subscriber. If local data diverges from the incoming change, a conflict occurs and apply stops.

Typical conflicts include:

• Unique violation — a row with the same key already exists
• Missing row — an UPDATE or DELETE targets a row not present on the subscriber
• Type or constraint mismatch

When a conflict hits, the subscription's apply worker errors out and retries the same transaction repeatedly, so replication lag grows until you intervene.

The PostgreSQL log records the failing transaction and its LSN. You can also inspect subscription stats.

One option is to make the local data consistent so the change can apply: delete the conflicting row, then let replication retry.

If the offending change should be ignored, skip it by LSN. Use this carefully because it abandons that data.

Set the subscription to disable itself on error so it stops retrying and waits for an operator.

Reduce conflicts by:

• Using a single writer per logical dataset
• Avoiding overlapping primary key ranges across nodes
• Ensuring the subscriber schema matches the publisher

UPDATE and DELETE need a replica identity to locate the target row. A table without a primary key needs REPLICA IDENTITY FULL or replication will fail.

Watch pg_replication_slots on the publisher; a stalled subscriber holds back WAL and grows the slot, risking disk exhaustion.

Because conflicts need manual judgement, teams keep a runbook: how to identify, whether to fix-data or skip, and how to verify consistency afterward.

An incoming INSERT hits a unique violation on the subscriber. What is the safest resolution?

You learned how logical replication conflicts arise, how to find them via stats and logs, and how to resolve them by fixing data, skipping an LSN, or using disable_on_error. Proper replica identity and single-writer design prevent most conflicts.