GBase 8s 数据库ER同步原始数据

转载

南大通用GBase 2024-07-01 10:22:27

原文链接：https://www.gbase.cn/community/post/3946

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在定义好复制服务器以及复制以后，执行 cdr start replicate可以将源库中的初始数据同步到目标服务器。该方法优点是过程简单，缺点是同步速度较慢。适用于数据量小的复制环境。相关的参数是CDR_QUEUEMEM。

同步的操作为在目标服务器上增加源服务器上存在而目标服务器中不存在的行和修改源服务器与目标服务器均存在但不一致的行。针对目标服务器上存在而源服务器中不存在的行的同步策略：

选项	说明
delete	根据参照完整性约束从目标服务器中删除行和从属行
keep	保留目标服务器上的行
merge	在目标服务器上保留行并将其复制到源服务器

测试环境：

两个实例中同时创建一个数据库testdb,并创建表t1-t5,均建有主键约束，然后分别向表中插入原始数据，如下：

表	group1	group2
t1	> select * from t1; a b 1 1 2 2 4 4 5 5	> select * from t1; a b 1 1 2 1 3 3 5 5
t2	> select * from t2; a b 1 1 5 5 2 2 4 4	> select * from t2; a b 1 1 2 1 3 3 5 5
t3	> select * from t3; a b 1 1 2 2 4 4 5 5	> select * from t3; a b 1 1 2 1 3 3 5 5
t4	> select * from t4; a b 1 1 2 2 4 4 5 5 6 6	> select * from t4; a b 1 1 2 1 3 3 5 5 6 6
t5	> select * from t5; a b 1 1 2 2 4 4 5 5	> select * from t5; a b 1 1 2 1 3 3 5 5

定义复制：

cdr define replicate --conflict=ignore   rep_testdb_t1 "testdb@group1:gbasedbt.t1" "select * from t1" "testdb@group2:gbasedbt.t1"  "select * from t1"
cdr define replicate --conflict=ignore   rep_testdb_t2 "testdb@group1:gbasedbt.t2" "select * from t2" "testdb@group2:gbasedbt.t2"  "select * from t2"
cdr define replicate --conflict=ignore   rep_testdb_t3 "testdb@group1:gbasedbt.t3" "select * from t3" "testdb@group2:gbasedbt.t3"  "select * from t3"
cdr define replicate --conflict=ignore   rep_testdb_t4 "testdb@group1:gbasedbt.t4" "select * from t4" "testdb@group2:gbasedbt.t4"  "select * from t4"
cdr define replicate --conflict=ignore  rep_testdb_t5 "testdb@group1:gbasedbt.t5" "select * from t5" "testdb@group2:gbasedbt.t5"   "select * from t5"

此时查看复制状态均为Inactive

[gbasedbt@gbase42 ~]$ cdr list repl rep_testdb_t1 rep_testdb_t2 rep_testdb_t3 rep_testdb_t4 rep_testdb_t5

DEFINED REPLICATES ATTRIBUTES
------------------------------
REPLICATE:        rep_testdb_t1
STATE:            Inactive ON:group2
CONFLICT:         Ignore
FREQUENCY:        immediate
QUEUE SIZE:       0
PARTICIPANT:      testdb:gbasedbt.t1
OPTIONS:          transaction,fullrow
REPLID:           131079 / 0x20007
REPLMODE:         PRIMARY  ON:group2
APPLY-AS:         GBASEDBT ON:group2
REPLTYPE:         Master

REPLICATE:        rep_testdb_t2
STATE:            Inactive ON:group2
CONFLICT:         Ignore
FREQUENCY:        immediate
QUEUE SIZE:       0
PARTICIPANT:      testdb:gbasedbt.t2
OPTIONS:          transaction,fullrow
REPLID:           131080 / 0x20008
REPLMODE:         PRIMARY  ON:group2
APPLY-AS:         GBASEDBT ON:group2
REPLTYPE:         Master

REPLICATE:        rep_testdb_t3
STATE:            Inactive ON:group2
CONFLICT:         Ignore
FREQUENCY:        immediate
QUEUE SIZE:       0
PARTICIPANT:      testdb:gbasedbt.t3
OPTIONS:          transaction,fullrow
REPLID:           131081 / 0x20009
REPLMODE:         PRIMARY  ON:group2
APPLY-AS:         GBASEDBT ON:group2
REPLTYPE:         Master

REPLICATE:        rep_testdb_t4
STATE:            Inactive ON:group2
CONFLICT:         Ignore
FREQUENCY:        immediate
QUEUE SIZE:       0
PARTICIPANT:      testdb:gbasedbt.t4
OPTIONS:          transaction,fullrow
REPLID:           131082 / 0x2000a
REPLMODE:         PRIMARY  ON:group2
APPLY-AS:         GBASEDBT ON:group2
REPLTYPE:         Master

REPLICATE:        rep_testdb_t5
STATE:            Inactive ON:group1
CONFLICT:         Ignore
FREQUENCY:        immediate
QUEUE SIZE:       0
PARTICIPANT:      testdb:gbasedbt.t5
OPTIONS:          transaction,fullrow
REPLID:           131083 / 0x2000b
REPLMODE:         PRIMARY  ON:group1
APPLY-AS:         GBASEDBT ON:group1
REPLTYPE:         Master

启动复制：

cdr start repl rep_testdb_t1 
cdr start repl rep_testdb_t2 --syncdatasource=group1 --extratargetrows=delete
cdr start repl rep_testdb_t3 --syncdatasource=group1 --extratargetrows=keep
cdr start repl rep_testdb_t4 --syncdatasource=group1 --extratargetrows=merge
cdr start repl rep_testdb_t5 --syncdatasource=group1

原始数据对比：

表	启动复制前group1	启动复制前group2	启动复制后group1	启动复制后group2
t1	> select * from t1; a b 1 1 2 2 4 4 5 5	> select * from t1; a b 1 1 2 1 3 3 5 5	> select * from t1; a b 1 1 2 2 4 4 5 5	> select * from t1; a b 1 1 2 1 3 3 5 5
t2	> select * from t2; a b 1 1 5 5 2 2 4 4	> select * from t2; a b 1 1 2 1 3 3 5 5	> select * from t2; a b 1 1 5 5 2 2 4 4	> select * from t2; a b 1 1 2 2 5 5 4 4
t3	> select * from t3; a b 1 1 2 2 4 4 5 5	> select * from t3; a b 1 1 2 1 3 3 5 5	> select * from t3; a b 1 1 2 2 4 4 5 5	> select * from t3; a b 1 1 2 2 3 3 5 5 4 4
t4	> select * from t4; a b 1 1 2 2 4 4 5 5 6 6	> select * from t4; a b 1 1 2 1 3 3 5 5 6 6	> select * from t4; a b 1 1 2 2 4 4 5 5 6 6 3 3	> select * from t4; a b 1 1 2 2 3 3 5 5 6 6 4 4
t5	> select * from t5; a b 1 1 2 2 4 4 5 5	> select * from t5; a b 1 1 2 1 3 3 5 5	> select * from t5; a b 1 1 2 2 4 4 5 5	> select * from t5; a b 1 1 2 2 5 5 4 4