bucardo5.3 postgres主从同步

标签： bucardo5 postgres 同步 | 发表时间：2014-12-30 12:26 | 作者：xmarker

出处：http://xmarker.blog.163.com

最近在pg官网上看到bucardo5.3 release了，bucardo可以实现postgresql的多主复制、主从同步，甚至可以以postgresql为源库，可以和oracle、mysql、mongodb等很多数据库进行数据异步同步，很多企业都有数据同步到业务需求，特别是不同数据库间的同步更是个大难题，bucardo5.3暂时只支持以pg为主库的异构数据库同步,将来会支持以别的数据库为源库的数据同步。在报表应用、olap等领域，bucardo可以把数据实时异步的传输到备库，然后在备库中可以进行数据转换、建临时表、报表等操作，而pg原生的stream replication只能是只读操作，所以bucardo的应用领域还是很广的。今天分两篇分别记录下pg到pg的主从同步和pg到mysql的主从同步。

1.bucardo5.3 安装：

1.1 首先安装依赖包：

[root@db164 soft]# wget http://bucardo.org/downloads/dbix_safe.tar.gz

tar xvfz dbix_safe.tar.gz

cd DBIx-Safe-1.2.5

perl Makefile.PL

make

make test

make install

也可以用yum install perl-DBIx-Safe （centos）或 apt-get install libdbix-safe-perl （ubuntu平台）安装

1.2 下载并安装bucardo

[root@db164 soft]# wget http://bucardo.org/downloads/Bucardo-5.3.0.tar.gz

cd Bucardo-5.3.0

perl Makefile.PL

make

make install

1.3 安装postgresql perl扩展

yum install postgresql-plperl 或者apt-get install postgresql-plperl-9.0

1.4 初始化安装bucardo到数据库中：

#bucardo install

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

Postgres version is: 9.3

Creating superuser 'bucardo'

Attempting to create and populate the bucardo database and schema

INSTALLATION FAILED! (psql:/usr/local/share/bucardo/bucardo.schema:45: ERROR: language "plperlu" does not exist

HINT: Use CREATE LANGUAGE to load the language into the database.)

解决：创建plperlu语言： create language plperlu;

如果提示没有plperlu语言，可能是编译时没有支持plperlu，需要重新编译以支持plperlu。

问题2：install_driver(Pg) failed: Can't locate DBD/Pg.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 (eval 9) line 3.

解决：安装perl dbd模块：yum install perl-DBD-Pg

以上问题都没问题后就可以安装了：

postgres:/home/postgres@db164>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: postgres

4. Database: bucardo

5. PID directory: /var/run/bucardo

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

Change the host to: 127.0.0.1

Changed host to: 127.0.0.1

Current connection settings:

1. Host: 127.0.0.1

2. Port: 5432

3. User: postgres

4. Database: bucardo

5. PID directory: /var/run/bucardo

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

Postgres version is: 9.3

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 [email protected]

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

bucardo show all

Change any setting by using: bucardo set foo=bar

注意要先创建pid directory

mkdir -p /var/run/bucardo

chown postgres:postgres /var/run/bucardo

2.配置复制：

这里我用postgresql9.3.5为源库，数据库为mcldb，并且在同一台机器上安装postgres9.4作为目标库，数据库为mcldb2，并且在源库和目标库创建相同表结构的表t_new，建表语句为：create table t_new (id int,name varchar(20),remark text)，因为bucardo不支持创建表等ddl同步操作。

2.1 添加源数据库：

postgres:/home/postgres@db164>bucardo add db source_mcldb dbname=mcldb port=5432 host=127.0.0.1 user=postgres pass=123456

Added database "source_mcldb"

2.2 添加目标数据库

postgres:/home/postgres@db164>bucardo add database destination_mcldb2 dbname=mcldb2 port=5434 host=127.0.0.1 user=pg94 pass=123456

Added database "destination_mcldb2"

2.3 添加表：

postgres:/home/postgres@db164>bucardo add table public.t_new db=source_mcldb

Added the following tables or sequences:

public.t_new

如果要添加所有的表，则可以bucardo add all table

2.4 添加复制群组（即复制一组表）：

postgres:/home/postgres@db164>bucardo add relgroup test_relgroup public.t_new

Created relgroup "test_relgroup"

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

2.5 添加数据库复制组（即一对复制关系）：

postgres:/home/postgres@db164>bucardo add dbgroup test_dbgroup source_mcldb:source destination_mcldb2:target

Created dbgroup "test_dbgroup"

Added database "source_mcldb" to dbgroup "test_dbgroup" as source

Added database "destination_mcldb2" to dbgroup "test_dbgroup" as target

2.6 添加同步：

postgres:/home/postgres@db164>bucardo add sync test_sync relgroup=test_relgroup dbs=test_dbgroup

Added sync "test_sync"

2.7 启动bucardo：

bucardo start

问题：

postgres:/home/postgres@db164>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 /usr/local/share/perl5/Bucardo.pm line 34.

BEGIN failed--compilation aborted at /usr/local/share/perl5/Bucardo.pm line 34.

Compilation failed in require at /usr/local/bin/bucardo line 820.

解决： apt-get install libboolean-perl 或 yum install perl-boolean

然后就可以启动了。

2.8 查看复制状态：

postgres:/home/postgres@db164>bucardo list all

-- customcodes:

There are no entries in the 'customcode' table.

-- customnames:

No customnames have been added yet

-- customcols:

No customcols have been added yet

-- dbgroups:

dbgroup: test_dbgroup Members: destination_mcldb2:target source_mcldb:source

-- databases:

Database: destination_mcldb2 Status: active Conn: psql -p 5434 -U pg94 -d mcldb2 -h 127.0.0.1

Database: source_mcldb Status: active Conn: psql -p 5432 -U postgres -d mcldb -h 127.0.0.1

-- relgroup:

Relgroup: test_relgroup DB: source_mcldb Members: public.t_new

Used in syncs: test_sync

-- syncs:

Sync "test_sync" Relgroup "test_relgroup" [Active]

DB group "test_dbgroup" destination_mcldb2:target source_mcldb:source

-- tables:

8. Table: public.t_new DB: source_mcldb PK: id (integer)

-- sequences:

There are no sequences.

2.9 验证同步：

在源库中插入一条记录，可以看到目标库中立即有一条数据同步过来。

另外可以在/var/log/bucardo/log.bucardo中看到新插入的数据的日志：

mcldb=# insert into t_new values(7,'mcl7');

目标库：

mcldb2=# select * from t_new ;

id | name

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

7 | mcl7

(1 row)

2.10 其他：

虽然bucardo中通过bucardo show all可以看到autosync_ddl配置：

[root@db164 ~]# bucardo show all

autosync_ddl = newcol

但真正增加一个新列并不能复制过去，在文档中也没有找到autosync_ddl的可选值的详细说明

bucardo5.3 postgres主从同步

相关 [bucardo5 postgres 同步] 推荐：

bucardo5.3 postgres主从同步

postgres创建表分区

监控Postgres数据库

Postgres 数据库分析工具

spring boot与spring batch、postgres及elasticsearch整合

Postgres-XL：基于PostgreSQL的开源分布式实现

管理大型 Postgres 数据库的 3 个技巧

linux时间同步

nginx + memcached session 同步

用wget同步ftp

相关文章

订阅