https://www.cnblogs.com/lottu/p/9138593.html
Slony是Postgresql领域中最广泛的复制解决方案之一。它不仅是最古老的复制实现之一，它也是一个拥有最广泛的外部工具支持的工具，比如pgAdmin3。多年来，Slony是在Postgresql中复制数据的惟一可行的解决方案。Slony使用逻辑复制；Slony-I一般要求表有主键,或者唯一键；Slony的工作不是基于Postgresql事务日志的；而是基于触发器的；基于逻辑复制高可用性；Postgresql除了slony；还有Londiste,BDR等等后续文章会讲到
1. 安装Slony
下载地址：http://www.slony.info；安装步骤:
- # tar -jxvf slony1-2.2.5.tar.bz2
- # cd slony1-2.2.5 ./configure --with-pgconfigdir=/opt/pgsql96/bin make make install
安装完成！
执行./configure时；会在当前目录是否可以找到pg_config命令；本例pg_config在/opt/pgsql96/bin目录下；
2. Slony架构图
3. 复制表
现有实验环境：
| 主机名 | IP | 角色 |
| Postgresql201 | 192.168.1.201 | master |
| Postgresql202 | 192.168.1.202 | slave |
3.1在两台数据库中都创建一个slony的超级用户；专为slony服务
create user slony superuser password 'li0924';
3.2 本实验两台主机都有lottu数据库；以lottu数据库中的表作为实验对象；在两个数据库中以相同的方式创建该表synctab，因为表结构不会自动复制。
create table synctab(id int primary key,name text);
3.3 在所有节点设置允许Slony-I用户远程登录；在pg_hba.conf文件添加
- host all slony 192.168.1.0/24 trust
3.4 设置slony(在master主机操作)
编写一个slonik脚本用于注册这些节点的脚本如下所示：
- [postgres@Postgres201 ~]$ cat slony_setup.sh
- #!/bin/sh
- MASTERDB=lottu
- SLAVEDB=lottu
- HOST1=192.168.1.201
- HOST2=1.202
- DBUSER=slony
- slonik<<_EOF_
- cluster name = first_cluster;
- # define nodes (this is needed by pretty much
- # all slonik scripts)
- node 1 admin conninfo = 'dbname=$MASTERDB host=$HOST1 user=$DBUSER';
- node 2 admin conninfo = dbname=$SLAVEDB host=$HOST2 user=$DBUSER';
- # init cluster
- init cluster ( id=1,comment = Master Node');
- # group tables into sets
- create set (Our tables');
- set add table (set lottu.synctab',0);line-height:1.5;">sample table');
- store node (2,0);line-height:1.5;">Slave node1);
- store path (server = ');
- store path (server = ');
- _EOF_
现在这个表在Slony的控制下，我们可以开始订阅脚本如下所示：
cat slony_subscribe.1.202
DBUSER=slony
slonik<<_EOF_
cluster name = first_cluster;
node ';
subscribe set ( id = no);
_EOF_
在master主机执行脚本
- [postgres@Postgres201 ~]$ ./slony_setup.sh
- [postgres@Postgres201 ~]$ ./slony_subscribe.sh &
- [1] 1225
定义了我们想要复制的东西之后，我们可以在每台主机启动slon守护进程
- slon first_cluster host=192.168.1.201 dbname=lottu user=slony' &
- slon first_cluster host=192.168.1.202 dbname=lottu user=slony' &
3.5 验证slony-I是否配置成功？
在master主机执行dml操作
- [postgres@Postgres201 ~]$ psql lottu lottu
- psql (9.6.0)
- Type "help" for help.
- lottu=# \d synctab
- Table "lottu.synctab"
- Column | Type | Modifiers
- --------+---------+-----------
- id | integer | not null
- name text |
- Indexes:
- "synctab_pkey" PRIMARY KEY,btree (id)
- Triggers:
- _first_cluster_logtrigger AFTER INSERT OR DELETE UPDATE ON synctab FOR EACH ROW EXECUTE PROCEDURE _first_cluster.logtrigger(_first_cluster1k')
- _first_cluster_truncatetrigger BEFORE TRUNCATE FOR EACH STATEMENT PROCEDURE _first_cluster.log_truncate(')
- Disabled user triggers:
- _first_cluster_denyaccess BEFORE PROCEDURE _first_cluster.denyaccess(')
- _first_cluster_truncatedeny BEFORE PROCEDURE _first_cluster.deny_truncate()
- lottu=# insert into synctab values (1001,0);line-height:1.5;">lottu');
- 0 1
在slave主机查看是否对应变化
postgres@Postgres202 ~]$ psql
psql (for help.
postgres=# \c lottu lottu
You are now connected to database "lottu" as user "lottu".
lottu=> select * from synctab ;
id | name
----+-------
1001 | lottu
(1 row)
4. Slony-I相关表或者视图查看
=# \dn
List of schemas
Name | Owner
--------------+----------
_first_cluster | slony
lottu | lottu
public | postgres
(3 rows)
4.2 查看集群中的节点信息
- lottufrom _first_cluster.sl_node;
- no_id | no_active | no_comment | no_@R_403_159@
- -----+-----------+-------------+-----------
- 1 | t | Master Node | f
- 2 | Slave node | f
- (2 rows)
4.3 查看集群中的集合信息
- lottufrom _first_cluster.sl_set;
- set_id | set_origin | set_locked | set_comment
- ------+------------+------------+-------------
- | | | Our tables
- (1 row)
4.4 查看集群中的表信息
from _first_cluster.sl_table;
- RECORD 1 ]-----------
tab_id 1
tab_reloid 57420
tab_relname | synctab
tab_nspname | lottu
tab_set 1
tab_idxname | synctab_pkey
tab_altered | f
tab_comment | sample table
5. 日常维护
5.1 Slony-I向现有集群中增加一个复制表
以表synctab2为例：
table synctab2(id text,reg_time timestamp);
我们要创建一个新的表格集;脚本是这样的
cat slony_add_table_set.';
create set (a second replication setlottu.synctab2second table');
subscribe set(2);
subscribe set(2);
merge set(1);
_EOF_
执行slony_add_table_set.sh脚本
- [postgres@Postgres201 ~]$ ./slony_add_table_set.sh
- <stdin>:8 subscription in progress before mergeSet. waiting
- <stdin>:in progress before mergeSet. waiting
查看是否添加成功
- lottu=# select * from _first_cluster.sl_table;
- -[ RECORD 1 ]--------------
- tab_id | 1
- tab_reloid | 57420
- tab_relname | synctab
- tab_nspname | lottu
- tab_set | 1
- tab_idxname | synctab_pkey
- tab_altered | f
- tab_comment | sample table
- -[ RECORD 2 ]--------------
- tab_id | 2
- tab_reloid | 57840
- tab_relname | synctab2
- tab_nspname | lottu
- tab_set | 1
- tab_idxname | synctab2_pkey
- tab_altered | f
- tab_comment | second table
5.2 Slony-I向现有集群中删除一个复制表
cat slony_drop_table.sh
#!/bin/';
set drop table (
