PostgreSQL逻辑复制之slony篇_sql

概述https://www.cnblogs.com/lottu/p/9138593.html 　　Slony是PostgreSQL领域中最广泛的复制解决方案之一。它不仅是最古老的复制实现之一，它也是一个拥有最广泛的外部工具支持的工具，比如pgAdmin3。多年来，Slony是在PostgreSQL中复制数据的惟一可行的解决方案。Slony使用逻辑复制；Slony-I一般要求表有主键,或者唯一键；Slo 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/shMASTERDB=lottuSLAVEDB=lottuHOST1=192.168.1.201HOST2=1.202DBUSER=slonyslonik<<_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 clusterinit cluster ( ID=1,comment = Master Node');# group tables into setscreate 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.202DBUSER=slonyslonik<<_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 lottupsql (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 ~]$ psqlpsql (for help.postgres=# \c lottu lottuYou 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

=# \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_Failed -----+-----------+-------------+-----------     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 查看集群中的表信息

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      | 1tab_reloID  | 57420tab_relname | synctabtab_nspname | lottutab_set     | 1tab_IDxname | synctab_pkeytab_altered | ftab_comment | sample table-[ RECORD 2 ]--------------tab_ID      | 2tab_reloID  | 57840tab_relname | synctab2tab_nspname | lottutab_set     | 1tab_IDxname | synctab2_pkeytab_altered | ftab_comment | second table

5.2 Slony-I向现有集群中删除一个复制表

cat slony_drop_table.sh#!/bin/';set drop table (

　　执行slony_drop_table.sh脚本

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

　　查看是否删除成功

5. 3删除slony

cat slony_drop_node.';uninstall node (1);uninstall node (2);_EOF_

　　执行脚本如下：

