Logical replication behaves similarly to normal DML operations in that
the data will be updated even if it was changed locally on the subscriber
node. If incoming data violates any constraints the replication will
stop. This is referred to as a conflict. When
replicating UPDATE
or DELETE
operations, missing data will not produce a conflict and such operations
will simply be skipped.
Logical replication operations are performed with the privileges of the role
which owns the subscription. Permissions failures on target tables will
cause replication conflicts, as will enabled
row-level security on target tables
that the subscription owner is subject to, without regard to whether any
policy would ordinarily reject the INSERT
,
UPDATE
, DELETE
or
TRUNCATE
which is being replicated. This restriction on
row-level security may be lifted in a future version of
PostgreSQL.
A conflict will produce an error and will stop the replication; it must be resolved manually by the user. Details about the conflict can be found in the subscriber's server log.
The resolution can be done either by changing data or permissions on the subscriber so that it does not conflict with the incoming change or by skipping the transaction that conflicts with the existing data. When a conflict produces an error, the replication won't proceed, and the logical replication worker will emit the following kind of message to the subscriber's server log:
ERROR: duplicate key value violates unique constraint "test_pkey" DETAIL: Key (c)=(1) already exists. CONTEXT: processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378
The LSN of the transaction that contains the change violating the constraint and
the replication origin name can be found from the server log (LSN 0/14C0378 and
replication origin pg_16395
in the above case). To skip the
transaction, the subscription needs to be disabled temporarily by
ALTER SUBSCRIPTION ... DISABLE
first or alternatively, the
subscription can be used with the disable_on_error
option.
Then, the transaction can be skipped by calling the
pg_replication_origin_advance()
function with
the node_name
(i.e., pg_16395
) and the
next LSN of the transaction's LSN (i.e., LSN 0/14C0379). After that the replication
can be resumed by ALTER SUBSCRIPTION ... ENABLE
. The current
position of origins can be seen in the
pg_replication_origin_status
system view.