现有数据集上的Postgres 13 +pglogical2.3.4

Question

我遇到了一个问题，试图在提供者和订阅者之间设置pglogical，两者已经有一些共同的数据。

这是一个很小的脚本来重现我想要做的事情：

CREATE DATABASE db1;
\c db1;
CREATE EXTENSION pglogical;
CREATE TABLE table1(pk_field int primary key , value_field int);
INSERT INTO table1 VALUES(1,1);
INSERT INTO table1 VALUES(2,2);

SELECT pglogical.create_node(
    node_name := 'db1_node',
    dsn := 'host=localhost port=5432 dbname=db1 '
);
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

CREATE DATABASE db2;
\c db2;
CREATE EXTENSION pglogical;
CREATE TABLE table1(pk_field int primary key , value_field int);
INSERT INTO table1 VALUES(1,1);

SELECT pglogical.create_node(
    node_name := 'db2_node',
    dsn := 'host=localhost port=5432 dbname=db2'
);
SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=localhost port=5432 dbname=db1'
);

我认为当冲突解决被设置为'apply_remote‘时，pglogical会在db2上重新插入现有的pk1行，然后用PK2插入新行。但是db2.table 1与pk1保持在一行中。

Postgres日志显示如下：

2021-08-03 01:46:46.771 UTC [4496] ERROR: duplicate key value violates unique constraint "table1_pkey"
2021-08-03 01:46:46.771 UTC [4496] DETAIL: Key (pk_field)=(1) already exists.
2021-08-03 01:46:46.771 UTC [4496] CONTEXT: COPY table1, line 1
2021-08-03 01:46:46.771 UTC [4496] STATEMENT: COPY "public"."table1" ("pk_field","value_field") FROM stdin
2021-08-03 01:47:42.885 UTC [4507] LOG: starting apply for subscription subscription1
2021-08-03 01:47:42.885 UTC [4507] ERROR: subscriber subscription1 initialization failed during nonrecoverable step (d), please try the setup again
2021-08-03 01:47:42.885 UTC [4507] LOG: apply worker [4507] at slot 2 generation 124 exiting with error
2021-08-03 01:47:42.885 UTC [1] LOG: background worker "pglogical apply 30304:1763399739" (PID 4507) exited with exit code 1

混合成果设定：

show pglogical.conflict_resolution;
apply_remote

我也尝试过：

pglogical.conflict_resolution last_update_wins 

在看到副本在日志上失败并读取了批处理内部将使用复制命令的文档之后，我尝试禁用它：

pglogical.batch_inserts off

但还是没成功

我--我做错了什么，还是我误解了它的工作原理？也许冲突解决只在执行初始复制批处理之后才适用？

注意:如果重复行是在初始复制之后插入的，那么它可以正常工作：

2021-08-03 10:39:30.568 UTC [5309] LOG:  CONFLICT: remote INSERT on relation public.table1 (local index table1_pkey). Resolution: apply_remote.
2021-08-03 10:39:30.568 UTC [5309] DETAIL:  existing local tuple {pk_field[int4]:2 value_field[int4]:2} xid=81770,origin=-1,timestamp=; remote tuple {pk_field[int4]:2 value_field[int4]:3} in xact origin=4,timestamp=2021-08-03 10:39:30.566666+00,commit_lsn=0/FEB6918

Answer 1

我贴出了一个答案，以防它对别人有帮助。

初始数据同步不会执行冲突解决方案，如果发生冲突，复制将中止。

感谢逻辑团队在这个问题上的帮助：https://github.com/2ndQuadrant/pglogical/issues/328