bucardo使用指南

原创
2016/01/13 13:51
阅读数 1K

一、Bucardo简介

Bucardo是一款能在PostgreSQL 中实现双向同步的软件,可以实现更多的源数据库(即主数据库)以及更多的目标数据库(即备份数据库)之间的同步,Bucardo还可以复制到其他类型的目标数据库,其中包括MySQLMariaDBOracleSQLiteMongoDBRedis

Bucardo是异步同步,因此实现多Master方案时,只能做到数据的最终一致,Bucardo的同步通过触发器来记录变化,并利用PostgreSQL中的“NOTIFY”消息事件通知机制实现高效同步,所以同步很灵活,可以只同步数据库中选定的几张表,或者几个序列。

二、安装环境

2.1 系统

# cat /etc/issue

CentOS release 6.5 (Final)

Kernel \r on an \m

# uname –a

Linux localhost.localdomain 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

 

2.2 主机

IP地址                          数据目录                   用户名            主机名

192.168.10.187        /data/pgdata                    pg95                  db1

192.168.10.186   /data/pgdata                  pg95                  db2

 

2.3 安装的依赖包

Test-Simple-1.302013_005

ExtUtils-MakeMaker-7.11_03

version-0.9912_01

DBI-1.634

DBD-Pg-3.5.3

DDBIx-Safe-1.2.5

 

三、安装postgresql

    注意事项:./configure --prefix=/opt/pgsql9.5 --port=1995 --with-perl

    执行该步骤时,必须带上—with-perl选项, 安装posrgresql-plperl

四、安装依赖包

    依赖包统一下载路径:/opt/software

#安装test-simple

cd /opt/software

wget http://search.cpan.org/CPAN/authors/id/E/EX/EXODIST/Test-Simple-1.302013_005.tar.gz

tar -zxvf Test-Simple-1.302013_005.tar.gz

cd Test-Simple-1.302013_005

perl Makefile.PL

make install

 

#安装ExtUtils-MakeMaker

cd /opt/software/

wget http://search.cpan.org/CPAN/authors/id/B/BI/BINGOS/ExtUtils-MakeMaker-7.11_03.tar.gz

tar -zxvf ExtUtils-MakeMaker-7.11_03.tar.gz

cd ExtUtils-MakeMaker-7.11_03

perl Makefile.PL

make

make install

 

#安装version

cd /opt/software/

wget http://search.cpan.org/CPAN/authors/id/J/JP/JPEACOCK/version-0.9912_01.tar.gz

tar -zxvf version-0.9912_01.tar.gz

cd version-0.9912_01

perl Makefile.PL

make

make install

 

#安装DBI

cd /opt/software/

wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.634.tar.gz

tar -zxvf DBI-1.634.tar.gz

cd DBI-1.634

perl Makefile.PL

make

make install

 

#安装DBD-Pg

Source /home/pg95/.bash_profile #加载配置有PG环境变量的文件。

cd /opt/software/

wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-3.5.3.tar.gz

tar -zxvf DBD-Pg-3.5.3.tar.gz

cd DBD-Pg-3.5.3

perl Makefile.PL

make

make install

 

#安装DBIx-Safe

cd /opt/software

wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBIx-Safe-1.2.5.tar.gz

tar -zxvf DBIx-Safe-1.2.5.tar.gz

cd DBIx-Safe-1.2.5

perl Makefile.PL

make

make install

五、安装Bucardo

db1上安装Bucardo

#安装bucardo

cd /opt/software/

wget http://bucardo.org/downloads/Bucardo-5.4.1.tar.gz

tar -zxvf Bucardo-5.4.1.tar.gz

cd Bucardo-5.4.1

export INSTALL_BUCARDODIR=/opt/bucardo

perl Makefile.PL

make

make install

 

六、配置Bucardo

6.1  初始化Bucardo管理库

mkdir -p /var/run/bucardo #建立Bucardo pid文件目录

chmod 777 /var/run/bucardo

su - pg95

 


 

切换到bucardo安装目录下,不然会报找不到bucardo.shema

cd  /opt/bucardo

bucardo install –U pg95 –d postgres

显示如下:

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:           localhost

2. Port:           1995

3. User:           postgres

4. Database:       postgres

5. PID directory:  /var/run/bucardo

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

 

Change the user to: pg95

 

Changed user to: pg95

Current connection settings:

1. Host:           localhost

2. Port:           1995

3. User:           pg95

4. Database:       postgres

5. PID directory:  /var/run/bucardo

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

 

Postgres version is: 9.5

