October 29, 2025
Summary: in this tutorial, you will learn how to troubleshooting logical replication conflicts in PostgreSQL.
Table of Contents
The ALTER SUBSCRIPTION SKIP command
The purpose of this command is to skip a conflicting transaction finished at its specified LSN on the subscriber. The user can specify finish LSN by ‘lsn’ skip_option to indicate the failed transaction. The apply worker receives changes from the publisher and judges whether those should be skipped or not on the node. For this ALTER SUBSCRIPTION SKIP command input, we can use the error context’s finish LSN directly. We’ll have a look at one demonstration below.
Observing conflict and resolution by skipping a transaction
1. On the publisher side, create a table and a publication.
CREATE TABLE tab (id integer);
INSERT INTO tab VALUES (5);
CREATE PUBLICATION mypub FOR TABLE tab;
We now have one record for initial table synchronization.
2. On the subscriber side, create a table with a unique constraint and a subscription.
CREATE TABLE tab (id integer UNIQUE);
CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub WITH (disable_on_error = true);
NOTICE: created replication slot "mysub" on publisher
We have created a subscription with disable_on_error enabled. This subscription definition will cause the initial table synchronization in the background, which will succeed without any issues.
As a result, the value 5 will be inserted on the subscriber.
3. On the publisher side, execute three transactions in succession after the table synchronization.
BEGIN; -- Txn1
INSERT INTO tab VALUES (1);
COMMIT;
BEGIN; -- Txn2
INSERT INTO tab VALUES (generate_series(2, 8));
COMMIT;
BEGIN; -- Txn3
INSERT INTO tab VALUES (9);
COMMIT;
SELECT * FROM tab;
id
----
5
1
2
3
4
5
6
7
8
9
(10 rows)
On the publisher, we could execute these transactions successfully. However, Txn2 includes duplicate data, which was already inserted at the time of the initial table synchronization. Thus, on the subscriber, this violates the unique constraint on the table and throws an error.
4. On the subscriber side, check the current replication status.
SELECT subname, subenabled FROM pg_subscription;
subname | subenabled
---------+------------
mysub | f
(1 row)
SELECT * FROM tab;
id
----
5
1
(2 rows)
Now, we’ll have a look at the current status on the subscriber. According to the behavior of the disable_on_error option2, the subscription got disabled automatically by the error. Only Txn1 (and data replicated by the initial table synchronization) has been replicated on the subscriber. Here we can’t see the results of Txn2 and Txn3 because of the conflict. Txn3 will only get replayed after we resolve the conflict.
5. On the subscriber’s log, we see the error message of this conflict and log of disable_on_error option.
ERROR: duplicate key value violates unique constraint "tab_id_key"
DETAIL: Key (id)=(5) already exists.
CONTEXT: processing remote data for replication origin "pg_16389" during "INSERT" for replication target relation "public.tab" in transaction 730 finished at 0/1566D10
LOG: logical replication subscription "mysub" has been disabled due to an error
In the subscriber’s server log, we can get the finish LSN for the argument of ALTER SUBSCRIPTION SKIP command. I’ll utilize this finish LSN to skip Txn2 as below.
6. On the subscriber side, execute the ALTER SUBSCRIPTION SKIP command and enable the subscription.
ALTER SUBSCRIPTION mysub SKIP (lsn = '0/1566D10');
SELECT subname, subskiplsn, subenabled FROM pg_subscription;
subname | subskiplsn | subenabled
---------+------------+------------
mysub | 0/1566D10 | f
(1 row)
ALTER SUBSCRIPTION mysub ENABLE;
SELECT * FROM tab;
id
----
5
1
9
(3 rows)
SELECT subname, subskiplsn, subenabled FROM pg_subscription;
subname | subskiplsn | subenabled
---------+------------+------------
mysub | 0/0 | t
(1 row)
Here, I set the skip LSN and re-activated the subscription. Immediately, we see the replicated value of Txn3, without Txn2’s values. We have skipped the whole transaction. Along with this, the subskiplsn has been cleared, after we succeeded in skipping the changes.
7. On the subscriber log, we can see the command’s log message of successful completion.
LOG: start skipping logical replication transaction finished at 0/1566D10
CONTEXT: processing remote data for replication origin "pg_16389" during "BEGIN" in transaction 730 finished at 0/1566D10
LOG: done skipping logical replication transaction finished at 0/1566D10
CONTEXT: processing remote data for replication origin "pg_16389" during "COMMIT" in transaction 730 finished at 0/1566D10
We can see the logs of this success from the server log also.