PostgreSQL邏輯復(fù)制之slony篇

Slony是PostgreSQL領(lǐng)域中最廣泛的復(fù)制解決方案之一。它不僅是最古老的復(fù)制實(shí)現(xiàn)之一，它也是一個(gè)擁有最廣泛的外部工具支持的工具，Slony使用邏輯復(fù)制；Slony-I一般要求表有主鍵,或者唯一鍵；Slony的工作不是基于PostgreSQL事務(wù)日志的，而是基于觸發(fā)器的；基于邏輯復(fù)制高可用性，PostgreSQL除了slony；還有Londiste,BDR等等后續(xù)文章會(huì)講到。

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

安裝完成！
執(zhí)行./configure時(shí)；會(huì)在當(dāng)前目錄是否可以找到pg_config命令；本例pg_config在/opt/pgsql96/bin目錄下；

2. Slony架構(gòu)圖

slony.png

3. 復(fù)制表

現(xiàn)有實(shí)驗(yàn)環(huán)境

主機(jī)名 IP 角色
PostgreSQL201 192.168.1.201 master
PostgreSQL202 192.168.1.202 slave

3.1 在兩臺(tái)數(shù)據(jù)庫(kù)中都創(chuàng)建一個(gè)slony的超級(jí)用戶；專為slony服務(wù)；

create user slony superuser password 'li0924';

3.2 本實(shí)驗(yàn)兩臺(tái)主機(jī)都有l(wèi)ottu數(shù)據(jù)庫(kù)；以lottu數(shù)據(jù)庫(kù)中的表作為實(shí)驗(yàn)對(duì)象；在兩個(gè)數(shù)據(jù)庫(kù)中以相同的方式創(chuàng)建該表synctab，因?yàn)楸斫Y(jié)構(gòu)不會(huì)自動(dòng)復(fù)制

create table synctab(id int primary key,name text);

3.3 在所有節(jié)點(diǎn)設(shè)置允許Slony-I用戶遠(yuǎn)程登錄；在pg_hba.conf文件添加

host    all             slony             192.168.1.0/24        trust

3.4 設(shè)置slony(master主機(jī)操作)
編寫一個(gè)slonik腳本用于注冊(cè)這些節(jié)點(diǎn)的腳本如下所示：

[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 ( id=1, comment = 'Master Node');
# group tables into sets
create set (id=1, origin=1, comment='Our tables');
set add table (set id=1, origin=1, id=1, fully qualified name = 'lottu.synctab', comment='sample table');
store node (id=2, comment = '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_

現(xiàn)在這個(gè)表在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主機(jī)執(zhí)行腳本

[postgres@Postgres201 ~]$ ./slony_setup.sh 
[postgres@Postgres201 ~]$ ./slony_subscribe.sh &
[1] 1225

定義了我們想要復(fù)制的東西之后，我們可以在每臺(tái)主機(jī)啟動(dòng)slon守護(hù)進(jìn)程

slon first_cluster 'host=192.168.1.201 dbname=lottu user=slony' &
slon first_cluster 'host=192.168.1.202 dbname=lottu user=slony' &

驗(yàn)證slony-I是否配置成功。
在master主機(jī)執(zhí)行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主機(jī)查看是否對(duì)應(yīng)變化

[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相關(guān)表或者視圖查看

4.1 配置成功；會(huì)在所在的數(shù)據(jù)庫(kù)中生成一個(gè)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 查看集群中的節(jié)點(diǎn)信息

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. 日常維護(hù)

5.1 Slony-I向現(xiàn)有集群中增加一個(gè)復(fù)制表
以表synctab2為例：

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

我們要?jiǎng)?chuàng)建一個(gè)新的表格集;腳本是這樣的

[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_

執(zhí)行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;
 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
      2 |      57840 | synctab2    | lottu       |       1 | synctab2_pkey | f           | second table
(2 rows)

5.2 Slony-I向現(xiàn)有集群中刪除一個(gè)復(fù)制表

[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_

執(zhí)行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)
  1. 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_

執(zhí)行腳本如下

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)

完美；一切歸零！

查考文獻(xiàn)
https://www.cnblogs.com/ilifeilong/p/7009322.html
https://www.cnblogs.com/gaojian/p/3196244.html

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • About:PostgreSQL About 《PostgreSQL 源碼分析系列》 PostgreSQL 源碼分...
    ty4z2008閱讀 8,564評(píng)論 1贊 40
  • feisky云計(jì)算、虛擬化與Linux技術(shù)筆記posts - 1014, comments - 298, trac...
    不排版閱讀 4,354評(píng)論 0贊 5
  • Spring Cloud為開發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見模式的工具（例如配置管理，服務(wù)發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,568評(píng)論 19贊 139
  • 2018年5月2日 星期三 晴 大風(fēng) 風(fēng)，總是在這個(gè)時(shí)節(jié)不停歇地“工作”。 上午原本計(jì)劃帶弟弟玩沙子去，可是小家伙...
    漫漫無(wú)憂閱讀 151評(píng)論 2贊 2
  • 20
    DidorDone閱讀 173評(píng)論 0贊 0

友情鏈接更多精彩內(nèi)容