0Pricing
Advanced PostgreSQL: Indexing, Partitioning, Replication · Lesson

Conflict Resolution in Logical Replication

Learn how conflicts arise in logical replication and the strategies PostgreSQL offers to detect and resolve them.

Conflict Resolution in Logical Replication is a free Advanced PostgreSQL: Indexing, Partitioning, Replication lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Advanced PostgreSQL: Indexing, Partitioning, Replication learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Conflicts Happen

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.

Common Conflict Types

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

Apply Worker Stalls

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

Finding the Conflict

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

SELECT subname, last_error_time, last_error_message
FROM pg_stat_subscription_stats;

Resolving by Fixing Data

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

DELETE FROM products WHERE id = 42;
-- the incoming INSERT for id 42 now applies

Skipping a Transaction

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

ALTER SUBSCRIPTION my_sub SKIP (lsn = '0/14A0B30');

Disabling on Error

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

ALTER SUBSCRIPTION my_sub SET (disable_on_error = true);

Preventing Conflicts

Reduce conflicts by:

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

Replica Identity Matters

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.

ALTER TABLE logs REPLICA IDENTITY FULL;

Monitoring Lag

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

SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
FROM pg_replication_slots;

Document a Runbook

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

Quick Check

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

Recap

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.

Frequently asked questions

Is the “Conflict Resolution in Logical Replication” lesson free?

Yes — the full text of “Conflict Resolution in Logical Replication” is free to read here on the web, and the Advanced PostgreSQL: Indexing, Partitioning, Replication course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Advanced PostgreSQL: Indexing, Partitioning, Replication course, upgrade to CoddyKit PRO.

What will I learn in “Conflict Resolution in Logical Replication”?

Learn how conflicts arise in logical replication and the strategies PostgreSQL offers to detect and resolve them. You practise Advanced PostgreSQL: Indexing, Partitioning, Replication with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Advanced PostgreSQL: Indexing, Partitioning, Replication?

No prior experience is required. Advanced PostgreSQL: Indexing, Partitioning, Replication on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Conflict Resolution in Logical Replication” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Advanced PostgreSQL: Indexing, Partitioning, Replication lesson?

Yes. Every Advanced PostgreSQL: Indexing, Partitioning, Replication lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Logical Replication Fundamentals
  2. Configuring Publications
  3. Managing Subscriptions
  4. Conflict Resolution in Logical Replication
← Back to Advanced PostgreSQL: Indexing, Partitioning, Replication