postgresql multi-master 系列 Bucardo 之二 配置Bucardo(单到多)

os: centos 7.4
db: postgresql 11.7

bucardo可以实现postgresql的多主复制、主从同步，甚至可以以postgresql为源库，可以和oracle、mysql、mongodb等很多数据库进行数据异步同步。

看上去很强大，只不过是通过触发器实现数据的捕捉。

注意不支持 ddl 复制

192.168.56.111  bu1
192.168.56.112  bu2
192.168.56.113  bu3

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i postgresql
postgresql11.x86_64                11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-contrib.x86_64        11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
postgresql11-devel.x86_64          11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-docs.x86_64           11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-libs.x86_64           11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-llvmjit.x86_64        11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
postgresql11-plperl.x86_64         11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-plpython.x86_64       11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-plpython3.x86_64      11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-pltcl.x86_64          11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-server.x86_64         11.7-1PGDG.rhel7                    @pgdg11  
postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
postgresql11-test.x86_64           11.7-1PGDG.rhel7                    @pgdg11 

# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$
$ psql -c "select version();"
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

实例化Bucardo(所有节点都需要执行)

需要修改下 hosts

# vi /etc/hosts

192.168.56.111  bu1
192.168.56.112  bu2
192.168.56.113  bu3

在postgres用户下面新建.bucardorc 文件

# mkdir -p /var/log/bucardo/;
chmod 777 /var/log/bucardo/;

# which bucardo
/usr/local/bin/bucardo

# su - postgres
$ vi .bucardorc
log_conflict_file         = /var/lib/pgsql/bucardo/bucardo_conflict.log
piddir                    = /var/lib/pgsql/bucardo
reason_file               = /var/lib/pgsql/bucardo/bucardo.restart.reason.log
warning_file              = /var/lib/pgsql/bucardo/bucardo.warning.log

创建 bucardo 用户及数据库 bucardo

$ psql

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

postgres=# create database bucardo with owner = bucardo;

$ psql -U bucardo

bucardo=# create table tmp_t0(
c0 bigint,
c1 varchar(100)
);
bucardo=# create table tmp_t1(
c0 bigint,
c1 varchar(100)
);

bucardo=# alter table tmp_t0 add primary key(c0);
alter table tmp_t1 add primary key(c0);

bucardo=# insert into tmp_t0
select id,md5(id::varchar)
  from generate_series(1,1000) as id;
;

bucardo=# insert into tmp_t1
select id,md5(id::varchar)
  from generate_series(1,1000) as id;
;

实例化Bucardo

$ 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:           <none>
2. Port:           5432
3. User:           bucardo
4. Database:       bucardo
5. PID directory:  /var/lib/pgsql/bucardo
Enter a number to change it, P to proceed, or Q to quit: 1

Change the host to: bu1

Changed host to: bu1
Current connection settings:
1. Host:           bu1
2. Port:           5432
3. User:           bucardo
4. Database:       bucardo
5. PID directory:  /var/lib/pgsql/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 [e-mail]

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(bu1 to bu2、bu3)

bu1 节点上执行添加DB(需要事先配置 pg_hba.conf ，确保 psql -h hostname -U bucardo 可以登录)

$ bucardo add db db1 dbname=bucardo host=192.168.56.111 port=5432 user=bucardo
$ bucardo add db db2 dbname=bucardo host=192.168.56.112 port=5432 user=bucardo
$ bucardo add db db3 dbname=bucardo host=192.168.56.113 port=5432 user=bucardo

bu1 节点上执行添加表(需要有主键)

$ bucardo add table tmp_t0
$ bucardo add table tmp_t1

bu1 节点上执行添加复制群组

$ bucardo add relgroup relgroup1 tmp_t0 tmp_t1;
$ bucardo add dbgroup  dbgroup1  db1:source db2:target db3:target

bu1 节点上执行添加同步

$ bucardo add sync sync1 relgroup=relgroup1 dbs=dbgroup1

bu1 节点上启动bucardo

$ bucardo start;
bucardo stop;
bucardo restart;

bu1 节点上查看状态

$ bucardo status
PID of Bucardo MCP: 14169
 Name    State    Last good    Time     Last I/D    Last bad    Time  
=======+========+============+========+===========+===========+=======
 sync1 | Good   | 16:47:16   | 3m 33s | 0/0       | none      |  
 

参考：
http://bucardo.org
https://bucardo.org/Bucardo/pgbench_example.html
https://bucardo.org/Bucardo/bucardo.html

$ bucardo help
Usage:
      bucardo [<options>] <command> [<action>] [<command-options>] [<command-params>]

Commands:
    Run "bucardo help <command>" for additional details

    "install"
        Installs the Bucardo configuration database.

    "upgrade"
        Upgrades the Bucardo configuration database to the latest schema.

    "start [<start options>] [<reason>]"
        Starts Bucardo.

    "stop [<reason>]"
        Stops Bucardo.

    "restart [<start options>] [<reason>]"
        Stops and starts Bucardo.

    "list <type> [<regex>]"
        Lists objects managed by Bucardo.

    "add <type> <name> <parameters>"
        Adds a new object.

    "update <type> <name> <parameters>"
        Updates an object.

    "remove <type> <name> [<name>...]"
        Removes one or more objects.

    "kick <syncname> [<sync options>] [<syncname>...] [<timeout>]"
        Kicks off one or more syncs.

    "reload config"
        Sends a message to all CTL and KID processes asking them to reload
        the Bucardo configuration.

    "reopen"
        Sends a message to all Bucardo processes asking them to reopen any
        log files they may have open. Call this after you have rotated the
        log file(s).

    "show all|<setting> [<setting>...]"
        Shows the current Bucardo settings.

    "<set <setting=value" [<setting=value>...] >>
        Sets one or more configuration setting..

    "ping [<timeout>]"
        Sends a ping notice to the MCP process to see if it will respond.

    "status [<status options>] <syncname> [<syncname>...]"
        Shows the brief status of syncs in a tabular format.

    "activate <syncname> [<syncname>...] [<timeout>]"
        Activates one or more named syncs.

    "deactivate <syncname> [<syncname>...] [<timeout>]"
        Deactivates one or more named syncs.

    "message '<body>'"
        Sends a message to the running Bucardo logs.

    "reload [<syncname> [<syncname>...]]"
        Sends a message to one or more sync processes, instructing them to
        reload.

    "inspect <type> <name> [<name>...]"
        Inspects one or more objects of a particular type.

    "validate all|<syncname> [<syncname>...]"
        Validates one or more syncs.

    "purge all|<table> [<table>...]"
        Purges the delta and track tables for one or more tables, for one or
        more databases.

    "delta [<database(s)>]"
        Show the delta counts for each source target.

    "help [<command> [<action>]]"
        Shows help.

Options:
      -d --db-name       NAME  Database name.
      -U --db-user       USER  Database user name.
      -P --db-pass       PASS  Database password.
      -h --db-host       HOST  Database server host name.
      -p --db-port       PORT  Database server port number.
         --bucardorc     FILE  Use specified .bucardorc file.
         --no-bucardorc        Do not use .bucardorc file.
         --quiet               Incremental quiet.
         --verbose             Incremental verbose mode.
      -? --help                Output basic help and exit.
         --version             Print the version number and exit.
         --dryrun              Do not perform any actual actions.
         --confirm             Require direct confirmation before changes.