Creating superuser 'bucardo'

Attempting to create and populate the bucardo database and schema

ERROR:  role "bucardo" already exists

STATEMENT:  CREATE USER bucardo SUPERUSER;

ERROR:  language "plpgsql" already exists

STATEMENT:  CREATE LANGUAGE plpgsql;

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

 

 

查看引入的对象:

[pg95@localhost bucardo]$ psql bucardo bucardo

psql (9.5rc1)

Type "help" for help.

 

bucardo=# \d

                      List of relations

 Schema   |             Name                       |   Type   |  Owner 

---------+-------------------------------+----------+---------

 Bucardo   | bucardo_config                         | table    | bucardo

 Bucardo   | bucardo_custom_trigger                 | table    | bucardo

 Bucardo   | bucardo_custom_trigger_id_seq         | sequence | bucardo

 bucardo   | bucardo_log_message                  | table    | bucardo

 bucardo   | bucardo_rate                        | table    | bucardo

 bucardo   | clone                           | table    | bucardo

 bucardo   | clone_id_seq                       | sequence | bucardo

 bucardo   | customcode                         | table    | bucardo

 bucardo   | customcode_id_seq                     | sequence | bucardo

 bucardo   | customcode_map                   | table    | bucardo

 bucardo   | customcols                        | table    | bucardo

 bucardo   | customcols_id_seq                | sequence | bucardo

 bucardo   | customname                         | table    | bucardo

 bucardo   | customname_id_seq               | sequence | bucardo

 bucardo   | db                              | table    | bucardo

 bucardo   | db_connlog                      | table    | bucardo

 bucardo   | dbgroup                         | table    | bucardo

 bucardo   | dbmap                          | table    | bucardo

 bucardo   | dbrun                           | table    | bucardo

 bucardo   | goat                            | table    | bucardo

 bucardo   | goat_id_seq                       | sequence | bucardo

 bucardo   | herd                            | table    | bucardo

 bucardo   | herdmap                                     | table    | bucardo

 bucardo   | sync                             | table    | bucardo

 bucardo   | syncrun                        | table    | bucardo

 bucardo   | upgrade_log                     | table    | bucardo

(26 rows)

 

bucardo=# \df

                                    List of functions

 Schema  |            Name            | Result data type | Argument data types |  Type  

---------+----------------------------+------------------+---------------------+---------

 bucardo | bucardo_delete_sync        | trigger          |                     | trigger

 bucardo | bucardo_log_message_notify | trigger          |                     | trigger

 bucardo | bucardo_tablename_maker    | text             | text                | normal

 bucardo | check_bucardo_config       | trigger          |                     | trigger

 bucardo | db_change                  | trigger          |                     | trigger

 bucardo | db_getconn                 | text             | text                | normal

 bucardo | db_testconn                | text             | text                | normal

 bucardo | find_unused_goats          | SETOF text       |                     | normal

 bucardo | herdcheck                  | trigger          |                     | trigger

 bucardo | magic_update               | text             |                     | normal

 bucardo | plperlu_test               | text             |                     | normal

 bucardo | table_exists               | boolean          | text, text          | normal

 bucardo | validate_all_syncs         | integer          |                     | normal

 bucardo | validate_all_syncs         | integer          | integer             | normal

 bucardo | validate_goat              | trigger          |                     | trigger

 bucardo | validate_sync              | trigger          |                     | trigger

 bucardo | validate_sync              | text             | text                | normal

 bucardo | validate_sync              | text             | text, integer       | normal

(18 rows)

 

bucardo=# \dL

                     List of languages

  Name   |  Owner  | Trusted   |         Description         

---------+---------+---------+------------------------------

 Plperlu   | bucardo   | f        |

 plpgsql   | pg95      | t       | PL/pgSQL procedural language

(2 rows)

 

 

 

 

6.2 修改环境变量

export PGHOME=/opt/pgsql9.5

export PATH=$PGHOME/bin:/opt/bucardo:$PATH:.

export PGDATA=/data/pgdata

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH


 

七、测试

7.1 修改db1db2数据库网络访问权限

分别修改数据库的postgresql.con,pg_hba.conf

db1db2 postgresql.conf 修改如下:

# - 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 = 1995                             # (change requires restart)

max_connections = 100                   # (change requires restart)

 

 

*为了方便测试,故db1,db2之间实现无密码登录,也可以通过.pgpass实现,也可以在

Bucardo 添加数据库的时候,指定pass参数。

db1 pg_hba.conf 修改如下:

在文件中添加:

