PostgreSQL的多主应用部署

最新推荐文章于 2024-06-06 07:40:17 发布

weixin_34356555

最新推荐文章于 2024-06-06 07:40:17 发布

阅读量1.1k

点赞数

文章标签：数据库运维 shell

原文链接：https://my.oschina.net/Kenyon/blog/309306

版权

为什么80%的码农都做不了架构师？>>>

postgresql的现有版本没有提供内置的多主复制功能，这个一般需要自己写工具或使用其他第三方工具来实现。网上已经有不少现成的成熟的工具，比如rubyrep,bucardo，尤其是bucardo最近发布的version 5已经实现了多主多从以及跨数据库类别进行数据复制的功能，功能很丰富，操作维护也相对简单。这对跨机房的数据库双向同步特别有用，Bucardo采用的是perl写的触发器级别的异步数据复制，遵循的是BSD协议。下面其实也主要是描述一下其安装和简单使用过程。

题外话，bucardo这个英文单词是一种西班牙野山羊的名称，这种野山羊已经于2000年灭绝了，起用这个单词可能带有些纪念的意思。

一、背景功能
我想要实现类似如下的功能：其中主从之间的功能可以通过内置的stream replication来实现。
环境：
CentOS 6.5
DB 9.4beta2
master1 10.1.11.71 port 5432 db_name db_ken
master2 10.1.11.72 port 5432 db_name db_ken

二、安装

1.安装准备依赖包

[root@localhost ~]# yum install -y perl-ExtUtils-MakeMaker  perl-DBD-Pg  perl-Encode-Locale  perl-Sys-Syslog perl-boolean  perl-Time-HiRes  perl-Test-Simple  perl-Pod-Parser
--bucardo是用perl写的一个工具，有些perl的依赖包可能yum源找不到，没关系，可以下一个cpan，这是个perl库，里面的依赖包基本都有
[root@localhost ~]# yum install -y cpan
--命令行进入cpan,可以装以下依赖包，如下表示已经装好了
[root@localhost ~]# perl -MCPAN -e shell
Terminal does not support AddHistory.

cpan shell -- CPAN exploration and modules installation (v1.9402)
Enter 'h' for help.

cpan[1]> install boolean
CPAN: Storable loaded ok (v2.20)
Going to read '/root/.cpan/Metadata'
  Database was generated on Mon, 01 Sep 2014 06:41:02 GMT
boolean is up to date (0.42).

cpan[2]> install boolean
boolean is up to date (0.42).

cpan[3]> install Encode::Locale
Encode::Locale is up to date (1.03).

cpan[4]> install DBIx::Safe
DBIx::Safe is up to date (1.2.5).

2.下载安装bucardo
可以去他的官网下载： http://bucardo.org/wiki/Bucardo，目前最新版本是5.1.1

--指定安装路径
[root@localhost soft]# export INSTALL_BUCARDODIR=/home/postgres/bucardo
[root@localhost soft]# tar -zxvf Bucardo-5.1.1
[root@localhost soft]# cd Bucardo-5.1.1
[root@db1 Bucardo-5.1.1]# perl Makefile.PL
Writing Makefile for Bucardo

[root@db1 Bucardo-5.1.1]# make
cp bucardo.schema blib/share/bucardo.schema
cp Bucardo.pm blib/lib/Bucardo.pm
cp bucardo blib/script/bucardo
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/bucardo
Manifying blib/man1/bucardo.1pm
Manifying blib/man3/Bucardo.3pm

[root@localhost Bucardo-5.1.1]# make install
Installing /home/postgres/bucardo/Bucardo.pm
Installing /home/postgres/bucardo/bucardo.1pm
Installing /home/postgres/bucardo/Bucardo.3pm
Installing /home/postgres/bucardo/bucardo
Installing /home/postgres/bucardo/bucardo.schema
Appending installation info to /home/postgres/bucardo/perllocal.pod

--安装日志路径
[root@db1 postgres]# mkdir bucardo_ken
[root@db1 postgres]# chmod 777 bucardo_ken/

3.初始化bucardo
--切换到postgres用户的bucardo安装路径下

[root@db1 postgres]# su - postgres
[postgres@db1 ~]$ cd /home/postgres/bucardo/
[postgres@db1 bucardo]$ ./bucardo install -U postgres -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:           127.0.0.1
2. Port:           5432
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: 5

Change the PID directory to: /tmp  
Changed PID dir to: /tmp
Current connection settings:

1. Host:           127.0.0.1
2. Port:           5432
3. User:           postgres
4. Database:       postgres
5. PID directory:  /tmp

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

Postgres version is: 9.4beta
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.
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

--这个时候就有bucardo的基础数据在postgresql数据库上了，可以看一看

[postgres@localhost bucardo]$ psql bucardo bucardo

psql (9.4beta2)
Type "help" for help.

bucardo=# \dt
                 List of relations
 Schema  |          Name          | Type  |  Owner  
---------+------------------------+-------+---------
 bucardo | bucardo_config         | table | bucardo
bucardo | bucardo_custom_trigger | table | bucardo
bucardo | bucardo_log_message    | table | bucardo
bucardo | bucardo_rate           | table | bucardo
bucardo | customcode             | table | bucardo
bucardo | customcode_map         | table | bucardo
bucardo | customcols             | table | bucardo
bucardo | customname             | table | 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 | herd                   | table | bucardo
bucardo | herdmap                | table | bucardo
bucardo | sync                   | table | bucardo
bucardo | syncrun                | table | bucardo
bucardo | upgrade_log            | table | bucardo
(19 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)

4.配置bucardo
在配置bucardo前，调整一下pg_hba.conf文件的内容，各新增一条访问权限信息
host all all 10.1.11.71/32 trust --72服务器上
host all all 10.1.11.72/32 trust --71服务器上

a.增加db配置
在71服务器上配置（postgres用户）,在72服务器上反着也做一遍
--新增目标配置
[postgres@localhost bucardo]$ bucardo add dbs target_db_ken host=10.1.11.72 dbport=1949 dbuser=postgres dbname=db_ken
--新增本地配置
[postgres@localhost bucardo]$ bucardo add dbs source_db_ken host=127.0.0.1 dbport=1949 dbuser=postgres dbname=db_ken

b.增加db组配置
[postgres@localhost bucardo]$ bucardo add dbgroup tgroup source_db_ken:source target_db_ken:target

c.增加表集群
[postgres@localhost bucardo]$ bucardo add table tbl_ken herd=therd

d.增加同步信息
[postgres@localhost bucardo]$ bucardo add sync sync1 herd=therd dbs=tgroup ping=false

e.启动bucardo
[postgres@localhost bucardo]$ bucardo start

f.关闭bucardo
[postgres@localhost bucardo]$ bucardo stop

g.暂停/恢复同步
[postgres@localhost bucardo]$ bucardo parse/resume sync1

三、验证
在71和72服务器上都部署完bucardo并启动后，可以发现被同步的表可以在71和72上可以互相更新。

四、其他维护
(待续)

五、参考
http://www.oschina.net/translate/bucardo-5-multimaster-postgres-released
http://bucardo.org/wiki/Bucardo
http://my.oschina.net/lianshunke/blog/287286

转载于:https://my.oschina.net/Kenyon/blog/309306