使用Bucardo搭建PG的双主
👉 本文共约2059个字，系统预计阅读时间或需8分钟。
简介
OGG方式实现双主可以参考：
https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
Bucardo开源工具是一个perl语言编写的程序，其依赖PG数据库的plperl语言组件，进而严格依赖perl的版本（数据库服务器安装的perl大版本号必须和官方说明的perl版本严格一致，小版本号不限制）。
Bucardo是PostgreSQL数据库中实现双向同步的软件，可以实现PostgreSQL数据库的双master的方案，不过bucardo中的同步都是异步的，它是通过触发器记录变化，程序是perl写的。Bucardo可以实现postgresql的多主复制、主从同步，甚至可以以postgresql为源库，可以和oracle、mysql、mongodb等很多数据库进行数据异步同步。
而pg原生的流复制（stream replication）虽可以同步，但只能单向同步，而且备库只能是只读操作，而bucardo不支持DDL的同步。
Bucardo 是基于表复制的系统，通过触发器记录变化，同步的表必须有主键，不能同步DDL语句(truncate可以同步)。Bucardo 可以实现PostgreSQL数据库的双master/多master的方案。Bucardo的核心是一个Perl守护进程，它侦听通知请求并对其进行操作，方法是连接到远程数据库并来回复制数据。
守护进程需要的所有特定信息都存储在主bucardo数据库中，包括复制所涉及的所有数据库的列表以及如何到达这些数据库、要复制的所有表以及如何复制每个表。
运行Bucardo的第一步是向主Bucardo数据库添加两个或更多数据库。
完成此操作后，将添加关于要复制哪些表的信息以及表的任何分组。然后添加同步。
同步被称为复制操作，将一组特定的表从一台服务器复制到另一台服务器或一组服务器。
一旦设置好Bucardo，触发器就开始存储有关所有相关的表中哪些行被更改的信息。
对于多主机，过程是这样的：
1、对表进行更改并记录在bucardo_delta表中。
2、向主Bucardo守护进程发送通知，让它知道表已经更改。
3、守护进程通知控制器进行同步，然后返回侦听。
4、控制器创建一个“kid”来处理复制，或者通知已经存在的复制。
5、孩子开始一个新的事务，禁用相关表上的触发器和规则。
6、然后，它收集自上次复制以来哪些行发生了更改的列表，然后比较这两个行以确定应该做什么。
7、如果存在冲突，则会运行标准冲突处理程序，或者为每个表设置的自定义处理程序来对内容进行排序。
8、触发器和规则被重新启用，事务被提交。
9、如果事务失败，则运行任何自定义异常处理程序。
10、子程序向控制器发出它已经完成的信号。
环境架构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 创建专用网络 docker network create --subnet=172.72.6.0/24 pg-network -- PG 1 docker rm -f lhrpg1 docker run -d --name lhrpg1 -h lhrpg1 \ -p 64326:5432 --net=pg-network --ip 172.72.6.26 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init -- PG 2 docker rm -f lhrpg2 docker run -d --name lhrpg2 -h lhrpg2 \ -p 64327:5432 --net=pg-network --ip 172.72.6.27 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgall:2.0 \ /usr/sbin/init |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
安装bucardo
https://github.com/bucardo/bucardo
Bucardo版本5.6.0需要如下组件：
1 2 3 4 5 | build, test, and install Perl 5 (at least 5.8.3) build, test, and install PostgreSQL (at least 8.2) build, test, and install the DBI module (at least 1.51) build, test, and install the DBD::Pg module (at least 2.0.0) build, test, and install the DBIx::Safe module (at least 1.2.4) |
在2个节点都需要安装：
1 2 3 4 5 6 | yum install -y perl-5* perl-DBI perl-DBIx-Safe perl-DBD-Pg postgresql13-plperl wget https://bucardo.org/downloads/Bucardo-5.6.0.tar.gz tar -xzvf Bucardo-5.6.0.tar.gz cd Bucardo-5.6.0/ perl Makefile.PL make && make install |
安装plperl语言组件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create language plperlu; create language plperl; postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plperl | 1.0 | pg_catalog | PL/Perl procedural language plperlu | 1.0 | pg_catalog | PL/PerlU untrusted procedural language plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (5 rows) |
初始化bucardo
1 2 3 4 5 6 7 | mkdir -p /var/run/bucardo chmod 777 /var/run/bucardo mkdir /var/log/bucardo/ chmod 777 /var/log/bucardo bucardo install -h 127.0.0.1 -p 5432 -U postgres -d postgres bucardo show all |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | [root@lhrpg1 ~]# mkdir -p /var/run/bucardo [root@lhrpg1 ~]# bucardo install -h 127.0.0.1 -p 5432 -U postgres -d postgres This will install the bucardo database into an existing Postgres cluster. Postgres must have been compiled with Perl support, and you must connect as a superuser Current connection settings: 1. Host: 127.0.0.1 2. Port: 5432 3. User: postgres 4. Database: postgres 5. PID directory: /var/run/bucardo Enter a number to change it, P to proceed, or Q to quit: P Creating superuser 'bucardo' Attempting to create and populate the bucardo database and schema Database creation is complete Updated configuration setting "piddir" Installation is now complete. If you see errors or need help, please email bucardo-general@bucardo.org You may want to check over the configuration variables next, by running: bucardo show all Change any setting by using: bucardo set foo=bar [root@lhrpg1 ~]# su - postgres Last login: Thu Mar 24 10:05:22 CST 2022 on pts/0 [postgres@lhrpg1 ~]$ psql psql (13.6) Type "help" for help. postgres=# \d List of relations Schema | Name | Type | Owner --------+--------------------+------+---------- public | pg_stat_statements | view | postgres (1 row) postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- bucardo | bucardo | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) [postgres@lhrpg1 ~]$ bucardo show all autosync_ddl = newcol bucardo_initial_version = 5.6.0 bucardo_vac = 1 bucardo_version = 5.6.0 ctl_checkonkids_time = 10 ctl_createkid_time = 0.5 ctl_sleep = 0.2 default_conflict_strategy = bucardo_latest default_email_from = nobody@example.com default_email_host = localhost default_email_port = 25 default_email_to = nobody@example.com email_auth_pass = email_auth_user = email_debug_file = endsync_sleep = 1.0 flatfile_dir = . host_safety_check = isolation_level = repeatable read kid_deadlock_sleep = 0.5 kid_nodeltarows_sleep = 0.5 kid_pingtime = 60 kid_restart_sleep = 1 kid_serial_sleep = 0.5 kid_sleep = 0.5 log_conflict_file = bucardo_conflict.log log_level = normal log_microsecond = 0 log_showlevel = 0 log_showline = 0 log_showpid = 1 log_showsyncname = 1 log_showtime = 3 log_timer_format = mcp_dbproblem_sleep = 15 mcp_loop_sleep = 0.2 mcp_pingtime = 60 mcp_vactime = 60 piddir = /var/run/bucardo quick_delta_check = 1 reason_file = bucardo.restart.reason.txt reload_config_timeout = 30 semaphore_table = bucardo_status statement_chunk_size = 6000 stats_script_url = http://www.bucardo.org/ stopfile = fullstopbucardo syslog_facility = log_local1 tcp_keepalives_count = 0 tcp_keepalives_idle = 0 tcp_keepalives_interval = 0 vac_run = 30 vac_sleep = 120 warning_file = bucardo.warning.log |
创建需要同步的库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | create database lhrdb; sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \ --pgsql-host=172.72.6.26 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=10000 --tables=2 --threads=80 \ --events=999999999 --time=60 prepare -- pg2只是建表，注意：table-size sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \ --pgsql-host=172.72.6.27 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=0 --tables=2 --threads=80 \ --events=999999999 --time=60 prepare -- 节点2 pg_dump -h 172.72.6.26 -a lhrdb > lhrdb.sql psql -h 172.72.6.27 -d lhrdb < lhrdb.sql |
配置bucardo单向同步
首先，配置pg1到pg2的单向同步。以下步骤在节点1上操作。
添加数据库信息
1 2 3 4 5 | bucardo add db db1 dbname=lhrdb host=172.72.6.26 user=postgres password=lhr bucardo add db db2 dbname=lhrdb host=172.72.6.27 user=postgres password=lhr bucardo list all |
添加表
1 2 | bucardo add all tables bucardo add all sequences |
如果要添加某一个表，则可以bucardo add table tablename
添加群组
1 2 | bucardo add relgroup relgroup1 sbtest1 sbtest2 bucardo add dbgroup dbgroup1 db1:source db2:target |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 | [postgres@lhrpg1 ~]$ bucardo add all tables bucardo add all sequencesNew tables added: 2 [postgres@lhrpg1 ~]$ bucardo add all sequences New sequences added: 2 [postgres@lhrpg1 ~]$ bucardo add relgroup relgroup1 sbtest1 sbtest2 Relgroup "relgroup1" already exists The following tables or sequences are now part of the relgroup "relgroup1": public.sbtest1 public.sbtest2 [postgres@lhrpg1 ~]$ [postgres@lhrpg1 ~]$ bucardo add dbgroup dbgroup1 db1:source db2:target Added database "db1" to dbgroup "dbgroup1" as source Added database "db2" to dbgroup "dbgroup1" as target |
添加同步
1 2 | [postgres@lhrpg1 ~]$ bucardo add sync sync1 relgroup=relgroup1 dbs=dbgroup1 Added sync "sync1" |
这里注意的是；同步到表需要主键约束；不然同步是添加不了的。这个是跟触发器有关系；bucardo是建立在触发器之上的。
1 2 3 | [postgres@lhrpg1 ~]$ bucardo add sync sync1 relgroup=relgroup1 dbs=dbgroup1 Failed to add sync: DBD::Pg::st execute failed: ERROR: Table "public.pgbench_history" must specify a primary key! at line 119. at line 30. CONTEXT: PL/Perl function "validate_sync" at /usr/local/bin/bucardo line 4670. |
查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [postgres@lhrpg1 ~]$ bucardo list all -- dbgroups: dbgroup: dbgroup1 Members: db1:source db2:target -- databases: Database: db1 Status: active Conn: psql -p 5432 -U postgres -d lhrdb -h 172.72.6.26 Database: db2 Status: active Conn: psql -p 5432 -U postgres -d lhrdb -h 172.72.6.27 -- relgroup: Relgroup: relgroup1 DB: db1 Members: public.sbtest1, public.sbtest2 Used in syncs: sync1 -- syncs: Sync "sync1" Relgroup "relgroup1" DB group "dbgroup1" db1:source db2:target [Active] -- tables: 1. Table: public.sbtest1 DB: db1 PK: id (integer) 2. Table: public.sbtest2 DB: db1 PK: id (integer) -- sequences: Sequence: public.sbtest1_id_seq DB: db1 Sequence: public.sbtest2_id_seq DB: db1 |
启动
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | #启动 bucardo start #查看状态 bucardo status #重启 bucardo restart #停止 bucardo stop bucardo list syncs bucardo list dbgroups bucardo list dbs bucardo list tables bucardo list sequences bucardo list relgroups bucardo status bucardo status <Name> |
日志：
1 | tailf /var/log/bucardo/log.bucardo |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | [postgres@lhrpg1 ~]$ bucardo start Checking for existing processes Starting Bucardo [postgres@lhrpg1 ~]$ bucardo status PID of Bucardo MCP: 11815 Name State Last good Time Last I/D Last bad Time =======+========+============+=======+===========+===========+======= sync1 | Good | 12:56:11 | 18s | 0/0 | none | [postgres@lhrpg1 ~]$ bucardo status sync1 ====================================================================== Last good : Mar 24, 2022 12:56:11 (time to run: 1s) Rows deleted/inserted : 0 / 0 Sync name : sync1 Current state : Good Source relgroup/database : relgroup1 / db1 Tables in sync : 2 Status : Active Check time : None Overdue time : 00:00:00 Expired time : 00:00:00 Stayalive/Kidsalive : Yes / Yes Rebuild index : No Autokick : Yes Onetimecopy : No Post-copy analyze : Yes Last error: : ====================================================================== |
同步测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | [postgres@lhrpg1 ~]$ psql -d lhrdb psql (13.6) Type "help" for help. lhrdb=# delete from sbtest1 where id<=10; DELETE 10 lhrdb=# delete from sbtest2 where id<=20; DELETE 20 lhrdb=# select count(*) from sbtest1; count ------- 9990 (1 row) lhrdb=# select count(*) from sbtest2; count ------- 9980 (1 row) lhrdb=# exit [postgres@lhrpg2 ~]$ psql -d lhrdb psql (13.6) Type "help" for help. lhrdb=# select count(*) from sbtest1; count ------- 9990 (1 row) lhrdb=# select count(*) from sbtest2; count ------- 9980 (1 row) |
配置双向同步（双主）
在节点2操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | bucardo add db db1 dbname=lhrdb host=172.72.6.27 user=postgres password=lhr bucardo add db db2 dbname=lhrdb host=172.72.6.26 user=postgres password=lhr bucardo add all tables bucardo add all sequences bucardo add relgroup relgroup1 sbtest1 sbtest2 bucardo add dbgroup dbgroup1 db1:source db2:target bucardo add sync sync1 relgroup=relgroup1 dbs=dbgroup1 bucardo status bucardo start bucardo list [postgres@lhrpg2 ~]$ bucardo list all -- dbgroups: dbgroup: dbgroup1 Members: db1:source db2:target -- databases: Database: db1 Status: active Conn: psql -p 5432 -U postgres -d lhrdb -h 172.72.6.27 Database: db2 Status: active Conn: psql -p 5432 -U postgres -d lhrdb -h 172.72.6.26 -- relgroup: Relgroup: relgroup1 DB: db1 Members: public.sbtest1, public.sbtest2 Used in syncs: sync1 -- syncs: Sync "sync1" Relgroup "relgroup1" DB group "dbgroup1" db1:source db2:target [Active] -- tables: 1. Table: public.sbtest1 DB: db1 PK: id (integer) 2. Table: public.sbtest2 DB: db1 PK: id (integer) -- sequences: Sequence: public.sbtest1_id_seq DB: db1 Sequence: public.sbtest2_id_seq DB: db1 |
压测
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- 在PG1压测 sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ --pgsql-host=172.72.6.26 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=1000 --tables=2 --threads=20 \ --events=999999999 --time=10 --report-interval=1 \ --db-ps-mode=disable --forced-shutdown=1 run -- 在PG2压测 sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ --pgsql-host=172.72.6.27 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=1000 --tables=2 --threads=20 \ --events=999999999 --time=10 --report-interval=1 \ --db-ps-mode=disable --forced-shutdown=1 run |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | [postgres@lhrpg1 ~]$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ > --pgsql-host=172.72.6.26 --pgsql-port=5432 \ > --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ > --table-size=1000 --tables=2 --threads=20 \ > --events=999999999 --time=10 --report-interval=1 \ > --db-ps-mode=disable --forced-shutdown=1 run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 20 Report intermediate results every 1 second(s) Initializing random number generator from current time Forcing shutdown in 11 seconds Initializing worker threads... Threads started! [ 1s ] thds: 20 tps: 11.93 qps: 554.97 (r/w/o: 445.57/62.66/46.74) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 20 tps: 24.02 qps: 560.52 (r/w/o: 406.38/90.08/64.06) lat (ms,95%): 1533.66 err/s: 5.00 reconn/s: 0.00 [ 3s ] thds: 20 tps: 1.00 qps: 68.00 (r/w/o: 56.00/7.00/5.00) lat (ms,95%): 1109.09 err/s: 3.00 reconn/s: 0.00 [ 4s ] thds: 20 tps: 16.00 qps: 415.05 (r/w/o: 294.04/75.01/46.01) lat (ms,95%): 3706.08 err/s: 5.00 reconn/s: 0.00 [ 5s ] thds: 20 tps: 7.00 qps: 165.99 (r/w/o: 125.99/23.00/17.00) lat (ms,95%): 3911.79 err/s: 2.00 reconn/s: 0.00 [ 6s ] thds: 20 tps: 5.00 qps: 134.00 (r/w/o: 98.00/22.00/14.00) lat (ms,95%): 4943.53 err/s: 2.00 reconn/s: 0.00 [ 7s ] thds: 20 tps: 12.00 qps: 291.02 (r/w/o: 210.02/43.00/38.00) lat (ms,95%): 4768.67 err/s: 3.00 reconn/s: 0.00 [ 8s ] thds: 20 tps: 32.00 qps: 794.98 (r/w/o: 573.98/125.00/96.00) lat (ms,95%): 3448.53 err/s: 9.00 reconn/s: 0.00 [ 9s ] thds: 20 tps: 33.00 qps: 755.01 (r/w/o: 532.01/125.00/98.00) lat (ms,95%): 1479.41 err/s: 5.00 reconn/s: 0.00 [ 10s ] thds: 20 tps: 13.00 qps: 328.99 (r/w/o: 237.99/49.00/42.00) lat (ms,95%): 2493.86 err/s: 4.00 reconn/s: 0.00 FATAL: The --max-time limit has expired, forcing shutdown... [ 11s ] thds: 20 tps: 4.00 qps: 32.00 (r/w/o: 14.00/13.00/5.00) lat (ms,95%): 3574.99 err/s: 1.00 reconn/s: 0.00 SQL statistics: queries performed: read: 2996 write: 636 other: 473 total: 4105 transactions: 159 (14.44 per sec.) queries: 4105 (372.90 per sec.) ignored errors: 40 (3.63 per sec.) reconnects: 0 (0.00 per sec.) Number of unfinished transactions on forced shutdown: 20 General statistics: total time: 11.0041s total number of events: 159 Latency (ms): min: 16.53 avg: 1202.92 max: 6225.98 95th percentile: 3706.08 sum: 191264.29 Threads fairness: events (avg/stddev): 8.9500/2.31 execution time (avg/stddev): 9.5632/1.93 [postgres@lhrpg1 ~]$ bucardo status PID of Bucardo MCP: 11815 Name State Last good Time Last I/D Last bad Time =======+========+============+=======+===========+===========+======= sync1 | Good | 14:02:03 | 27s | 5/5 | none | [postgres@lhrpg1 ~]$ bucardo status sync1 ====================================================================== Last good : Mar 24, 2022 14:02:02 (time to run: 1s) Rows deleted/inserted : 5 / 5 Sync name : sync1 Current state : Good Source relgroup/database : relgroup1 / db1 Tables in sync : 2 Status : Active Check time : None Overdue time : 00:00:00 Expired time : 00:00:00 Stayalive/Kidsalive : Yes / Yes Rebuild index : No Autokick : Yes Onetimecopy : No Post-copy analyze : Yes Last error: : ====================================================================== |
经过压测，双向数据同步正常！！！
初始化数据
1 2 3 4 5 6 7 8 9 10 | # 关闭bucardo服务 bucardo stop # 更新同步为增量同步； bucardo update sync sync1 onetimecopy=2 "onetimecopy" 0: 关闭 1: fullcopy；采用delete/copy的方式 2: 增量copy； # 启动bucardo服务 bucardo start |
总结
1、Bucardo 是基于表复制的系统，通过触发器记录变化，同步的表必须有主键，不能同步DDL语句(truncate可以同步)。
2、Bucardo可以安装在一台单独的机器上，类似OGG的远程replicate或OGG的微服务架构，不同的是，OGG使用的是日志抽取，而Bucardo使用的是触发器。