host    all             all             192.168.10.186/32       trust

db2 pg_hba.conf 修改如下:

在文件中添加:

host    all             all             192.168.10.187/32       trust

 

修改完上述两个文件之后重启数据库。

/opt/pgsql9.5/bin/pg_ctl –D /data/pgdata restart

 

测试是否实现无密码登录:

db1

su pg95

source /home/pg95/.bash_profile

[pg95@localhost pgdata]$ psql -h 192.168.10.186

psql (9.5rc1)

Type "help" for help.

 

postgres=#

 

db2上执行

[pg95@localhost pgdata]$ psql -h 192.168.10.186

psql (9.5rc1)

Type "help" for help.

 

postgres=#

 

执行上述命令。不需要输入登录密码即可访问对方数据库。

 

7.2 主从测试

7.2.1、分别在db1,db2上创建两个测试库。

db1上执行  creatdb masterdb

db2上执行  createdb slavedb

 

7.2.2、分别在 masterdb,slavedb中建立一个测试表。

所建立的表必须添加主键。如果不指定主键,bucardo添加table的时候会报错,无法添加。

db1上执行psql -d masterdb -c 'create table mstest(id int primary key,content text);'

db2上执行psql -d slavedb -c 'create table mstest(id int primary key,content text);'

 

7.2.3、添加复制数据库(在db1上执行操作)

[pg95@localhost bucardo]$ bucardo add db masterdb dbhost=127.0.0.1 dbport=1995 dbuser=pg95 dbname=masterdb

Added database "masterdb"

[pg95@localhost bucardo]$ bucardo add db slavedb dbhost=192.168.10.186 dbport=1995 dbuser=pg95 dbname=slavedb

Added database "slavedb"

 

*当添加的数据库IP地址错误或者数据库不存在时报如下错误:

[pg95@localhost bucardo]$ bucardo add db slave dbhost=192.168.30.186 dbport=1995 dbuser=pg95 dbname=slavedb

Connection to "slave" (PostgreSQL database) failed. You may force add it with the --force argument.

Error was: could not connect to server: No route to host

         Is the server running on host "192.168.30.186" and accepting

         TCP/IP connections on port 1995?

 

[pg95@localhost bucardo]$ bucardo add db slave dbhost=192.168.10.186 dbport=1995 dbuser=pg95 dbname=slave

Connection to "slave" (PostgreSQL database) failed. You may force add it with the --force argument.

Error was: FATAL:  database "slave" does not exist

 

添加数据库后查看:

bucardo=# select * from db;

-[ RECORD 1 ]--------+------------------------------

name                 | masterdb

dbtype               | postgres

dbhost               | 127.0.0.1

dbport               | 1995

dbname               | masterdb

dbuser               | pg95

dbpass               |

dbconn               |

dbservice            |

pgpass               |

status               | active

server_side_prepares | t

makedelta            | f

cdate                | 2016-01-07 11:15:02.510795+08

-[ RECORD 2 ]--------+------------------------------

name                 | slavedb

dbtype               | postgres

dbhost               | 192.168.10.186

dbport               | 1995

dbname               | slavedb

dbuser               | pg95

dbpass               |

dbconn               |

dbservice            |

pgpass               |

status               | active

server_side_prepares | t

makedelta            | f

cdate                | 2016-01-07 11:16:13.717162+08

bucardo=# select * from db_connlog ;

-[ RECORD 1 ]-------------------------------------------------------------

db         | masterdb

conndate   | 2016-01-07 11:15:02.510795+08

connstring | dbi:Pg:dbname=masterdb;host=127.0.0.1;port=1995 user=pg95

status     | good

version    | 90500

-[ RECORD 2 ]-------------------------------------------------------------

db         | slavedb

conndate   | 2016-01-07 11:16:13.717162+08

connstring | dbi:Pg:dbname=slavedb;host=192.168.10.186;port=1995 user=pg95

status     | good

version    | 90500

 

 

7.2.4、添加需要同步的表(在db1上执行操作)

[pg95@localhost bucardo]$ bucardo add table public.mstest db=masterdb

Added the following tables or sequences:

  public.mstest

 

7.2.5、添加表群组

bucardo add herd herd1 public.mstest

Created relgroup "herd1"

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

  public.mstest

或者使用以下命令也可以

bucardo add relgroup herd1 public.mstest

Created relgroup "herd1"

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

  public.mstest

 

添加后查看:

bucardo=# select * from herd;

    name     | about |             cdate            

