Logical Replication Conflict

Scenario

A data engineering team set up logical replication from the production PostgreSQL database to a reporting PostgreSQL database. Replication worked for three weeks. Then a developer ran a one-time data-correction script directly on the subscriber, inserting a few rows into a replicated table to fix a reporting issue. The next day, the nightly ETL detects that the subscriber table is missing hundreds of rows. Investigation shows the logical replication worker silently stopped. The subscriber PostgreSQL logs contain: ERROR: duplicate key value violates unique constraint "orders_pkey". The worker stopped after failing to apply an INSERT from the publisher because the subscriber already has that row (inserted manually).

How to Identify

Conditions:

• pg_stat_subscription shows active = false or the worker process is absent
• Subscriber PostgreSQL logs contain ERROR: duplicate key violation or ERROR: could not find row for UPDATE/DELETE
• pg_stat_subscription.last_msg_receipt_time is stale (not updating)
• Subscription is in a disabled state; worker has exited
• pg_replication_origin_progress LSN has stopped advancing on the subscriber

Analysis Steps

-- 1. Check subscription status on the SUBSCRIBER
SELECT
    subname,
    subenabled,
    subslotname,
    subpublications
FROM pg_subscription;

-- 2. Check the subscription worker activity
SELECT
    subname,
    pid,
    relid,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    latest_end_time
FROM pg_stat_subscription;

-- 3. Check per-subscription statistics (PostgreSQL 15+)
--    On PG14: check subscriber logs directly
SELECT *
FROM pg_stat_subscription_stats;

-- 4. Identify conflicting rows on the SUBSCRIBER
--    (Replace 'orders_p034' with the actual conflicting table)
SELECT id FROM orders_p034
WHERE id IN (
    -- IDs that exist on subscriber but may conflict with publisher inserts
    SELECT id FROM orders_p034 ORDER BY id DESC LIMIT 100
);

-- 5. Check replication origin progress on SUBSCRIBER
SELECT *
FROM pg_replication_origin_progress('pg_16851');
-- Note: origin name matches subscription OID

Pitfalls

• Logical replication does NOT replicate DDL. If ALTER TABLE is run on the publisher, the subscriber silently drifts unless the same DDL is manually applied to the subscriber first. Schema drift is the #1 cause of silent logical replication failures.
• Manual writes to subscriber tables break replication. Any direct INSERT/UPDATE/DELETE on a replicated table on the subscriber that conflicts with incoming changes from the publisher will stop the worker.
• Conflicts must be resolved manually. Unlike physical replication, there is no automatic conflict resolution. You must decide which side wins.
• Skipping an LSN is irreversible. Using pg_replication_slot_advance() to skip a conflicting WAL <a class="sev1-termlink" href="https://thesev1database.com/glossary/tuple/">record skips that transaction permanently — the conflicting row will never be replicated.
• Disabling and re-enabling the subscription does NOT automatically resolve conflicts — the worker will fail again at the same LSN unless the conflict is first removed.

Resolution Approach

A logical replication conflict must be resolved before the subscription can resume. Choose the approach based on which side should win: