工具下载
两边都需要安装且同步的表提前创建好！
解压缩
tar -jxvf slony1-2.1.4.tar.bz2
源端：192.168.1.100
目标端：192.168.1.101
编译安装
[highgo@pg962 slony1-2.1.4]$ ./configure --with-pgconfigdir=/home/highgo/hgdb/bin --with-perltools
make
make install 【root用户执行，否则/usr/local/etc权限不够】
All of Slony-I is successfully installed
[postgres@postgres1 data]$ which postgres
/opt/pg9.4.4/bin/postgres
[postgres@postgres1 data]$ which slon
/opt/pg9.4.4/bin/slon
[postgres@postgres1 data]$ which slonik
/opt/pg9.4.4/bin/slonik
修改文件：
postgresql.conf文件：
listen_addresses = '*'
port=5432
pg_hba.conf 文件：
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
创建测试用户和数据库
postgres=# create user slony with superuser password 'slony';
postgres=# create database master;
postgres=# alter database master owner to slony;
postgres=# create user slony with superuser password 'slony';
highgo=# create database slave;
highgo=# alter database slave owner to slony;
master=# create table test01 (id int primary key,name text);
CREATE TABLE
master=# insert into test01 values(1,'测试');
INSERT 0 1
master=# select * from test01;
id | name
----+------
1 | 测试
(1 row)
/usr/local/etc 目录下的slon_tools.conf-sample拷贝成slon_tools.conf
修改配置文件slon_tools.conf
$CLUSTER_NAME = 'cluster01';
$LOGDIR = '/opt/pg9.4.4/slonylog';
add_node(node => 1,
host => '192.168.1.100',
dbname => 'master',
port => 5432,
user => 'slony',
password => 'slony');
add_node(node => 2,
host => '192.168.1.101',
dbname => 'slave',
port => 5432,
user => 'slony',
password => '');
# add_node(node => 3,
# host => 'server3',
# dbname => 'database',
# port => 5432,
# user => 'postgres',
# password => 'slony');
#
# If the node should only receive event notifications from a
# single node (e.g. if it can't access the other nodes), you can
# specify a single parent. The downside to this approach is that
# if the parent goes down, your node becomes stranded.
# add_node(node => 4,
# parent => 3,
# host => 'server4',
# dbname => 'database',
# port => 5432,
# user => 'postgres',
# password => '');
}
# This array contains a list of tables that already have
# primary keys.设置复制集的配置项，同步哪些表和序列
第一部分，复制哪些有主键的表
"pkeyedtables" => [
'test01',
],
# For tables that have unique not null keys, but no primary
# key, enter their names and indexes here.
第二部分，没有主键但是有唯一键的表（注释掉）
# "keyedtables" => {
# 'table3' => 'index_on_table3',
# 'table4' => 'index_on_table4',
# },
# Sequences that need to be replicated should be entered here.
三 同步哪些序列，注释掉
# "sequences" => ['sequence1',
# 'sequence2',
# ],
},
复制集2也不使用，注释掉
# "set2" => {
# "set_id" => 2,
# "table_id" => 6,
# "sequence_id" => 3,
# "pkeyedtables" => ["table6"],
# "keyedtables" => {},
# "sequences" => [],
# },
};
传送工具文件至目标端：
scp slon_tools.conf 192.168.1.101:/usr/local/etc/
初始化集群：
[postgres@postgres1 data]$ slonik_init_cluster | slonik
<stdin>:6: Possible unsupported PostgreSQL version (90404) 9.4, defaulting to 8.4 support
<stdin>:9: Possible unsupported PostgreSQL version (90404) 9.4, defaulting to 8.4 support
<stdin>:10: Set up replication nodes
<stdin>:13: Next: configure paths for each node/origin
<stdin>:16: Replication nodes prepared
<stdin>:17: Please start a slon replication daemon for each node
启动守护进程：
100节点：（其中1代表master节点的节点号）
[postgres@postgres1 data]$ slon_start 1
Invoke slon for node 1 - /opt/pg9.4.4/bin//slon -s 1000 -d2 replication 'host=192.168.1.100 dbname=master user=slony port=5432 password=highgo123' > /opt/pg9.4.4/slony1/slony1/node1/master-2017-03-13.log 2>&1 &
Slon successfully started for cluster replication, node node1
PID [2708]
Start the watchdog process as well...
同样，启动2节点守护进程：（其中2代表slave节点的节点号）
[postgres@postgres2 ~]$ slon_start 2
Invoke slon for node 2 - /opt/pg9.4.4/bin//slon -s 1000 -d2 cluster01 'host=192.168.1.101 dbname=slave user=slony port=5432 password=highgo123' > /opt/pg9.4.4/slonylog/slony1/node2/slave-2017-03-13.log 2>&1 &
Slon successfully started for cluster cluster01, node node2
PID [4000]
Start the watchdog process as well...
创建数据集：
[postgres@postgres1 data]$ slonik_create_set 1 | slonik
<stdin>:11: Subscription set 1 created
<stdin>:12: Adding tables to the subscription set
<stdin>:16: Add primary keyed table public.test01
<stdin>:19: Adding sequences to the subscription set
<stdin>:20: All tables added
使用slonik_subscribe_set 1 2| slonik增加数据订阅者，其中1代表同步集号，第二数字2代表数据订阅者的节点号
[postgres@postgres1 data]$ slonik_subscribe_set 1 2| slonik
<stdin>:6: Subscribed nodes to set 1
配置完成。
测试同步效果：
master=# select * from test01 ;
id | name
----+------
(0 rows)
master=# insert into test01 values (1,'test');
INSERT 0 1
master=# insert into test01 values (2,'test2');
INSERT 0 1
master=# insert into test01 values (3,'测试3');
INSERT 0 1
master=# select * from test01 ;
id | name
----+-------
1 | test
2 | test2
3 | 测试3
(3 rows)
slave=# select * from test01 ;
id | name
----+-------
1 | test
2 | test2
3 | 测试3
(3 rows)
取消数据订阅者
[postgres@postgres1 replication]$ slonik_unsubscribe_set 1 2 | slonik
<stdin>:11: unsubscribed node 2 from set 1
删除数据集
[postgres@postgres1 replication]$ slonik_drop_set 1 | slonik
<stdin>:9: Dropped set 1
查看状态
[postgres@postgres1 replication]$ slon_status 1
There is no such node.
关闭守护进程
[postgres@postgres1 replication]$ slon_kill 1
slon_kill.pl... Killing all slon and slon_watchdog instances for the cluster replication
1. Kill slon watchdogs
slon for cluster replication killed - PID [3418]
2. Kill slon processes
slon for cluster replication killed - PID [3404]
slon for cluster replication killed - PID [3625]
卸载节点
[postgres@postgres1 replication]$ slonik_uninstall_nodes | slonik
<stdin>:4: NOTICE: Slony-I: Please drop schema "_replication"
<stdin>:4: NOTICE: drop cascades to 158 other objects
DETAIL: drop cascades to table _replication.sl_node
drop cascades to table _replication.sl_nodelock
drop cascades to table _replication.sl_set
。。。。。。
drop cascades to function _replication.setdroptable(integer)
drop cascades to function _replication.setdroptable_int(integer)
and 58 other objects (see server log for list)
重新查看状态：
[postgres@postgres1 replication]$ slon_status 1
There is no such node.
查看配置：
[postgres@postgres1 replication]$ slony_show_configuration
Slony Configuration
-------------------------------------
Slony-I Cluster: replication
Logs stored under /opt/pg9.4.4/slony1
Slony Binaries in: /opt/pg9.4.4/bin/
Node information
--------------------------------
Node: 1 Host: 192.168.1.100 User: slony Port: 5432 Forwarding? Parent: 0 Database: master
DSN: host=192.168.1.100 dbname=master user=slony port=5432 password=slony
Node: 2 Host: 192.168.1.101 User: slony Port: 5432 Forwarding? Parent: 0 Database: slave
DSN: host=192.168.1.101 dbname=slave user=slony port=5432 password=slony
一个关于slony-i问题的国外网站