执行bucardo add db命令;记录同步数据库的连接信息
[postgres@node3 ~]$ bucardo add db db1 host=http://www.likecs.com/tmp dbname=master user=postgres Added database "db1" [postgres@node3 ~]$ bucardo add db db2 host=http://www.likecs.com/tmp dbname=slave user=postgres Added database "db2" #这里推荐使用unix套接字;若元数据库跟需要复制的数据不是在同一台;则需要使用TCP/IP连接的方式。 bucardo add db db1 host=IP port=PGPORT dbname=PGDB user=PGUSRR password=PGPASSWD 4.2、add dbgroup添加 dbgroup 数据库组; 在一套复制系统中可以有多个源数据库、目标数据库。指定哪个数据库是源数据库,哪个是目标数据库
[postgres@node3 ~]$ bucardo add dbgroup dbgrp01 db1:source db2:target Created dbgroup "dbgrp01" Added database "db1" to dbgroup "dbgrp01" as source Added database "db2" to dbgroup "dbgrp01" as target 4.3、add relgroup # 创建复制表tbl_lottu01 [postgres@node3 ~]$ psql master postgres -c "create table tbl_lottu01(id int primary key, info text, reg_time timestamp)" CREATE TABLE [postgres@node3 ~]$ psql slave postgres -c "create table tbl_lottu01(id int primary key, info text)" CREATE TABLE # 添加 relgroup 复制集;即要复制的表、序列的集合 [postgres@node3 ~]$ bucardo add relgroup relgrp01 tbl_lottu01 Created relgroup "relgrp01" Added the following tables or sequences: public.tbl_lottu01 (DB: db1) The following tables or sequences are now part of the relgroup "relgrp01": public.tbl_lottu01 4.4、Add syncs添加同步;在创建同步需要指定dbgroup 和relgroup
[postgres@node3 ~]$ bucardo add sync sync01 relgroup=relgrp01 dbgroup=dbgrp01 conflict_strategy=bucardo_latest 2020-11-03 17:08:51.494 CST [6988] LOG: Starting validate_sync for sync01 2020-11-03 17:08:51.494 CST [6988] CONTEXT: PL/Perl function "validate_sync" SQL statement "SELECT validate_sync('sync01')" PL/Perl function "validate_sync" 2020-11-03 17:08:51.494 CST [6988] STATEMENT: INSERT INTO bucardo.sync (herd,name,dbs,conflict_strategy) VALUES ($1,$2,$3,$4) 2020-11-03 17:08:51.619 CST [6988] LOG: Ending validate_sync for sync01 2020-11-03 17:08:51.619 CST [6988] CONTEXT: PL/Perl function "validate_sync" SQL statement "SELECT validate_sync('sync01')" PL/Perl function "validate_sync" 2020-11-03 17:08:51.619 CST [6988] STATEMENT: INSERT INTO bucardo.sync (herd,name,dbs,conflict_strategy) VALUES ($1,$2,$3,$4) Added sync "sync01"由于bucardo是解决PostgreSQL双主/多主的方案。可能会存在主键/唯一约束的冲突
"conflict_strategy" The conflict resolution strategy to use in the sync. Supported values: "bucardo_source" The rows on the "source" database always "win". In other words, in a conflict, Bucardo copies rows from source to target. "bucardo_target" The rows on the "target" database always win. "bucardo_skip" Any conflicting rows are simply not replicated. Not recommended for most cases. "bucardo_random" Each database has an equal chance of winning each time. This is the default. "bucardo_latest" The row that was most recently changed wins. "bucardo_abort" The sync is aborted on a conflict. 4.5、test sync启动bucardo服务
[postgres@node3 ~]$ bucardo start Checking for existing processes Starting Bucardo [postgres@node3 ~]$ 2020-11-03 17:21:07.440 CST [7178] LOG: Starting validate_sync for sync01 2020-11-03 17:21:07.440 CST [7178] CONTEXT: PL/Perl function "validate_sync" 2020-11-03 17:21:07.440 CST [7178] STATEMENT: SELECT validate_sync('sync01') 2020-11-03 17:21:07.494 CST [7178] LOG: Ending validate_sync for sync01 2020-11-03 17:21:07.494 CST [7178] CONTEXT: PL/Perl function "validate_sync" 2020-11-03 17:21:07.494 CST [7178] STATEMENT: SELECT validate_sync('sync01') [postgres@node3 ~]$ ps -ef | grep bucardo postgres 7178 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle postgres 7184 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle postgres 7187 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle postgres 7191 6376 0 17:21 ? 00:00:00 postgres: bucardo bucardo [local] idle postgres 7196 7066 0 17:21 pts/1 00:00:00 grep bucardo