[postgres@Postgres201 ~]$ ./slony_drop_node.4: NOTICE:  Slony-I: Please drop schema "_first_cluster"<stdin>:4: NOTICE:  drop cascades to 175 other objectsDETAIL:  drop cascades to table _first_cluster.sl_nodedrop cascades to table _first_cluster.sl_nodelockdrop cascades to table _first_cluster.sl_setdrop cascades to table _first_cluster.sl_setsyncdrop cascades to table _first_cluster.sl_tabledrop cascades to table _first_cluster.sl_sequencedrop cascades to table _first_cluster.sl_pathdrop cascades to table _first_cluster.sl_Listendrop cascades to table _first_cluster.sl_subscribedrop cascades to table _first_cluster.sl_eventdrop cascades to table _first_cluster.sl_confirmdrop cascades to table _first_cluster.sl_seqlogdrop cascades to function _first_cluster.sequencelastvalue(text)drop cascades to table _first_cluster.sl_log_1drop cascades to table _first_cluster.sl_log_2drop cascades to table _first_cluster.sl_log_scriptdrop cascades to table _first_cluster.sl_registrydrop cascades to table _first_cluster.sl_apply_statsdrop cascades to vIEw _first_cluster.sl_seqlastvaluedrop cascades to vIEw _first_cluster.sl_failover_targetsdrop cascades to sequence _first_cluster.sl_local_node_IDdrop cascades to sequence _first_cluster.sl_event_seqdrop cascades to sequence _first_cluster.sl_action_seqdrop cascades to sequence _first_cluster.sl_log_statusdrop cascades to table _first_cluster.sl_config_lockdrop cascades to table _first_cluster.sl_event_lockdrop cascades to table _first_cluster.sl_archive_counterdrop cascades to table _first_cluster.sl_componentsdrop cascades to type _first_cluster.vactablesdrop cascades to function _first_cluster.createevent(name,text)drop cascades to function _first_cluster.denyaccess()drop cascades to trigger _first_cluster_denyaccess on table lottu.synctabdrop cascades to function _first_cluster.lockedset()drop cascades to function _first_cluster.getlocalnodeID(name)drop cascades to function _first_cluster.getmoduLeversion()drop cascades to function _first_cluster.resetsession()drop cascades to function _first_cluster.logapply()drop cascades to function _first_cluster.logapplysetcachesize(integer)drop cascades to function _first_cluster.logapplysavestats(name,integer,interval)drop cascades to function _first_cluster.checkmoduLeversion()drop cascades to function _first_cluster.decode_tgargs(bytea)drop cascades to function _first_cluster.logtrigger()drop cascades to trigger _first_cluster_logtrigger on table lottu.synctabdrop cascades to function _first_cluster.terminatenodeconnections(integer)drop cascades to function _first_cluster.killbackend(integer,255);line-height:1.5;">function _first_cluster.seqtrack(integer,bigint)drop cascades to function _first_cluster.slon_quote_brute(text)drop cascades to function _first_cluster.slon_quote_input(text)drop cascades to function _first_cluster.slonyversionmajor()drop cascades to function _first_cluster.slonyversionminor()drop cascades to function _first_cluster.slonyversionpatchlevel()drop cascades to function _first_cluster.slonyversion()drop cascades to function _first_cluster.registry_set_int4(text,integer)drop cascades to function _first_cluster.registry_get_int4(text,255);line-height:1.5;">function _first_cluster.registry_set_text(text,255);line-height:1.5;">function _first_cluster.registry_get_text(text,255);line-height:1.5;">function _first_cluster.registry_set_timestamp(text,timestamp with time zone)drop cascades to function _first_cluster.registry_get_timestamp(text,255);line-height:1.5;">function _first_cluster.cleanupnodelock()drop cascades to function _first_cluster.registernodeconnection(integer)drop cascades to function _first_cluster.initializelocalnode(integer,255);line-height:1.5;">function _first_cluster.storenode(integer,255);line-height:1.5;">function _first_cluster.storenode_int(integer,255);line-height:1.5;">function _first_cluster.enablenode(integer)drop cascades to function _first_cluster.enablenode_int(integer)drop cascades to function _first_cluster.disablenode(integer)drop cascades to function _first_cluster.disablenode_int(integer)drop cascades to function _first_cluster.dropnode(integer[])drop cascades to function _first_cluster.dropnode_int(integer)drop cascades to function _first_cluster.prefailover(integer,boolean)drop cascades to function _first_cluster.Failednode(integer,integer[])drop cascades to function _first_cluster.Failednode2(integer,bigint,255);line-height:1.5;">function _first_cluster.Failednode3(integer,255);line-height:1.5;">function _first_cluster.failoverset_int(integer,255);line-height:1.5;">function _first_cluster.uninstallnode()drop cascades to function _first_cluster.clonenodeprepare(integer,255);line-height:1.5;">function _first_cluster.clonenodeprepare_int(integer,255);line-height:1.5;">function _first_cluster.clonenodefinish(integer,255);line-height:1.5;">function _first_cluster.storepath(integer,255);line-height:1.5;">function _first_cluster.storepath_int(integer,255);line-height:1.5;">function _first_cluster.droppath(integer,255);line-height:1.5;">function _first_cluster.droppath_int(integer,255);line-height:1.5;">function _first_cluster.storeListen(integer,255);line-height:1.5;">function _first_cluster.storeListen_int(integer,255);line-height:1.5;">function _first_cluster.dropListen(integer,255);line-height:1.5;">function _first_cluster.dropListen_int(integer,255);line-height:1.5;">function _first_cluster.storeset(integer,255);line-height:1.5;">function _first_cluster.storeset_int(integer,255);line-height:1.5;">function _first_cluster.lockset(integer)drop cascades to function _first_cluster.unlockset(integer)drop cascades to function _first_cluster.moveset(integer,255);line-height:1.5;">function _first_cluster.moveset_int(integer,bigint)and 75 other objects (see server log for List)<stdin>:5: NOTICE:  Slony-I: Please drop schema 5: NOTICE:  drop cascades to for List)