-------------+-------+-------------------------------

 ms_relgroup |       | 2016-01-07 13:42:35.503979+08

(1 row)

bucardo=# select * from herdmap ;

    herd     | goat | priority |             cdate            

-------------+------+----------+-------------------------------

 ms_relgroup |    1 |        0 | 2016-01-07 13:42:35.503979+08

(1 row)

 

 

7.2.6、添加数据库群组

[pg95@localhost bucardo]$ bucardo add dbgroup ms_dbgroup masterdb:source slavedb:target

Created dbgroup "ms_dbgroup"

Added database "masterdb" to dbgroup "ms_dbgroup" as source

Added database "slavedb" to dbgroup "ms_dbgroup" as target

 

添加后查看:

bucardo=# select * from dbgroup;

    name    | about |             cdate            

------------+-------+-------------------------------

 ms_dbgroup |       | 2016-01-07 13:43:21.071796+08

(1 row)

 

7.2.7、添加同步

bucardo add sync ms_sync relgroup=ms_relgroup dbs=ms_dbgroup

Added sync "ms_sync"

 

mstest表不存在是报如下错误:

[pg95@localhost bucardo]$ bucardo add sync ms_sync relgroup=ms_relgroup dbs=ms_dbgroup

WARNING:  Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=slavedb;host=192.168.10.186;port=1995 at line 1018.

CONTEXT:  PL/Perl function "validate_sync"

SQL statement "SELECT validate_sync('ms_sync')"

PL/Perl function "validate_sync"

Failed to add sync: DBD::Pg::st execute failed: ERROR:  Could not find "mstest" inside the "public" schema on database "slavedb"! at line 30.

CONTEXT:  PL/Perl function "validate_sync" at /opt/bucardo/bucardo line 4612.

 

添加后查看:

bucardo=# select * from sync;

-[ RECORD 1 ]------+------------------------------

name               | ms_sync

herd               | ms_relgroup

dbs                | ms_dbgroup

stayalive          | t

kidsalive          | t

conflict_strategy  | bucardo_latest

copyextra          |

deletemethod       | delete

autokick           | t

checktime          |

status             | active

rebuild_index      | 0

priority           | 0

analyze_after_copy | t

vacuum_after_copy  | f

strict_checking    | t

overdue            | 00:00:00

expired            | 00:00:00

track_rates        | f

onetimecopy        | 0

lifetime           |

maxkicks           | 0

isolation_level    |

cdate              | 2016-01-07 13:49:05.463634+08

 

[pg95@localhost bucardo]$ bucardo list sync

Sync "ms_sync"  Relgroup "ms_relgroup" [Active]

  DB group "ms_dbgroup" masterdb:source slavedb:target

 

[pg95@localhost bucardo]$ bucardo list dbs

Database: masterdb  Status: active  Conn: psql -p 1995 -U pg95 -d masterdb -h 127.0.0.1

Database: slavedb   Status: active  Conn: psql -p 1995 -U pg95 -d slavedb -h 192.168.10.186

 

[pg95@localhost bucardo]$ bucardo list herd[relgroups/herd/relgroup]

Relgroup: ms_relgroup  DB: masterdb  Members: public.mstest

  Used in syncs: ms_sync

 

[pg95@localhost bucardo]$ bucardo list tables[table]

1. Table: public.mstest  DB: masterdb  PK: id (integer)

 

 

7.2.8 启动bucardo

bucardo start

Checking for existing processes

Starting Bucardo

 

[pg95@localhost bucardo]$  ps -ef | grep Bucardo | grep -v "grep"

pg95      4395     1  0 13:54 ?        00:00:01 Bucardo Master Control Program v5.4.1. Active syncs: ms_sync

pg95      4400  4395  0 13:54 ?        00:00:00 Bucardo VAC.

pg95      4403  4395  0 13:54 ?        00:00:01 Bucardo Controller. Sync "ms_sync" for relgroup "ms_relgroup" to dbs "ms_dbgroup"

pg95      4406  4403  0 13:54 ?        00:00:00 Bucardo Kid. Sync "ms_sync"

 

 

7.2.9 数据测试

 masterdb中插入数据

[pg95@localhost bucardo]$ psql masterdb

psql (9.5rc1)

Type "help" for help.

 

masterdb=# insert into mstest values (1,'test100');

INSERT 0 1

masterdb=# select * from mstest ;

 id | content

----+---------

  1 | test100

(1 row)

 

查询slavedb中查询数据

[pg95@localhost ~]$ psql slavedb

psql (9.5rc1)

Type "help" for help.

 

