PostgreSQL多主同步指的是在多个主数据库之间进行数据同步的过程。这种配置使得多个主数据库可以同时读写，并保持数据的一致性和可靠性。本文采用Bucardo对PostgreSQL数据库进行多主复制同步，由于官方2020-02-28后再无更新，请谨慎使用。未进行性能、及时性、更新频率进行测试。

安装风险

不建议在物理机上操作，perl版本要求为5.36，可能由于perl版本问题无法安装成功且操作系统环境被破坏。猫子就是在物理机上安装Bucardo失败。

PostgreSQL安装

本文采用docker的方式

  postgres:
    image: postgres:15.4
    container_name: postgres
    restart: on-failure:3
    volumes:
      - /data/postgres:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    healthcheck:
      test: [ "CMD", "pg_isready" ]
      interval: 10s
      timeout: 5s
      retries: 5
    environment:
      - POSTGRES_PASSWORD=密码
      - POSTGRES_USER=超级用户名称
      - POSTGRES_DB=数据库

运行数据库容器

docker-compose up -d

数据库节点清单

Hostname      IP Address
---------------------------
postgres01    192.168.0.11
postgres02    192.168.0.12
postgres03    192.168.0.13
postgres04    192.168.0.14

注：我们这里是假设的节点IP，请按自己的实际情况分配数据库IP。

并在192.168.0.11安装Bucardo。

安装Bucardo

主节点安装

在192.168.0.11运行，进入数据库容器终端

docker exec -it postgres bash

在容器中安装依赖

apt update
apt install postgresql-plperl-15 vim wget make build-essential gcc libdbix-safe-perl

安装perl依赖

perl -MCPAN -e 'install DBI'
perl -MCPAN -e 'install DBD::Pg'

安装DBD::Pg时会提示目录不存在，创建目录再次运行即可。

安装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
perl Makefile.PL
make
sudo make install

创建相关目录

mkdir -p /var/run/bucardo /var/log/bucardo
touch /var/log/bucardo/log.bucardo

初始化Bucardo

bucardo install --dbhost=192.168.0.11 --dbname=数据库 --dbuser=超级用户名称

在提示中输入p，回车，多次输入密码，提示以下内容即安装成功

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.0.11
2. Port: 5432
3. User: 超级用户名称
4. Database: 数据库
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

其他节点安装

在192.168.0.12，192.168.0.13，192.168.0.14运行，进入数据库容器终端

docker exec -it postgres bash

在容器中安装依赖

apt update
apt install postgresql-plperl-15 vim

配置bucardo用户免密码登录

su - postgres
cd data
vi pg_hba.conf

加入以下内容（添加内容请自行比对）

# "local" is for Unix domain socket connections only
local   all             all                                     trust
local   all             bucardo                                 trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             bucardo         192.168.0.11/24         trust

创建bucardo的用户名和数据库

psql -U 超级用户名称 数据库

执行SQL

CREATE USER bucardo WITH SUPERUSER;
CREATE DATABASE bucardo OWNER bucardo;

配置复制同步

在192.168.0.11上执行

将需要同步的节点数据库加入bucardo

bucardo add database server1 dbname=test
bucardo add database server2 dbname=test host=192.168.0.12
bucardo add database server3 dbname=test host=192.168.0.13
bucardo add database server4 dbname=test host=192.168.0.14

test为要同步的数据库

提示Added database "server3"为成功。

设置为所有表同步到分组

bucardo add all tables --her=testdbSrv1 db=server1
bucardo add all tables --her=testdbSrv2 db=server2
bucardo add all tables --her=testdbSrv3 db=server3
bucardo add all tables --her=testdbSrv4 db=server4

提示以下内容为成功

Creating relgroup: testdbSrv3
Added table public.extensions to relgroup testdbSrv3
New tables added: 1

查看分组列表

bucardo list relgroup
Relgroup: testdbSrv1  DB: server1  Members: public.extensions
  Used in syncs: testdbSrv1
Relgroup: testdbSrv2  DB: server2  Members: public.extensions
  Used in syncs: testdbSrv2
Relgroup: testdbSrv3  DB: server3  Members: public.extensions
  Used in syncs: testdbSrv3
Relgroup: testdbSrv4  DB: server4  Members: public.extensions
  Used in syncs: testdbSrv4

设置同步

bucardo add sync testdbSrv1 relgroup=testdbSrv1 db=server1,server2,server3,server4
bucardo add sync testdbSrv2 relgroup=testdbSrv2 db=server2,server1,server3,server4
bucardo add sync testdbSrv3 relgroup=testdbSrv3 db=server3,server2,server1,server4
bucardo add sync testdbSrv4 relgroup=testdbSrv4 db=server4,server3,server2,server1

注：db参数的第一个节点为数据源，后面为同步数据的目标节点。

查看设置的同步清单

bucardo list sync
Sync "testdbSrv1"  Relgroup "testdbSrv1" [Active]
  DB group "testdbSrv1" server1:source server2:target server3:target server4:target
Sync "testdbSrv2"  Relgroup "testdbSrv2" [Active]
  DB group "testdbSrv2" server1:target server2:source server3:target server4:target
Sync "testdbSrv3"  Relgroup "testdbSrv3" [Active]
  DB group "testdbSrv3" server1:target server2:target server3:source server4:target
Sync "testdbSrv4"  Relgroup "testdbSrv4" [Active]
  DB group "testdbSrv4" server1:target server2:target server3:target server4:source

执行同步

bucardo restart sync
Creating /var/run/bucardo/fullstopbucardo ... Done
Checking for existing processes
Removing file "/var/run/bucardo/fullstopbucardo"
Starting Bucardo

查看同步状态

bucardo status
PID of Bucardo MCP: 3723
 Name         State              Last good    Time    Last I/D    Last bad    Time  
============+==================+============+=======+===========+===========+=======
 testdbSrv1 | No records found |            |       |           |           |       
 testdbSrv2 | No records found |            |       |           |           |       
 testdbSrv3 | No records found |            |       |           |           |       
 testdbSrv4 | No records found |            |       |           |           |

测试

在各个节点进行数据库的增删改操作，查看其他节点是否正常同步。

相关文档

https://bucardo.org/Bucardo/installation/