標籤:
主要資料庫: 172.16.254.21 連接埠:5432
從資料庫: 172.16.254.22 連接埠:5432
步驟1:主從均安裝slon
apt-get install slon-bin
步驟2:主從資料庫配置許可權,建立語言。
在主要資料庫中 vi /etc/postgresql/8.3/node/pg_hba.conf
添加一條記錄 host all repl 172.16.254.22/32 md5
在主從資料庫均執行以下操作:
shell>psql
node=#create role repl password ‘123456’ login superuser
#建立使用者repl,賦予超級使用者權限
node=# use node;
node=# create language plpgsql;
#建立語言plpgsql
步驟3:備份主要資料庫至從資料庫並恢複
#在主要資料庫備份要複製的資料庫
pg_dump node > node_20100220.dmp
#在從庫恢複資料庫node
postgres=#create database node;
postgres=#\q
shell>psql node < node_20100220.dmp ;
步驟4:在從資料庫/export/script目錄下建立一個指令碼replication,內容如下:
#!/bin/sh
SLONIK=/usr/bin/slonik
SLON=/usr/bin/slon
CLUSTER_NAME=mynode
MASTER=”host=172.16.254.21 dbname=node user=repl password=123456″
SLAVE=”host=172.16.254.22 dbname=node user=repl password=123456″
LOG=/root
uninstall()
{
$SLONIK << _EOF_
cluster name = $CLUSTER_NAME;
node 1 admin conninfo = ‘$MASTER‘;
node 2 admin conninfo = ‘$SLAVE‘;
# drop node (id = 2);
# drop node (id = 1);
uninstall node (id = 2);
uninstall node (id = 1);
_EOF_
}
install()
{
$SLONIK << _EOF_
# 定義叢集名字
cluster name = $CLUSTER_NAME;
# 定義兩個節點
node 1 admin conninfo = ‘$MASTER‘;
node 2 admin conninfo = ‘$SLAVE‘;
try
{
# 初始化主節點
init cluster (id=1, comment = ‘Master Node’);
# 建立一個複製集合
create set (id=1, origin=1, comment = ‘All tables’);
# 在複製集合中添加一個需要複製的表
set add table ( set id=1, origin=1,id=1, fully qualified name=’public.hxf’,comment=’Table hxf’ );
#set add sequence (set id = 1, origin = 1, id = 3, fully qualified name = ‘public.hxf_seq’);
# 建立從節點
store node (id = 2, comment = ‘Slave node’);
# 定義節點之間的訪問路徑
store path (server = 1, client = 2, conninfo = ‘$MASTER’);
store path (server = 2, client = 1, conninfo = ‘$SLAVE’);
# 定義事件監聽
store listen (origin = 1, provider = 1, receiver = 2);
store listen (origin = 2, provider = 2, receiver = 1);
# 訂閱複製集合
subscribe set (id = 1, provider = 1, receiver = 2, forward = no);
}
on success
{
echo ‘Install OK!‘;
}
on error
{
echo ‘Install FAIL!‘;
}
_EOF_
}
start()
{
# 啟動複製守護進程
$SLON $CLUSTER_NAME “$MASTER” >> /export/scripts/master.log &
$SLON $CLUSTER_NAME “$SLAVE” >> /export/scripts/slave.log &
}
stop()
{
# killall slon
kill -9 `ps axu|grep ‘dbname=node’ |grep -v grep|awk ‘{print $2}’`
}
case $1 in
‘install’)
install
;;
‘uninstall’)
uninstall
;;
‘start’)
start
;;
‘stop’)
stop
;;
*)
echo “usage: $0 {install|uninstall|start|stop} ”
;;
esac
步驟5:啟動
./replication start
步驟6:測試
1.在主要資料庫的空表public.hxf中添加一條記錄
insert into hxf(uid,uname) select 1,’huangxifeng‘;
2.在主要資料庫中看到有一條記錄
node=# select * from hxf;
uid | uname
—–+————-
1 | huangxifeng
(1 row)
3.在從資料庫中看到有一條記錄
node=# select * from hxf;
uid | uname
—–+————-
1 | huangxifeng
(1 row)
postgresql-slony-I同步複製配置步驟