slavedb=# select * from mstest ;

 id | content

----+---------

  1 | test100

(1 row)

 

masterdb中删除数据

masterdb=# select * from mstest ;

 id | content

----+---------

  1 | test100

(1 row)

masterdb=# delete from mstest where id=1;

DELETE 1

masterdb=# select * from mstest ;

 id | content

----+---------

(0 rows)

 

slavedb中查看数据

slavedb=# select * from mstest ;

 id | content

----+---------

(0 rows)

 

masterdb中修改数据

masterdb=# select * from mstest ;

 id | content

----+---------

  1 | test100

  2 | test200

  3 | test300

(3 rows)     

masterdb=# update mstest set content='updatetest100' where id=1;

UPDATE 1  

masterdb=# select * from mstest ;

 id |    content   

----+---------------

  2 | test200

  3 | test300

  1 | updatetest100

(3 rows)

 

查看slavedb的数据

slavedb=# select * from mstest ;

 id |    content   

----+---------------

  2 | test200

  3 | test300

  1 | updatetest100

(3 rows)

 

查看同步状态:

[pg95@localhost bucardo]$ bucardo status

PID of Bucardo MCP: 4395

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

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

 ms_sync | Good   | 14:42:24   | 4m 16s | 1/1       | none      |     

同时可以暂停唤醒停止同步

[pg95@localhost bucardo]$  bucardo pause ms_sync

Syncs paused: ms_sync

[pg95@localhost bucardo]$  bucardo resume ms_sync

Syncs resumed: ms_sync

[pg95@localhost bucardo]$ bucardo stop

Creating /var/run/bucardo/fullstopbucardo ... Done

 

7.2.10 撤除与搭建同步的过程相反

移除同步

[pg95@localhost bucardo]$ bucardo remove sync ms_sync

Removed sync "ms_sync"

Note: table triggers (if any) are not automatically removed!

移除数据库组

[pg95@localhost bucardo]$ bucardo remove dbgroup ms_dbgroup

Removed dbgroup "ms_dbgroup"

移除表集群

[pg95@localhost bucardo]$ bucardo remove relgroup ms_relgroup

Removed relgroup "ms_relgroup"

移除同步的表

[pg95@localhost bucardo]$ bucardo remove table public.mstest

Removed the following tables:

  public.mstest

移除数据库

[pg95@localhost bucardo]$ bucardo remove db masterdb slavedb

Removed database "masterdb"

Removed database "slavedb"

 

 

至此:使用Bucardo搭建主从同步完成。

至此:使用Bucardo主从同步的搭建和撤出已经完成。

 

7.3 主主测试

7.3.1 分别在db1,db2一个数据库master1,master2及测试表

db1                   [pg95@localhost ~]$ createdb masterdb0

db2                     [pg95@localhost ~]$ createdb masterdb1

db1         psql -d masterdb0 -c 'create table mmtest(id int primary key,content text);'

db2         psql -d masterdb1 -c 'create table mmtest(id int primary key,content text);'

7.3.2 bucardo中添加数据库(db1上执行)

[pg95@localhost ~]$  bucardo add db masterdb0 dbhost=127.0.0.1 dbport=1995 dbuser=pg95 dbname=masterdb0

Added database "masterdb0"

[pg95@localhost ~]$ bucardo add db masterdb1 dbhost=192.168.10.186 dbport=1995 dbuser=pg95 dbname= masterdb1

Added database "masterdb1"

7.3.3 添加需要同步的表

[pg95@localhost ~]$ bucardo add table public.mmtest db=masterdb0

Added the following tables or sequences:

  public.mmtest

7.3.4 添加同步表集群

[pg95@localhost ~]$ bucardo add relgroup mm_relgoup public.mmtest

Created relgroup "mm_relgoup"

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

  public.mmtest

7.3.5添加数据库群组

[pg95@localhost ~]$ bucardo add dbgroup mm_dbgroup masterdb0:source masterdb1:source

Created dbgroup "mm_dbgroup"

Added database "masterdb0" to dbgroup "mm_dbgroup" as source

Added database "masterdb1" to dbgroup "mm_dbgroup" as source

7.3.6 添加同步

[pg95@localhost ~]$ bucardo add sync mm_sync relgroup=mm_relgroup dbs=mm_dbgroup

Added sync "mm_sync"

7.3.7 启动同步

[pg95@localhost ~]$ bucardo start

Checking for existing processes

Removing file "/var/run/bucardo/fullstopbucardo"

Starting Bucardo

7.3.8 数据测试

