summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/logical-replication-conflicts.html
blob: 99b8a7969b61fa60d3d02878677d1cc1c903d853 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>31.5. Conflicts</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="logical-replication-col-lists.html" title="31.4. Column Lists" /><link rel="next" href="logical-replication-restrictions.html" title="31.6. Restrictions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">31.5. Conflicts</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logical-replication-col-lists.html" title="31.4. Column Lists">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><th width="60%" align="center">Chapter 31. Logical Replication</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logical-replication-restrictions.html" title="31.6. Restrictions">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGICAL-REPLICATION-CONFLICTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">31.5. Conflicts</h2></div></div></div><p>
   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 <em class="firstterm">conflict</em>.  When
   replicating <code class="command">UPDATE</code> or <code class="command">DELETE</code>
   operations, missing data will not produce a conflict and such operations
   will simply be skipped.
  </p><p>
   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
   <a class="link" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">row-level security</a> on target tables
   that the subscription owner is subject to, without regard to whether any
   policy would ordinarily reject the <code class="command">INSERT</code>,
   <code class="command">UPDATE</code>, <code class="command">DELETE</code> or
   <code class="command">TRUNCATE</code> which is being replicated.  This restriction on
   row-level security may be lifted in a future version of
   <span class="productname">PostgreSQL</span>.
  </p><p>
   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.
  </p><p>
   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:
</p><pre class="screen">
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
</pre><p>
   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 <code class="literal">pg_16395</code> in the above case).  The
   transaction that produced the conflict can be skipped by using
   <code class="command">ALTER SUBSCRIPTION ... SKIP</code> with the finish LSN
   (i.e., LSN 0/14C0378).  The finish LSN could be an LSN at which the transaction
   is committed or prepared on the publisher.  Alternatively, the transaction can
   also be skipped by calling the <a class="link" href="functions-admin.html#PG-REPLICATION-ORIGIN-ADVANCE">
   <code class="function">pg_replication_origin_advance()</code></a> function.
   Before using this function, the subscription needs to be disabled temporarily
   either by <code class="command">ALTER SUBSCRIPTION ... DISABLE</code> or, the
   subscription can be used with the <code class="literal">disable_on_error</code> option.
   Then, you can use <code class="function">pg_replication_origin_advance()</code> function
   with the <em class="parameter"><code>node_name</code></em> (i.e., <code class="literal">pg_16395</code>)
   and the next LSN of the finish LSN (i.e., 0/14C0379).  The current position of
   origins can be seen in the <a class="link" href="view-pg-replication-origin-status.html" title="54.18. pg_replication_origin_status">
   <code class="structname">pg_replication_origin_status</code></a> system view.
   Please note that skipping the whole transaction includes skipping changes that
   might not violate any constraint.  This can easily make the subscriber
   inconsistent.
  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logical-replication-col-lists.html" title="31.4. Column Lists">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logical-replication-restrictions.html" title="31.6. Restrictions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">31.4. Column Lists </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 31.6. Restrictions</td></tr></table></div></body></html>