PostgreSQL逻辑复制之slony篇

文章目录

[隐藏]

• 1. 安装Slony
• 2. Slony架构图
• 3. 复制表
• 4. Slony-I相关表或者视图查看
  • 4.1 配置成功；会在所在的数据库中生成一个schema
  • 4.2 查看集群中的节点信息
  • 4.3 查看集群中的集合信息
  • 4.4 查看集群中的表信息
• 5. 日常维护
  • 5.1 Slony-I向现有集群中增加一个复制表
  • 5.2 Slony-I向现有集群中删除一个复制表
  • 5.3 删除slony

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. 复制表

现有实验环境

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=192.168.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 ( Master Node');  # group tables into sets  create set (Our tables');  set add table (set lottu.synctab', comment='sample table');  store node (Slave node', event node=1);  store path (server = 1, client = 2, conninfo='dbname=$MASTERDB host=$HOST1 user=$DBUSER');  store path (server = 2, client = 1, conninfo='dbname=$SLAVEDB host=$HOST2 user=$DBUSER');  _EOF_  

现在这个表在Slony的控制下，我们可以开始订阅脚本如下所示：

[postgres@Postgres201 ~]$ cat slony_subscribe.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';  subscribe set ( id = 1, provider = 1, receiver = 2, forward = 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' &  

验证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 OR UPDATE ON synctab FOR EACH ROW EXECUTE PROCEDURE _first_cluster.logtrigger('_first_cluster', '1', 'k')      _first_cluster_truncatetrigger BEFORE TRUNCATE ON synctab FOR EACH STATEMENT EXECUTE PROCEDURE _first_cluster.log_truncate('1')  Disabled user triggers:      _first_cluster_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON synctab FOR EACH ROW EXECUTE PROCEDURE _first_cluster.denyaccess('_first_cluster')      _first_cluster_truncatedeny BEFORE TRUNCATE ON synctab FOR EACH STATEMENT EXECUTE PROCEDURE _first_cluster.deny_truncate()  lottu=# insert into synctab values (1001,'lottu');  INSERT 0 1  

在slave主机查看是否对应变化

[postgres@Postgres202 ~]$ psql  psql (9.6.0)  Type "help" 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相关表或者视图查看

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;   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. 日常维护

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 (a second replication set');  set add table (set lottu.synctab2', comment='second table');  subscribe set( tab_reloid | tab_relname | tab_nspname | tab_set |  tab_idxname  | tab_altered | tab_comment  --------+------------+-------------+-------------+---------+---------------+-------------+--------------        1 |      57420 | synctab     | lottu       |       1 | synctab_pkey  | f           | sample table        2 |      57840 | synctab2    | lottu       |       1 | synctab2_pkey | f           | second table  (2 rows)  

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 ( 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

[postgres@Postgres201 ~]$ cat slony_drop_node.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';  uninstall node (id = 1);  uninstall node (id = 2);  _EOF_  

执行脚本如下

postgres@Postgres201 ~]$ ./slony_drop_node.sh  <stdin>:4: NOTICE:  Slony-I: Please drop schema "_first_cluster"  <stdin>:4: NOTICE:  drop cascades to 175 other objects  .........  drop cascades to function _first_cluster.unlockset(integer)  drop cascades to function _first_cluster.moveset(integer,integer)  drop cascades to function _first_cluster.moveset_int(integer,integer,integer,bigint)  and 75 other objects (see server log for list)  

完美；一切归零！

原文出处：jianshu -> https://www.jianshu.com/p/89151e8651d5