master0上和master1上分别进行数据的增删改查。

增加数据

masterdb0=# select * from mmtest;

 id | content

----+---------

  1 | test100

(1 row)

masterdb0=# insert into mmtest values (2,'test200');

INSERT 0 1

masterdb0=# select * from mmtest;

 id | content

----+---------

  1 | test100

  2 | test200

(2 rows)

删除数据

masterdb1=# select * from mmtest ;

 id | content

----+---------

  1 | test100

  2 | test200

(2 rows)

masterdb1=# delete from mmtest where id=1;

DELETE 1

masterdb1=# select * from mmtest ;

 id | content

----+---------

  2 | test200

(1 row)

masterdb0=# select * from mmtest;

 id | content

----+---------

  2 | test200

(1 row)

修改数据

masterdb1=# select * from mmtest ;

 id | content

----+---------

  2 | test200

(1 row)

masterdb1=# update mmtest set content='updatetest200' where id=2;

UPDATE 1

masterdb1=# select * from mmtest ;

 id |    content   

----+---------------

  2 | updatetest200

(1 row)

masterdb0=# select * from mmtest;

 id |    content   

----+---------------

  2 | updatetest200

(1 row)

 

至此主主测试完成。

7.4 多主测试(4主库)

7.4.1 建立数据库和同步表

db1上操作

[pg95@localhost ~]$ createdb master0

[pg95@localhost ~]$ createdb master1

[pg95@localhost ~]$ psql -d master0 -c 'create table mutitest(id int primary key,content text);'

CREATE TABLE

[pg95@localhost ~]$ psql -d master1 -c 'create table mutitest(id int primary key,content text);'

CREATE TABLE

db2上操作

[pg95@localhost ~]$ createdb master2

[pg95@localhost ~]$ createdb master3

[pg95@localhost ~]$ psql -d master2 -c 'create table mutitest(id int primary key,content text);'

CREATE TABLE

[pg95@localhost ~]$ psql -d master3 -c 'create table mutitest(id int primary key,content text);'

CREATE TABLE

7.4.2 bucardo中添加数据库(db1上操作)

[pg95@localhost ~]$ bucardo add db masterdb0 dbhost=127.0.0.1 dbuser=pg95 dbport=1995 dbname=master0

Added database "masterdb0"

[pg95@localhost ~]$ bucardo add db masterdb1 dbhost=127.0.0.1 dbuser=pg95 dbport=1995 dbname=master1

Added database "masterdb1"

[pg95@localhost ~]$ bucardo add db masterdb2 dbhost=192.168.10.186 dbuser=pg95 dbport=1995 dbname=master2

Added database "masterdb2"

[pg95@localhost ~]$ bucardo add db masterdb3 dbhost=192.168.10.186 dbuser=pg95 dbport=1995 dbname=master3

Added database "masterdb3"

7.4.3 添加需要同步的表

[pg95@localhost ~]$ bucardo add table public.mutitest db=masterdb0

Added the following tables or sequences:

  public.mutitest

7.4.4 添加表集群

[pg95@localhost ~]$ bucardo add relgroup muti_relgroup public.mutitest

Created relgroup "muti_relgroup"

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

  public.mutitest

7.4.5 添加数据库集群

[pg95@localhost ~]$ bucardo add dbgroup muti_dbgroup masterdb0:source masterdb1:source masterdb2:source masterdb3:source

Created dbgroup "muti_dbgroup"

Added database "masterdb0" to dbgroup "muti_dbgroup" as source

Added database "masterdb1" to dbgroup "muti_dbgroup" as source

Added database "masterdb2" to dbgroup "muti_dbgroup" as source

Added database "masterdb3" to dbgroup "muti_dbgroup" as source

7.4.6 添加同步

[pg95@localhost ~]$ bucardo add sync muti_sync relgroup=muti_relgroup dbs=muti_dbgroup

Added sync "muti_sync"

7.4.7 启动bucardo

[pg95@localhost ~]$ bucardo start

Checking for existing processes

Removing file "/var/run/bucardo/fullstopbucardo"

Starting Bucardo

7.4.8 数据测试

插入数据

master1=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

(1 row)

 

master1=# insert into mutitest values (2,'mutitest200');

INSERT 0 1

master1=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

(2 rows)

 

master1=# \c master0

You are now connected to database "master0" as user "pg95".

master0=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

(2 rows)

 

master0=# insert into mutitest values (3,'mutitest300');

INSERT 0 1

master0=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

  3 | mutitest300

(3 rows)

 

master0=# \c master0

