使用Bucardo搭建PG的双主
使用 搭建 pg 双主
2023-06-13 09:18:57 时间
简介
OGG方式实现双主可以参考：
https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
https://www.xmmup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.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，触发器就开始存储有关所有相关的表中哪些行被更改的信息。
环境架构
-- 创建专用网络
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
安装bucardo
https://github.com/bucardo/bucardo
https://bucardo.org/Bucardo/
Bucardo版本5.6.0需要如下组件：
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个节点都需要安装：
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语言组件
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
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
过程：
[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
创建需要同步的库
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上操作。
添加数据库信息
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
添加表
bucardo add all tables
bucardo add all sequences
如果要添加某一个表，则可以bucardo add table tablename
添加群组
bucardo add relgroup relgroup1 sbtest1 sbtest2
bucardo add dbgroup dbgroup1 db1:source db2:target
过程：
[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
添加同步
[postgres@lhrpg1 ~]$ bucardo add sync sync1 relgroup=relgroup1 dbs=dbgroup1
Added sync "sync1"
这里注意的是；同步到表需要主键约束；不然同步是添加不了的。这个是跟触发器有关系；bucardo是建立在触发器之上的。
[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.
查询
[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
启动
#启动
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>
日志：
tailf /var/log/bucardo/log.bucardo
过程：
[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: :
======================================================================
同步测试
[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操作：
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
压测
-- 在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
过程：
[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: :
======================================================================
经过压测，双向数据同步正常！！！
初始化数据
# 关闭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使用的是触发器。
相关文章
- [博客搭建] 通过用户邮箱认证来介绍 django-allauth 的使用思路
- 使用 koa-generator 搭建项目
- 使用Docker快速搭建ELK日志分析平台
- 使用bind搭建内网dns服务
- 使用 Docker 搭建你自己的 RSS 服务（Miniflux）
- 【tornado】tornado路由系统以及加密cookie在项目中的使用详解
- Linux下使用Docker搭建nginx服务
- AWS简单搭建使用EKS一
- 如何使用图片压缩降低COS流量成本？
- Oracle 视图 DBA_ATTRIBUTE_DIM_LVL_CLASS 官方解释，作用，如何使用详细说明
- Oracle 视图 V$SQL_WORKAREA 官方解释，作用，如何使用详细说明
- 在Linux系统中搭建虚拟机环境（linux使用虚拟机）
- 如何使用 Vim 编辑器编辑多个文件
- 如何使用Oracle进行DBF文件的还原（oracle还原dbf）
- Linux中的或运算符：使用技巧简介（linux或运算符）
- 如何使用Linux读取目录（linux 读取目录）
- 实例集群搭建及使用教程（多个redis）
- 使用Java去连接MySQL数据库（java jdbc mysql）
- 使用Win7远程连接Linux服务器的简单方法（win7远程linux）
- MySQL中BIT字段类型的使用方法详解（mysql中bit_）
- 搭建CDH环境，部署并使用mysql（CDH配置使用mysql）
- 用BS架构搭建MySQL数据库服务（bs架构使用mysql）
- 用风力，搭建Redis（winds使用redis）
- 架构使用Oracle DAL 搭建稳健的Web页面架构（oracle dal页面）
- php中simplexml_load_string使用实例分享
- Nginx跨域使用字体文件的配置方法