PostgreSQL逻辑复制之slony篇(2)

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相关表或者视图查看

4.1 配置成功；会在所在的数据库中生成一个schema

[postgres@Postgres201 ~]$ psql lottu lottu
psql (9.6.0)
Type "help" for help.

lottu=# \dn
      List of schemas
      Name      |  Owner 
----------------+----------
 _first_cluster | slony
 lottu          | lottu
 public        | postgres
(3 rows)

4.2 查看集群中的节点信息

lottu=# select * from _first_cluster.sl_node;
 no_id | no_active | no_comment  | no_failed
-------+-----------+-------------+-----------
    1 | t        | Master Node | f
    2 | t        | Slave node  | f
(2 rows)

4.3 查看集群中的集合信息

lottu=# select * from _first_cluster.sl_set;
 set_id | set_origin | set_locked | set_comment
--------+------------+------------+-------------
      1 |          1 |            | Our tables
(1 row)

4.4 查看集群中的表信息

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

5. 日常维护

5.1  Slony-I向现有集群中增加一个复制表

　　以表synctab2为例：

create table synctab2(id int primary key,name text,reg_time timestamp);

　　我们要创建一个新的表格集;脚本是这样的

[postgres@Postgres201 ~]$ cat slony_add_table_set.sh
#!/bin/sh
MASTERDB=lottu
SLAVEDB=lottu
HOST1=192.168.1.201
HOST2=192.168.1.202
DBUSER=slony
slonik<<_EOF_
cluster name = first_cluster;
node 1 admin conninfo = 'dbname=$MASTERDB host=$HOST1 user=$DBUSER';
node 2 admin conninfo = 'dbname=$SLAVEDB host=$HOST2 user=$DBUSER';
create set (id=2, origin=1, comment='a second replication set');
set add table (set id=2, origin=1, id=2, fully qualified name ='lottu.synctab2', comment='second table');
subscribe set(id=1, provider=1,receiver=2);
subscribe set(id=2, provider=1,receiver=2);
merge set(id=1, add id=2,origin=1);
_EOF_

　　执行slony_add_table_set.sh脚本

[postgres@Postgres201 ~]$ ./slony_add_table_set.sh
<stdin>:8 subscription in progress before mergeSet. waiting
<stdin>:8 subscription 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向现有集群中删除一个复制表

[postgres@Postgres201 ~]$ cat slony_drop_table.sh
#!/bin/sh
MASTERDB=lottu
SLAVEDB=lottu
HOST1=192.168.1.201
HOST2=192.168.1.202
DBUSER=slony
slonik<<_EOF_
cluster name = first_cluster;
node 1 admin conninfo = 'dbname=$MASTERDB host=$HOST1 user=$DBUSER';
node 2 admin conninfo = 'dbname=$SLAVEDB host=$HOST2 user=$DBUSER';
set drop table (id=2, origin=1);
_EOF_

　　执行slony_drop_table.sh脚本

[postgres@Postgres201 ~]$ ./slony_drop_table.sh

　　查看是否删除成功

lottu=# select * from _first_cluster.sl_table;
 tab_id | tab_reloid | tab_relname | tab_nspname | tab_set | tab_idxname  | tab_altered | tab_comment 
--------+------------+-------------+-------------+---------+--------------+-------------+--------------
      1 |      57420 | synctab    | lottu      |      1 | synctab_pkey | f          | sample table
(1 row)

5. 3删除slony