You are now connected to database "master0" as user "pg95".

master0=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

  3 | mutitest300

(3 rows)

master3=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

  3 | mutitest300

(3 rows)

master3=# insert into mutitest values (4,'test400');

INSERT 0 1

master3=# \c master2

You are now connected to database "master2" as user "pg95".

master2=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

  3 | mutitest300

  4 | test400

(4 rows)

删除数据

master2=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  2 | mutitest200

  3 | mutitest300

  4 | test400

(4 rows)

master2=# delete from mutitest where id=2;

DELETE 1

master2=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  3 | mutitest300

  4 | test400

(3 rows)

master2=# \c master3

You are now connected to database "master3" as user "pg95".

master3=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  3 | mutitest300

  4 | test400

(3 rows)

master0=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  3 | mutitest300

  4 | test400

(3 rows)

 

master0=# \c master1

You are now connected to database "master1" as user "pg95".

master1=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  3 | mutitest300

  4 | test400

(3 rows)

修改数据

master1=# select * from mutitest ;

 id |   content  

----+-------------

  1 | mutitest100

  3 | mutitest300

  4 | test400

(3 rows)

 

master1=# update mutitest set content='updatetest400' where id=4;

UPDATE 1

master1=# select * from mutitest ;

 id |    content   

----+---------------

  1 | mutitest100

  3 | mutitest300

  4 | updatetest400

(3 rows)

 

master1=# \c master0

You are now connected to database "master0" as user "pg95".

master0=# select * from mutitest ;

 id |    content   

----+---------------

  1 | mutitest100

  3 | mutitest300

  4 | updatetest400

(3 rows)

master3=# select * from mutitest ;

 id |    content   

----+---------------

  1 | mutitest100

  3 | mutitest300

  4 | updatetest400

(3 rows)

 

master3=# \c master2

You are now connected to database "master2" as user "pg95".

master2=# select * from mutitest ;

 id |    content   

----+---------------

  1 | mutitest100

  3 | mutitest300

  4 | updatetest400

(3 rows)

至此 多主复制搭建完成。

 

7.5 多主多从测试

1、添加数据库

数据库列表:

[pg95@localhost ~]$ bucardo list db

Database: masterdb0  Status: active  Conn: psql -p 1995 -U pg95 -d master0 -h 127.0.0.1

Database: masterdb1  Status: active  Conn: psql -p 1995 -U pg95 -d master1 -h 127.0.0.1

Database: masterdb2  Status: active  Conn: psql -p 1995 -U pg95 -d master2 -h 192.168.10.186

Database: masterdb3  Status: active  Conn: psql -p 1995 -U pg95 -d master3 -h 192.168.10.186

Database: slavedb0   Status: active  Conn: psql -p 1995 -U pg95 -d slave0 -h 127.0.0.1

Database: slavedb1   Status: active  Conn: psql -p 1995 -U pg95 -d slave1 -h 127.0.0.1

Database: slavedb2   Status: active  Conn: psql -p 1995 -U pg95 -d slave2 -h 192.168.10.186

Database: slavedb3   Status: active  Conn: psql -p 1995 -U pg95 -d slave3 -h 192.168.10.186

2、添加同步表

[pg95@localhost ~]$  bucardo add table public.mutitest db=masterdb0

Added the following tables or sequences:

  public.mutitest

3、添加表集群

[pg95@localhost ~]$ bucardo add relgroup mutims_relgroup public.mutitest

Created relgroup "mutims_relgroup"

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

  public.mutitest

4、添加数据库集群

[pg95@localhost ~]$ bucardo add dbgroup mutims_dbgroup masterdb0:source masterdb1:source masterdb2:source masterdb3:source slavedb0:target  slavedb1:target  slavedb2:target  slavedb3:target

Created dbgroup "mutims_dbgroup"

Added database "masterdb0" to dbgroup "mutims_dbgroup" as source

Added database "masterdb1" to dbgroup "mutims_dbgroup" as source

Added database "masterdb2" to dbgroup "mutims_dbgroup" as source

Added database "masterdb3" to dbgroup "mutims_dbgroup" as source

Added database "slavedb0" to dbgroup "mutims_dbgroup" as target

Added database "slavedb1" to dbgroup "mutims_dbgroup" as target

Added database "slavedb2" to dbgroup "mutims_dbgroup" as target

Added database "slavedb3" to dbgroup "mutims_dbgroup" as target

5、添加同步

[pg95@localhost ~]$ bucardo add sync mutims_sync relgroup=mutims_relgroup dbs=mutims_dbgroup

