PostgreSQL logical replication stopped: subscription errors, conflicts and a slot that keeps WAL forever

#Symptom

One or more of these:

• Rows written on the publisher stop appearing on the subscriber.
• The subscriber log repeats the same error every few seconds, often duplicate key value violates unique constraint or logical replication target relation "public.orders" does not exist.
• On the publisher, pg_wal grows and the disk-space alert fires, because the subscription's slot is holding WAL.
• On the publisher, updates or deletes fail with cannot update table "orders" because it does not have a replica identity and publishes updates.

Logical replication applies changes one transaction at a time, in order. If one transaction fails on the subscriber, the apply worker exits, restarts and fails on the same transaction again. Nothing after it is applied until you fix it.

#Step 1: confirm on the subscriber

-- Apply workers and how far they got
SELECT subname, pid, received_lsn, latest_end_lsn,
       last_msg_receipt_time, latest_end_time
FROM pg_stat_subscription;

-- Error counters (PostgreSQL 15+)
SELECT subname, apply_error_count, sync_error_count, stats_reset
FROM pg_stat_subscription_stats;

-- Is the subscription enabled?
SELECT subname, subenabled, subslotname, subpublications
FROM pg_subscription;

A NULL pid, an apply_error_count that keeps climbing, or a last_msg_receipt_time far in the past all point to a stuck apply worker. PostgreSQL 18 adds conflict counters (confl_* columns) to pg_stat_subscription_stats; column names vary by version, so check yours with \d pg_stat_subscription_stats.

Check per-table state too. Tables still in initial copy can fail separately from the main apply:

SELECT srrelid::regclass AS table_name, srsubstate, srsublsn
FROM pg_subscription_rel
ORDER BY srsubstate;

r means ready and s synchronized. i (initialize) or d (data copy) that never finishes means the table sync worker is failing; look for errors mentioning that table.

#Step 2: read the error and the finishing LSN

The subscriber's server log gives you the failing transaction. On PostgreSQL 15 and later the error has a context line like this:

ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(48213) already exists.
CONTEXT:  processing remote data for replication origin "pg_16395" during message type "INSERT"
          for replication target relation "public.orders" in transaction 725, finished at 0/14C0378

Write down the relation, the key and the finished at LSN. You need the LSN if you decide to skip the transaction.

#Step 3: check the publisher side

-- Slot for the subscription (named after the subscription by default)
SELECT slot_name, plugin, active, active_pid, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- Walsender for the subscriber
SELECT application_name, client_addr, state, sent_lsn, replay_lsn, reply_time
FROM pg_stat_replication;

retained_wal is how much WAL the publisher keeps because the subscriber has not confirmed it. wal_status = 'lost' means the slot was invalidated (usually by max_slot_wal_keep_size) and this subscription cannot resume; you must rebuild it (see the last section). PostgreSQL 17 also shows invalidation_reason and inactive_since in this view.

#Common causes and fixes

#Fix a conflicting row

This is the safest fix because no change from the publisher is lost. Using the error above:

-- On the subscriber: compare with the publisher's row first
SELECT * FROM public.orders WHERE id = 48213;
DELETE FROM public.orders WHERE id = 48213;

The apply worker retries on its own and should get past the transaction within seconds. If disable_on_error disabled the subscription, run ALTER SUBSCRIPTION sub_orders ENABLE; after the fix. Watch pg_stat_subscription to confirm latest_end_lsn moves.

#Fix missing tables or columns

DDL is not replicated. Apply the same DDL on the subscriber, then tell it about new tables:

-- Subscriber
ALTER TABLE public.orders ADD COLUMN channel text;
ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION;

REFRESH PUBLICATION copies data for newly added tables by default (copy_data = true). Run DDL on the subscriber first and the publisher second, so new columns exist before rows arrive with them.

#Fix replica identity on the publisher

-- Best: add a primary key
ALTER TABLE public.audit_log ADD PRIMARY KEY (id);
-- Or use an existing unique, non-null index
ALTER TABLE public.audit_log REPLICA IDENTITY USING INDEX audit_log_uid_key;
-- Last resort: whole-row identity (more WAL, slower apply)
ALTER TABLE public.audit_log REPLICA IDENTITY FULL;

#Skip the failing transaction

Caution

Skipping throws away the entire remote transaction, not just the one row. The subscriber is then permanently missing those changes until you repair the data by hand. Only skip when you know what the transaction contained.

On PostgreSQL 15 and later:

ALTER SUBSCRIPTION sub_orders SKIP (lsn = '0/14C0378');

Use the finished at LSN from the log. The skip only applies if it matches the next failing transaction, and it clears itself once used. Superuser is required on 15; check your version's docs for who may run it on later releases and on managed services.

On PostgreSQL 13–14 there is no SKIP. You can move the replication origin forward instead, with the subscription disabled:

ALTER SUBSCRIPTION sub_orders DISABLE;
SELECT roname, remote_lsn FROM pg_replication_origin_status;  -- find pg_<subid>
SELECT pg_replication_origin_advance('pg_16395', '0/14C0379'::pg_lsn);
ALTER SUBSCRIPTION sub_orders ENABLE;

This skips everything up to that LSN, so be exact.

#Stop the publisher disk filling while you work

If the publisher is close to full, the slot is the risk. Options, safest first:

1. Fix the subscriber error so the slot advances.
2. Set a cap so one stuck subscriber can't take the publisher down:

ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';
SELECT pg_reload_conf();

3. As a last resort, drop the slot.

Warning

Dropping the slot frees WAL immediately but the subscription can never resume from where it stopped. You will have to resync every table. On managed services, set max_slot_wal_keep_size through the parameter group or server parameters instead of ALTER SYSTEM.

-- Publisher, only if you accept a full resync
SELECT pg_drop_replication_slot('sub_orders');

#Rebuild a subscription that can't resume

When the slot is lost or dropped, recreate the subscription cleanly:

-- Subscriber
ALTER SUBSCRIPTION sub_orders DISABLE;
ALTER SUBSCRIPTION sub_orders SET (slot_name = NONE);
DROP SUBSCRIPTION sub_orders;

TRUNCATE public.orders, public.order_lines;   -- tables in the publication

CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=pub01 dbname=shop user=repl password=...'
  PUBLICATION pub_orders
  WITH (copy_data = true, disable_on_error = true);

SET (slot_name = NONE) lets you drop the subscription without trying to drop a slot that no longer exists on the publisher. Truncate first, or the initial copy hits the same duplicate keys.

#Prevent it next time

• Make the subscriber read-only for applications. A user that can write to replicated tables will eventually cause a duplicate key.
• Create every new subscription with disable_on_error = true (15+). A disabled subscription is easier to see than a worker in a retry loop, and it stops log noise.
• Set max_slot_wal_keep_size on every publisher so a dead subscriber can't fill the disk.
• Alert on retained WAL per slot, on pg_stat_subscription_stats.apply_error_count increasing, and on last_msg_receipt_time older than a few minutes.
• Put schema changes in a runbook: subscriber first, publisher second, REFRESH PUBLICATION when tables are added.
• Sequences are not replicated in PostgreSQL 17 and earlier. Before any cutover, set them on the subscriber with setval() from the publisher's values.