Postgresql 中文操作指南

31.5. Conflicts #

逻辑复制的行为与正常的 DML 操作类似，即使数据在订阅者节点上本地更改，数据也会更新。如果输入数据违反任何约束，则复制将停止。这称为 conflict。在复制 UPDATE 或 DELETE 操作时，缺少数据不会产生冲突，且此类操作将被跳过。

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.

逻辑复制操作以拥有订阅的角色的权限执行。目标表上的权限故障将导致复制冲突，同样，目标表上启用了 row-level security，订阅所有者适用，而不管任何策略是否通常拒绝正在复制的_INSERTUPDATEDELETE_或_TRUNCATE_。在PostgreSQL的未来版本中可能会取消对行级安全性的这种限制。

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

包含导致违反约束的变更的事务的 LSN 和复制源名称可以从服务器日志中找到（在以上情况中为 LSN 0/14C0378 和复制源 pg_16395 ）。通过将 ALTER SUBSCRIPTION … SKIP 与终止 LSN（即，LSN 0/14C0378）一起使用，可以跳过产生冲突的事务。终止 LSN 可能是事务在发布服务器上提交或准备时的 LSN。或者，还可以通过调用 pg_replication_origin_advance() 函数来跳过该事务。在使用此函数之前，需要通过 ALTER SUBSCRIPTION … DISABLE 暂时禁用订阅，也可以在 disable_on_error 选项中使用订阅。然后，可以将 pg_replication_origin_advance() 函数与 node_name （即， pg_16395 ）和终止 LSN 的下一个 LSN（即，0/14C0379）一起使用。可以在 pg_replication_origin_status 系统视图中查看源的当前位置。请注意，跳过整个事务包括跳过那些未违反任何约束的变更。这很容易让订阅者不一致。

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). The transaction that produced the conflict can be skipped by using ALTER SUBSCRIPTION … SKIP 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 pg_replication_origin_advance() function. Before using this function, the subscription needs to be disabled temporarily either by ALTER SUBSCRIPTION … DISABLE or, the subscription can be used with the disable_on_error option. Then, you can use pg_replication_origin_advance() function with the node_name (i.e., pg_16395) and the next LSN of the finish LSN (i.e., 0/14C0379). The current position of origins can be seen in the pg_replication_origin_status 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.

当 streaming 模式为 parallel 时，可能不会记录失败事务的终止 LSN。在这种情况下，可能需要将流媒体模式更改为 on 或 off ，并再次引起相同冲突，以便失败事务的终止 LSN 将被写入服务器日志。有关终止 LSN 的用法，请参阅 ALTER SUBSCRIPTION … SKIP 。

When the streaming mode is parallel, the finish LSN of failed transactions may not be logged. In that case, it may be necessary to change the streaming mode to on or off and cause the same conflicts again so the finish LSN of the failed transaction will be written to the server log. For the usage of finish LSN, please refer to ALTER SUBSCRIPTION … SKIP.