Added sync "mutims_sync"

6 启动bucardo

[pg95@localhost ~]$ bucardo start

Checking for existing processes

Removing file "/var/run/bucardo/fullstopbucardo"

Starting Bucardo

7 数据测试。

省略。(测试通过)

8bucardo测试用例结构

[pg95@localhost ~]$ bucardo list all

-- dbgroups:

dbgroup: mutims_dbgroup  Members: masterdb0:source masterdb1:source masterdb2:source masterdb3:source slavedb0:target slavedb1:target slavedb2:target slavedb3:target

-- databases:

Database: masterdb0  Status: active  Conn: psql -p 1995 -U pg95 -d master0 -h 127.0.0.1

Database: masterdb1  Status: active  Conn: psql -p 1995 -U pg95 -d master1 -h 127.0.0.1

Database: masterdb2  Status: active  Conn: psql -p 1995 -U pg95 -d master2 -h 192.168.10.186

Database: masterdb3  Status: active  Conn: psql -p 1995 -U pg95 -d master3 -h 192.168.10.186

Database: slavedb0   Status: active  Conn: psql -p 1995 -U pg95 -d slave0 -h 127.0.0.1

Database: slavedb1   Status: active  Conn: psql -p 1995 -U pg95 -d slave1 -h 127.0.0.1

Database: slavedb2   Status: active  Conn: psql -p 1995 -U pg95 -d slave2 -h 192.168.10.186

Database: slavedb3   Status: active  Conn: psql -p 1995 -U pg95 -d slave3 -h 192.168.10.186

-- relgroup:

Relgroup: mutims_relgroup  DB: masterdb0  Members: public.mutitest

  Used in syncs: mutims_sync

-- syncs:

Sync "mutims_sync"  Relgroup "mutims_relgroup" [Active]

  DB group "mutims_dbgroup" masterdb0:source masterdb1:source masterdb2:source masterdb3:source slavedb0:target slavedb1:target slavedb2:target slavedb3:target

-- tables:

5. Table: public.mutitest  DB: masterdb0  PK: id (integer)

-- sequences:

There are no sequences

 

 

八、安装过程中存在的问题

Q1

bucardo start

Checking for existing processes

Can't locate Bucardo.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /opt/bucardo/bucardo line 833.

 

/opt/bucardo/ 下执行 bucardo start

 

Q2

bucardo start

Could not open "bucardo.restart.reason.txt": Permission denied

 

su root

chown -R postgres:postgres /opt/bucardo

 

Q3

bucardo start

Checking for existing processes

Can't locate boolean.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Bucardo.pm line 34.

BEGIN failed--compilation aborted at Bucardo.pm line 34.

Compilation failed in require at /opt/bucardo/bucardo line 833.

 

yum install perl-boolean

 

Q4

bucardo start

Checking for existing processes

Starting Bucardo

Could not append to "/var/log/bucardo/log.bucardo": No such file or directory

 

mkdir -P /var/log/bucardo/

chmod 777 /var/log/bucardo/

 

Q5

/opt/bucardo/bucardo_ctl --help

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /opt/bucardo/bucardo_ctl line 18.

BEGIN failed--compilation aborted at /opt/bucardo/bucardo_ctl line 18.

 

解决方式:

yum install perl-devel perl-CPAN

perl -MCPAN -e shell

cpan[2]> install Time::HiRes

cpan[3]> exit

 

Q6

./configure --prefix=/opt/pg93/ --with-perl

checking for Perl privlibexp... /usr/share/perl5

checking for Perl useshrplib... true

checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).

BEGIN failed--compilation aborted.

no

configure: error: could not determine flags for linking embedded Perl.

This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not

解决方式:

yum install perl-ExtUtils-Embed

Q7

$ ./bucardo install

Can't locate Encode/Locale.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./bucardo line 24.

BEGIN failed--compilation aborted at ./bucardo line 24.

解决方式:

yum install perl-Encode-Locale

 

Q8

[postgres@bucardo ~]$ bucardo start

Checking for existing processes

Can't locate Bucardo.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /opt/bucardo/bucardo line 767.

解决方式:

/opt/bucardo目录下再执行start

 

 

 

小知识

添加同步节点,分五部:

1 添加源,目标数据库 db

2.添加表table

3.添加表集群relgroup/herd

4.添加数据库组dbgroup

5.添加同步sync

 

展开阅读全文
打赏
1
0 收藏
分享
打赏
0 评论
0 收藏
1
分享
返回顶部
顶部