基于Bucardo建立PostgreSQL 主主混合集群(X86 + ARM)部署

# 更新库
sudo apt-get update

# 安装依赖库
sudo apt-get install zlib1g zlib1g-dev bzip2 libbz2-dev readline-common libreadline-dev bison libgmp-dev libmpfr-dev libmpc-dev -y

# 安装工具软件
sudo apt-get -q install -y --no-install-recommends build-essential autoconf automake libtool cmake zlib1g-dev pkg-config libssl-dev libssl1.0.0 libsasl2-dev bats curl sudo git wget

# Barcudo 依赖的perl安装
sudo apt-get install perl -y

# 取PG版本为10
PGVERSION=10

# 安装PG server/client 以及perl依赖
sudo apt-get install -y postgresql-${PGVERSION} postgresql-client-${PGVERSION}     postgresql-${PGVERSION}-pgtap postgresql-server-dev-${PGVERSION} postgresql-server-dev-all postgresql-plperl-${PGVERSION} debhelper fakeroot libdbd-pg-perl libtap-parser-sourcehandler-pgtap-perl

# 导入PATH
export PATH=/usr/lib/postgresql/${PGVERSION}/bin:${PATH}

sudo useradd -m -d /home/pgtest -s /bin/bash pgtest && echo pgtest:pgtest | chpasswd && adduser pgtest sudo
sudo echo "pgtest ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers

export PATH=/usr/lib/postgresql/${PGVERSION}/bin:${PATH}
git clone https://github.com/bucardo/bucardo
cd bucardo/

# 安装缺少的Perl modules
sudo cpan install CGI
sudo cpan install DBIx::Safe

# 编译安装bucardo
perl Makefile.PL
export USER=pgtest
BUCARDO_LOG_ERROR_CONTEXT=1 PATH=$PATH:/usr/lib/postgresql/${PGVERSION}/bin make test
sudo make install

iptables -t nat -A PREROUTING -d 192.168.0.111/32 -p tcp -m tcp --dport 5432 -j DNAT --to-destination 172.17.0.111:5432

# 由于docker在Forward链默认规则是DROP，我们需要针对放通
iptables -A FORWARD -d 172.17.0.111/32 -p tcp -m tcp --dport 5432 -j ACCEPT

listen_addresses = '*'

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    replication     all             1.1.1.1/32                 trust
host    replication     all             2.2.2.2/32                 trust
host    all             all             1.1.1.1/32               md5
host    all             all             2.2.2.2/32               md5

# 导入PG到环境变量
PGVERSION=10
export PATH=/usr/lib/postgresql/${PGVERSION}/bin:${PATH}

# 初始化PG数据库服务，注意PG_DATA_DIR 和 PG_LOG_DIR变量需要自行设置，保证当前运行用户有权限访问
initdb --pgdata=$PG_DATA_DIR --encoding=UTF8
pg_ctl -D $PG_DATA_DIR -l $PG_LOG_DIR start

# 创建测试数据库 testdb
createdb -O pgtest testdb

pgtest@pg-x86:~/bucardo$ psql -d testdb
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
Type "help" for help.

testdb=# create user dbuser with password '123456';
CREATE ROLE
testdb=# create table t1 (col1 numeric NOT NULL, col2 numeric, CONSTRAINT pk_1 PRIMARY KEY (col1));
CREATE TABLE
testdb=# \q
pgtest@pg-x86:~/bucardo$ psql -U dbuser -d testdb
psql (10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
Type "help" for help.

testdb=> \q

$ ./bucardo install
Enter a number to change it, P to proceed, or Q to quit: 3

Change the user to: greenplum

Changed user to: greenplum
Current connection settings:
1. Host:           <none>
2. Port:           5432
3. User:           pgtest
4. Database:       bucardo
5. PID directory:  $PG_PID_DIR
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.

# 添加源数据库
bucardo add database pg51 dbname=testdb port 5432 host=127.0.0.1 user=dbuser pass=123456

# 添加目标数据库
bucardo add database pg52 dbname=testdb port 5432 host=2.2.2.2 user=dbuser pass=123456

# 添加数据库组
bucardo add dbgroup grp1 pg51:source pg52:target

# 添加表集群, 注意：同步的表需要有主键
bucardo add table public.t1 herd=herd_test

# 添加同步信息, conflict_strategy为解决冲突的方式共有6种方式，source,target,skip,random,latest,abort
bucardo add sync sync51to52 herd=herd_test dbs=grp1 conflict_strategy=latest

bucardo start

# 停止同步
bucardo stop

# 暂停 / 恢复某一组同步
bucardo pause/resume sync51to52

# 查看同步状态
bucardo status