使用Bucardo配置PostgreSQL14数据库双主同步
斜昊空
一、前言
- 目标是 PostgreSQL 的双主同步
- Bucardo 官方网站的手册语焉不详
- Bucardo 有限制，不同步DDL，也就是表结构变化不会同步，大对象也不会同步，表必须有唯一主键
- Bucardo 是异步同步，如果写入库生效了，突然宕机，会存在数据丢失的可能性
- Bucardo 支持指定同步表
二、环境
- 操作系统：CentOS 8 Stream
- 数据库： PostgreSQL 14
- Bucardo版本：5.6.0
- 禁用防火墙
$ systemctl stop firewalld
$ systemctl disable firewalld
- 禁用 SELinux
$ setenforce 0
$ sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
三、安装软件
- 安装EPEL包
$ yum install epel-release
- 安装 PostgreSQL 14
# Install the repository RPM (for CentOS 8):
$ yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install packages, disable default postgresql module
$ dnf -qy module disable postgresql
$ dnf -y install postgresql14 postgresql14-server
# Initialize your PostgreSQL DB
$ /usr/pgsql-14/bin/postgresql-14-setup initdb
$ systemctl start postgresql-14
# Optional: Configure PostgreSQL to start on boot
$ systemctl enable --now postgresql-14
- 安装 Bucardo 依赖包
$ yum install perl-DBIx-Safe perl-DBD-Pg postgresql14-plperl
- 查看 perl 可用包
$ dnf module list perl
- 安装 perl5
$ dnf module -y install perl:5.26/common
- 下载 Bucardo 源代码，并解压缩
$ wget https://bucardo.org/downloads/Bucardo-5.6.0.tar.gz
$ tar xzf Bucardo-5.6.0.tar.gz
$ cd Bucardo-5.6.0
- 编译 Bucardo
$ perl Makefile.PL
$ make
$ make install
四、配置 Bucardo 运行环境
Bucardo 需要被安装到一个数据库中。为了做双主，我们在两台 PostgreSQL 数据库服务器上都安装 Bucardo，远端的机器为同步的目标机器。
- 修改 PostgreSQL 监听，允许非本机访问
$ vim /var/lib/pgsql/14/data/postgresql.conf
注意修改 listen_addresses 属性
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 500 # (change requires restart)
- 配置 PostgreSQL 允许 bucardo 无密码访问
$ vim /var/lib/pgsql/14/data/pg_hba.conf
注意增加的配置
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all bucardo 127.0.0.1/32 trust
host all bucardo 192.168.7.218/32 trust
host all bucardo 192.168.7.219/32 trust
# IPv6 local connections:
host all all ::1/128 scram-sha-256
host all bucardo ::1/128 trust
注意：bucardo 这一行一定要设置成 trust，也就是信任 127.0.0.1 的任何访问，不会校验密码。同时也配置本机内网IP和对端主机的 Bucardo 账号访问不需要密码。
否则在执行初始化时会出现以下错误：
DBI connect('dbname=bucardo;host=localhost;port=5432','bucardo',...) failed: connection to server at "localhost" (127.0.0.1), port 5432 failed: fe_sendauth: no password supplied at /usr/bin/bucardo line 9162.
这是因为在/usr/bin/bucardo的9162行，连接数据库的时候，账号名称默认是bucardo，密码为空字符串。
$dbh = DBI->connect($BDSN, 'bucardo', '', {AutoCommit=>0,RaiseError=>1,PrintError=>0});
- 配置完成，重启数据库
$ systemctl restart postgresql-14
- 在 PostgreSQL 中配置 Bucardo 需要的空数据库和访问数据库所需的账户名
$ su - postgres
postgres@localhost ~]$ psql
psql (14.5 (Ubuntu 14.5-1.pgdg22.04+1))
Type "help" for help.
postgres=# create user bucardo with superuser password 'bucardo';
CREATE ROLE
postgres=# create database bucardo with owner = bucardo;
CREATE DATABASE
- 创建PID文件目录和日志目录，并设置好权限
# 创建 PID 目录
$ mkdir /var/run/bucardo
$ chown -R bucardo:bucardo /var/run/bucardo
# 创建日志目录
$ mkdir /var/log/bucardo
$ chown -R bucardo:bucardo /var/log/bucardo
- 在操作系统创建 bucardo 用户，添加
.pgpass文件
$ useradd bucardo
$ su - bucardo
$ echo "*:5432:*:bucardo:bucardo" > .pgpass
$ chmod 600 .pgpass
重要：bucardo 的所有操作都需要做 Linux 系统用户 bucardo 下完成，否则会出现类似于以下的错误
DBI connect(‘dbname=bucardo’,‘bucardo’,…) failed: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: Peer authentication failed for user “bucardo” at /usr/local/bin/bucardo line 310.
- 在系统用户 bucardo 下完成 Bucardo 初始化（如无特别说明，以下操作均在 bucardo 用户下完成）
$ bucardo install -h 192.168.7.218
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: 192.168.7.218
2. Port: 5432
3. User: bucardo
4. Database: bucardo
5. PID directory: /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: p
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
- 检查 Bucardo 所有参数
$ 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
- 检查 Bucardo 服务状态
$ bucardo status
PID of Bucardo MCP: 50914
No syncs have been created yet.
- 检查数据库表状态
$ su - postgres
postgres@localhost ~]$ psql
psql (14.5 (Ubuntu 14.5-1.pgdg22.04+1))
Type "help" for help.
postgres=# \c bucardo
bucardo=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
---------+-------------------------------+----------+-------------------+-------------------+----------
bucardo | bucardo_config | table | | |
bucardo | bucardo_custom_trigger | table | | |
bucardo | bucardo_custom_trigger_id_seq | sequence | | |
bucardo | bucardo_log_message | table | | |
bucardo | bucardo_rate | table | | |
bucardo | clone | table | | |
bucardo | clone_id_seq | sequence | | |
bucardo | customcode | table | | |
bucardo | customcode_id_seq | sequence | | |
bucardo | customcode_map | table | | |
bucardo | customcols | table | | |
bucardo | customcols_id_seq | sequence | | |
bucardo | customname | table | | |
bucardo | customname_id_seq | sequence | | |
bucardo | db | table | | |
bucardo | db_connlog | table | | |
bucardo | dbgroup | table | | |
bucardo | dbmap | table | | |
bucardo | dbrun | table | | |
bucardo | goat | table | | |
bucardo | goat_id_seq | sequence | | |
bucardo | herd | table | | |
bucardo | herdmap | table | | |
bucardo | sync | table | | |
bucardo | syncrun | table | | |
bucardo | upgrade_log | table | | |
(26 rows)
五、初始化业务库表
按业务要求创建业务库表，此处提供测试用表结构
CREATE TABLE public.bucardo_test_20221012 (
id bigserial NOT NULL,
std_name varchar(10) NULL,
grade int4 NULL,
join_date date NULL,
attrs jsonb NULL,
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT bucardo_test_20221012_pk PRIMARY KEY (id)
);
六、配置业务库表同步
-
基本概念
-
bucardo add database 设置同步的数据源
-
bucardo add dbgroup 绑定数据源到一个组
-
bucardo add all 添加要同步的表和序列（PostgreSQL中必须同步，自增字段类型serial系列存储的值）
-
bucardo add sync 创建同步任务，并指定冲突处理策略
-
所有的配置完成后记得
bucardo stop停止服务再使用bucardo start启动服务，使新参数生效 -
bucardo 是依赖主键做同步，只要有变化就会操作同步
-
-
在第一台主机配置
$ bucardo add database db218 dbname=aps host=192.168.7.218 port=5432 user=bucardo
$ bucardo add database db219 dbname=aps host=192.168.7.219 port=5432 user=bucardo
$ bucardo add dbgroup grp1 db218:source db219:target
$ bucardo add all tables db=db218 --relgroup=relg_aps --verbose
$ bucardo add all sequences db=db218 --relgroup=relg_aps --verbose
- 在第二台主机配置
$ bucardo add database db218 dbname=aps host=192.168.7.218 port=5432 user=bucardo
$ bucardo add database db219 dbname=aps host=192.168.7.219 port=5432 user=bucardo
$ bucardo add dbgroup grp1 db219:source db218:target
$ bucardo add all tables db=db219 --relgroup=relg_aps --verbose
$ bucardo add all sequences db=db219 --relgroup=relg_aps --verbose
- 在两台主机上分别执行添加同步的命令
$ bucardo add sync dbsync relgroup=relg_aps dbs=grp1 conflict_strategy=bucardo_latest
- 查看 Bucardo 各种业务库表同步配置
[bucardo@localhost ~]$ bucardo list dbs
Database: db218 Status: active Conn: psql -p 5432 -U bucardo -d aps -h 192.168.7.218
Database: db219 Status: active Conn: psql -p 5432 -U bucardo -d aps -h 192.168.7.218
[bucardo@localhost ~]$ bucardo list dbgroups
dbgroup: grp1 Members: db218:source db219:target
[bucardo@localhost ~]$ bucardo list relgroups
Relgroup: relg_aps Members:
Used in syncs: dbsync
[bucardo@localhost ~]$ bucardo list syncs
Sync "dbsync" Relgroup "relg_aps" DB group "grp1" db218:source db219:target [Active]
[bucardo@localhost ~]$ bucardo list tables
1. Table: public.bucardo_test_20221012 DB: db218 PK: id (bigint)
- 查看 Bucardo 运行状态
$ bucardo status
PID of Bucardo MCP: 47874
Name State Last good Time Last I/D Last bad Time
========+========+============+=======+===========+===========+=======
dbsync | Good | 02:40:24 | 4s | 0/0 | none |
七、参考资料
类似资料：