磨砺技术珠矶，践行数据之道，追求卓越价值
回到上一级页面： PostgreSQL集群方案相关索引页 回到顶级页面：PostgreSQL索引页
参考如下链接：
http://lets.postgresql.jp/documents/technical/pgpool/4/
这里我使用slony-I 2.1.0。
下载地址如下：
http://slony.info/downloads/2.1/source/
安装过程:
我有两台机器，
机器A: 作为master数据库，IP地址： 192.168.10.102
机器B: 作为slave数据库，IP地址： 192.168.10.101
两台机器上都安装了Postgresql数据库9.1.2版本，安装目录位于: /usr/local/pgsql
master数据库和slave数据库都需要安装slony-I，安装过程一样：
将下载后的 slony1-2.1.0.tar.bz2 拷贝到如下目录：/soft
解压：tar -jxvf slony1-2.1.0.tar.bz2
给目录赋予权限：chown postgres:postgres /soft/slony1-2.1.0
给postgresql的安装目录也赋予权限：chown -R postgres:postgres /usr/local/pgsql
执行 configure：
cd /soft/slony1-2.1.0
./configure --prefix=/usr/local/slony --with-pgconfigdir=/usr/local/pgsql/bin
执行make 和 make install : make && make install
修改PATH环境变量：vim ~/.bash_profile：
export PATH=$PATH:/usr/local/pgsql/bin:/usr/local/slony/bin
准备好复制用的数据库：
[root@pg102 ~]# su - postgres [postgres@pg102 ~]$ createdb testdb3 [postgres@pg102 ~]$
准备好复制用的表：
[postgres@pg102 ~]$ psql testdb3 psql (9.1.2) Type "help" for help. testdb3=# CREATE TABLE t1 (id SERIAL PRIMARY KEY, comment TEXT, testdb3(# ins_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP); NOTICE: CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE testdb3=#
查看建好的表：
testdb3=# d t1 Table "public.t1" Column | Type | Modifiers ----------+-----------------------------+------------------------------------------------- id | integer | not null default nextval('t1_id_seq'::regclass) comment | text | ins_time | timestamp without time zone | default now() Indexes: "t1_pkey" PRIMARY KEY, btree (id)
需要注意到是：
如果想使用slony来复制数据，表必须是带有主键的。
我前面建表的时候，因为字段类型serial的原因，隐含地建立了一个sequence。这个在之后要使得slony知道此点。
还有一点，DDL动作是不会被复制到，因此，需要我手动向slave数据库节点建库和表：
[postgres@pg102 ~]$ pg_dump -C -s testdb3 | psql -h 192.168.10.101 SET SET SET SET SET CREATE DATABASE ALTER DATABASE You are now connected to database "testdb3" as user "postgres". SET SET SET SET SET CREATE EXTENSION COMMENT SET SET SET CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE ALTER TABLE ALTER TABLE REVOKE REVOKE GRANT GRANT [postgres@pg102 ~]$
接下来，开始进行slony的设定：
声明slony cluster名称、注册node、初始化cluster...(请注意table和相关联的sequence都需要注册)
[postgres@pg102 ~]$ cat setup.sh #!/bin/sh CLUSTERNAME=testdb3_cluster MASTERDBNAME=testdb3 SLAVEDBNAME=testdb3 MASTERHOST=192.168.10.102 SLAVEHOST=192.168.10.101 REPLICATIONUSER=postgres /usr/local/slony/bin/slonik <<_EOF_ #-- # define the namespace the replication system # uses in our example it is slony_example #-- cluster name = $CLUSTERNAME; #-- # admin conninfo's are used by slonik to connect to # the nodes one for eachnode on each side of the cluster, # the syntax is that of PQconnectdb in # the C-API # -- node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'; node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'; #-- # init the first node. Its id MUST be 1. This creates # the schema _$CLUSTERNAME containing all replication # system specific database objects. #-- init cluster ( id=1, comment = 'Master Node'); #-- # Slony-I organizes tables into sets. The smallest unit # a node can subscribe is a set. The master or origin of # the set is node 1. #-- create set (id=1, origin=1, comment='All testdb3 tables'); set add table (set id=1, origin=1, id=1, fully qualified name = 'public.t1', comment='t1 table'); set add sequence (set id=1, origin = 1, id = 1, fully qualified name = 'public.t1_id_seq', comment = 't1 id sequence'); #-- # Create the second node (the slave) tell the 2 nodes how # to connect to each other and how they should listen for events. #-- store node (id=2, comment = 'Slave Node', event node=1); store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'); store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'); _EOF_ [postgres@pg102 ~]$
sh setup.sh
完成了上述在master数据库节点发布的命令之后，下面在 master数据库和slave数据库节点分别启动slon daemon:
/usr/local/slony/bin/slon testdb3_cluster "dbname=testdb3 user=postgres host=localhost"&
/usr/local/slony/bin/slon testdb3_cluster "dbname=testdb3 user=postgres host=localhost" &
从master数据库节点，开始执行订阅过程：
[postgres@pg102 ~]$ cat subscribe.sh #!/bin/sh CLUSTERNAME=testdb3_cluster MASTERDBNAME=testdb3 SLAVEDBNAME=testdb3 MASTERHOST=192.168.10.102 SLAVEHOST=192.168.10.101 REPLICATIONUSER=postgres /usr/local/slony/bin/slonik <<_EOF_ # ---- # This defines which namespace the replication system uses # ---- cluster name = $CLUSTERNAME; # ---- # Admin conninfo's are used by the slonik program to connect # to the node databases. So these are the PQconnectdb arguments # that connect from the administrators workstation (where # slonik is executed). # ---- node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'; node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'; # ---- # Node 2 subscribes set 1 # ---- subscribe set ( id = 1, provider = 1, receiver = 2, forward = no); _EOF_ [postgres@pg102 ~]$
sh subscribe.sh
验证slony-I 复制生效：
在master数据库节点，增加数据：
testdb3=# INSERT INTO t1(comment) VALUES('replication test'); INSERT 0 1 testdb3=# select * from t1; id | comment | ins_time ----+------------------+---------------------------- 1 | replication test | 2013-07-17 16:26:37.320865 (1 row) testdb3=#
此时，在 slave数据库节点，也可以看到同样的数据：已经成功。
[root@pg101 ~]# su - postgres [postgres@pg101 ~]$ psql testdb3 psql (9.1.2) Type "help" for help. testdb3=# select * from t1; id | comment | ins_time ----+------------------+---------------------------- 1 | replication test | 2013-07-17 16:26:37.320865 (1 row) testdb3=#
最后，可以再看看在master数据库和slave数据库，针对master数据库和slave数据库的表都可以看到增加了trigger:
master数据库上：
testdb3=# d t1 Table "public.t1" Column | Type | Modifiers ----------+-----------------------------+------------------------------------------------- id | integer | not null default nextval('t1_id_seq'::regclass) comment | text | ins_time | timestamp without time zone | default now() Indexes: "t1_pkey" PRIMARY KEY, btree (id) Triggers: _testdb3_cluster_logtrigger AFTER INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE _testdb3_cluster.logtrigger('_testdb3_cluster', '1', 'k') _testdb3_cluster_truncatetrigger BEFORE TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE _testdb3_cluster.log_truncate('1') Disabled triggers: _testdb3_cluster_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE _testdb3_cluster.denyaccess('_testdb3_cluster') _testdb3_cluster_truncatedeny BEFORE TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE _testdb3_cluster.deny_truncate() testdb3=#
slave数据库上：
testdb3=# d t1 Table "public.t1" Column | Type | Modifiers ----------+-----------------------------+------------------------------------------------- id | integer | not null default nextval('t1_id_seq'::regclass) comment | text | ins_time | timestamp without time zone | default now() Indexes: "t1_pkey" PRIMARY KEY, btree (id) Triggers: _testdb3_cluster_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE _testdb3_cluster.denyaccess('_testdb3_cluster') _testdb3_cluster_truncatedeny BEFORE TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE _testdb3_cluster.deny_truncate() Disabled triggers: _testdb3_cluster_logtrigger AFTER INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE _testdb3_cluster.logtrigger('_testdb3_cluster', '1', 'k') _testdb3_cluster_truncatetrigger BEFORE TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE _testdb3_cluster.log_truncate('1') testdb3=#
回到上一级页面： PostgreSQL集群方案相关索引页 回到顶级页面：PostgreSQL索引页
磨砺技术珠矶，践行数据之道，追求卓越价值