codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 16.3 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 <a href="#LOGICAL-REPLICATION-CONFLICTS" class="id_link">#</a></h2></div></div></div><p>3   Logical replication behaves similarly to normal DML operations in that4   the data will be updated even if it was changed locally on the subscriber5   node.  If incoming data violates any constraints the replication will6   stop.  This is referred to as a <em class="firstterm">conflict</em>.  When7   replicating <code class="command">UPDATE</code> or <code class="command">DELETE</code>8   operations, missing data will not produce a conflict and such operations9   will simply be skipped.10  </p><p>11   Logical replication operations are performed with the privileges of the role12   which owns the subscription.  Permissions failures on target tables will13   cause replication conflicts, as will enabled14   <a class="link" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">row-level security</a> on target tables15   that the subscription owner is subject to, without regard to whether any16   policy would ordinarily reject the <code class="command">INSERT</code>,17   <code class="command">UPDATE</code>, <code class="command">DELETE</code> or18   <code class="command">TRUNCATE</code> which is being replicated.  This restriction on19   row-level security may be lifted in a future version of20   <span class="productname">PostgreSQL</span>.21  </p><p>22   A conflict will produce an error and will stop the replication; it must be23   resolved manually by the user.  Details about the conflict can be found in24   the subscriber's server log.25  </p><p>26   The resolution can be done either by changing data or permissions on the subscriber so27   that it does not conflict with the incoming change or by skipping the28   transaction that conflicts with the existing data.  When a conflict produces29   an error, the replication won't proceed, and the logical replication worker will30   emit the following kind of message to the subscriber's server log:31</p><pre class="screen">32ERROR:  duplicate key value violates unique constraint "test_pkey"33DETAIL:  Key (c)=(1) already exists.34CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C037835</pre><p>36   The LSN of the transaction that contains the change violating the constraint and37   the replication origin name can be found from the server log (LSN 0/14C0378 and38   replication origin <code class="literal">pg_16395</code> in the above case).  The39   transaction that produced the conflict can be skipped by using40   <code class="command">ALTER SUBSCRIPTION ... SKIP</code> with the finish LSN41   (i.e., LSN 0/14C0378).  The finish LSN could be an LSN at which the transaction42   is committed or prepared on the publisher.  Alternatively, the transaction can43   also be skipped by calling the <a class="link" href="functions-admin.html#PG-REPLICATION-ORIGIN-ADVANCE">44   <code class="function">pg_replication_origin_advance()</code></a> function.45   Before using this function, the subscription needs to be disabled temporarily46   either by <code class="command">ALTER SUBSCRIPTION ... DISABLE</code> or, the47   subscription can be used with the48   <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-DISABLE-ON-ERROR"><code class="literal">disable_on_error</code></a>49   option. Then, you can use <code class="function">pg_replication_origin_advance()</code>50   function with the <em class="parameter"><code>node_name</code></em> (i.e., <code class="literal">pg_16395</code>)51   and the next LSN of the finish LSN (i.e., 0/14C0379).  The current position of52   origins can be seen in the <a class="link" href="view-pg-replication-origin-status.html" title="54.18. pg_replication_origin_status">53   <code class="structname">pg_replication_origin_status</code></a> system view.54   Please note that skipping the whole transaction includes skipping changes that55   might not violate any constraint.  This can easily make the subscriber56   inconsistent.57  </p><p>58   When the59   <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-STREAMING"><code class="literal">streaming</code></a>60   mode is <code class="literal">parallel</code>, the finish LSN of failed transactions61   may not be logged. In that case, it may be necessary to change the streaming62   mode to <code class="literal">on</code> or <code class="literal">off</code> and cause the same63   conflicts again so the finish LSN of the failed transaction will be written64   to the server log. For the usage of finish LSN, please refer to <a class="link" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><code class="command">ALTER SUBSCRIPTION ...65   SKIP</code></a>.66  </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 31.6. Restrictions</td></tr></table></div></body></html>