PostgreSQL数据库双主同步

使用Bucardo搭建PG数据库同步

1、Bucardo介绍

Bucardo是针对PostgreSQL数据库开发的同步软件，可以实现PostgreSQL数据库的主从、主主、多主同步方案，不过bucardo中的同步都是异步的，它是通过触发器记录变化，甚至可以以postgresql为源库，可以和oracle、mysql、mongodb等很多数据库进行数据异步同步。

2、Bucardo搭建前准备

2.1搭建环境

版本要求：

Perl 5 (at least 5.8.3)

Postgres (at least 8.2)

DBI module (at least 1.51)

DBD::Pg module (at least 2.0.0)

DBIx::Safe module (at least 1.2.4)

Linux版本

cat /etc/redhat-release

CentOS Linux release 7.9.2009 (Core)

2.2 Perl安装（所有节点都需要安装）

本次系统linux版本是7.6 Perl是linux系统自带；默认版本是v5.16.1；可行

[root@node3 Bucardo]# perl -v

This is perl, v5.16.1 (*) built for x86_64-linux-thread-multi

如果perl版本低于5.8则执行如下操作

####需要安装 perl-5 /src/

wget /src/5.0/

tar -zxvf

cd perl-5.28.0

./Configure

Make

make install

进行Configure时务必不要指定-d参数，其中有2个重要选项不能采用默认配置：

Build a shared (y/n) [n] 这里要选Y

Build a threading Perl? [n] 这里要选Y

安装完成后登陆数据库查看plperl语言组件是否能正确运行：

create language plperlu;

create language plperl

2.3 安装postgreSQL（所有节点都需要安装）

在编译的过程需要带上 –with-perl;

./configure --prefix=/pgsql --with-perl

2.4安装Pl/PerlU（所有节点都安装）

之前安装Pg时已经加入了--with-perl则不需要再在此安装)

# yum install postgresql-plperl

3、安装依赖包（主节点安装）

3.1 安装DBI module

安装DBI 依赖 Test-Simple；

# 安装 Test-Simple

Test-Simple下载地址 搜索 Test-Simple

# 解压Test-Simple源码包

tar -zxvf

# 进入Test-Simple目录进行安装

cd Test-Simple-1.302183

perl

make

make install

# 接下来安装DBI

# 下载地址：/CPAN/authors/id/T/TI/TIMB/

tar -zxvf

cd DBI-1.630

perl

make

make install

3.2 安装DBD::Pg module

安装最新的DBD::Pg；需要依赖 Time::HiRes

# 安装 Time::HiRes

下载地址： 搜索 Time::HiRes

tar -zxvf

cd Time-HiRes-1.9764

perl

make

make install

# 接下来安装DBD::Pg

# 安装DBD::Pg；需要确定已经安装 PostgreSQL 环境；即pg_config输出

# 加载 PostgreSQL 环境变量

tar -zxvf

cd DBD-Pg-3.14.2

perl

make

make install

3.3 安装DBIx::Safe

# 下载地址：/DBIx-Safe/

tar -zxvf

cd DBIx-Safe-1.2.5

perl

make

make install

4、安装Bucardo

# 在网站/Bucardo/下载

tar -zxvf

cd Bucardo-5.6.0

perl

make

make install

# 查看Bucardo版本

[postgres@node3 ~]$ bucardo --version

bucardo version 5.6.0

5、创建bucardo元数据库

5.1创建.bucardorc文件

在系统用户home目录下创建；内容如下：

log_conflict_file

/home/postgres/bucardo/log/bucardo_

piddir = /home/postgres/bucardo/run

reason_file

/home/postgres/bucardo/log/

warning_file

/home/postgres/bucardo/log/

syslog_facility = LOG_LOCAL1

=

=

=

5.2创建所需的目录及数据库

mkdir -p /home/postgres/bucardo/log

mkdir -p /home/postgres/bucardo/run

mkdir -p /var/run/bucardo

chmod 777 /var/run/bucardo

$ psql

postgres=# create user bucardo with superuser password 'bucardo';

postgres=# create database bucardo with owner = bucardo;

5.3初始化bucardo管理库

准备工作完成；开始执行 bucardo install 命令创建 元数据库;

# /usr/local/share/bucardo/

[postgres@node3 ~]$ bucardo install

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:

2. Port: 5432

3. User: bucardo

4. Database: bucardo

5. PID directory: /home/postgres/bucardo/run

Enter a number to change it, P to proceed, or Q to quit: 1

-- 推荐使用unix套接字方式连接数据;可以使用dbuser:postgres;dbname:postgres去连

接。

Change the host to: /tmp

Changed host to: /tmp

Current connection settings:

1. Host: /tmp

2. Port: 6000

3. User: postgres

4. Database: postgres

5. PID directory: /home/postgres/bucardo/run

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@

You may want to check over the configuration variables next, by running:

bucardo show all

Change any setting by using: bucardo set foo=bar

6、Bucardo配置同步（主节点postgre用户操作）

6.1 添加复制数据库（在db1上执行操作）

执行bucardo add db命令；记录同步数据库的连接信息

bucardo add db db01 host=192.168.93.11 dbname=postgres port=5432 user=postgres

pass=123456

bucardo add db db02 host=192.168.93.12 dbname=postgres port=5432 user=postgres

pass=123456

6.2 添加数据库群组

添加 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

6.3 添加需要同步的表及序列

bucardo add all tables

bucardo add all sequences

6.4 添加表群组

# 创建复制表tbl_lottu01

[postgres@node3 ~]$ psql master postgres -c "create table tbl_lottu01(id int

primary key, info text)"

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:

_lottu01 (DB: db1)

The following tables or sequences are now part of the relgroup "relgrp01":

_lottu01

6.5添加同步

添加同步；在创建同步需要指定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

(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

(herd,name,dbs,conflict_strategy) VALUES ($1,$2,$3,$4)

Added sync "sync01"

6.6启动bucardo

bill@docker-> bucardo start

Checking for existing processes

Removing file "/home/bill/bucardo/run/fullstopbucardo"

Starting Bucardo

–查看状态

bill@docker-> bucardo status

PID of Bucardo MCP: 5796

Name State Last good Time Last I/D Last bad Time

=======+========+============+========+===========+===========+=======

sync1 | Good | 08:29:40 | 4m 52s | 0/0 | none |

6.7 测试同步

双向插入数据，验证数据是否正确同步。