Tuesday, September 14, 2010

以 PostgreSQL + Slony-I 設定複寫機制 @ Ubuntu

先前寫了一篇以 PostgreSQL + PgPool 設定複寫機制 @ Ubuntu介紹PgPool的基本設定,這次來介紹另外一套在PostgreSQL上可以達成複寫功能的Slony-I

在PostgreSQL官網上對於複寫功能,有推薦三個third party軟體可以達成,原本我是想試著用PgCluster去做的,不過研究了一陣子才發現PgCluster可以說是修改版的PostgreSQL,所以等於要動到原本的PostgreSQL,與需求不符,所以最後改成Slony-I來做複寫。

這次也是使用兩台電腦,分別稱為Ubuntu A、Ubuntu B,安裝的是Ubuntu 10.04,IP分別是192.168.220.130與192.168.220.132,下面開始介紹我安裝與設定的步驟。




在兩台電腦上先安裝與設定PostgreSQL

$ sudo aptitude update
更新

$ sudo aptitude install postgresql
安裝psql

$ sudo aptitude install pgadmin3
安裝psql管理介面

$ sudo aptitude install postgresql-server-dev-8.4
安裝一些slony會用到的psql元件

$ sudo vi /etc/postgresql/8.4/main/postgresql.conf
修改設定檔內容,將#listen_addresses = 'localhost' 改成listen_addresses = '*'

$ sudo vi /etc/postgresql/8.4/main/pg_hba.conf
修改信任IP來源,為了方便測試,先加入下面這行,測試完成後再依安全性修改
host all all 0.0.0.0/0 trust

$ sudo -i -u postgres
切換至postgres帳號

$ psql
進入postgresql

=# ALTER USER postgres WITH PASSWORD '密碼';
設定postgres的密碼

=# \q
跳出postgresql

$ sudo /etc/init.d/postgresql-8.4 restart
重新啟動PGSQL



在兩台電腦上安裝Slony-I

$ wget http://main.slony.info/downloads/2.0/source/slony1-2.0.4.tar.bz2
下載Slony-I

$ tar -vxjf slony1-2.0.4.tar.bz2
解壓縮

$ cd slony1-2.0.4/
進入目錄

$ sudo ./configure
產生設定檔

$ sudo make all
產生安裝檔,成功的話會有下面這個訊息
All of Slony-I is successfully made. Ready to install

$ sudo make install
安裝Slony-I,成功的話會有下面這個訊息
All of Slony-I is successfully installed



在Ubuntu A上設定DB

$ sudo -i -u postgres
切換至postgres帳號

$ createdb --owner postgres test
建立一個由postgres帳號擁有,叫做test的DB

$ createlang -d test plpgsql
將PL/pgSQL語言安裝到test這個DB

$ psql --username postgres --dbname test
以postgres帳號進入 test DB

test=# createdb table tb_master(
id int primary key,
name char(16));
建立一個測試用table


在Ubuntu B上設定DB

$ sudo -i -u postgres
切換至postgres帳號

$ createdb --owner postgres testslave1
建立一個由postgres帳號擁有,叫做testslave1的DB

$ createlang -d testslave1 plpgsql
將PL/pgSQL語言安裝到testslave1這個DB

$ psql --username postgres --dbname testslave1
以postgres帳號進入 test DB

testslave1=# createdb table tb_master(
id int primary key,
name char(16));
建立一個測試用table



在Ubuntu A上撰寫Slony-I設定檔

$ vi configmaster.sh
建立一個用來設定的shell script檔,內容如下
#!/bin/sh

# psql程式安裝處
basebin=/usr/lib/postgresql/8.4/bin

# 叢集名稱,隨意
CLUSTER=slony_test1

# 設定Master與Slave的DB name
DBSERVER=test
DBSLAVE1=testslave1

# 設定Master與Slave的IP
HOSTSERVER=192.168.220.130
HOSTSLAVE1=192.168.220.132

# 設定Master與Slave的
DBSERVER_USER=postgres
DBSLAVE1_USER=postgres

# 下command給 slonik
$basebin/slonik<<_EOF_

# 設叢集名稱
cluster name=$CLUSTER;

# 設定節點
node 1 admin conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432';
node 2 admin conninfo='dbname=$DBSLAVE1 host=$HOSTSLAVE1 user=$DBSLAVE1_USER port=5432';

# 初始化叢集 init cluster ( id=1, comment='Node 1' );

# 設定要複寫的table,若有多個table要複寫需一一加入
create set ( id=1, origin=1, comment='All test tables' );
set add table ( set id=1, origin=1,id=1, fully qualified name='public.tb_master',comment='Table Master' );

# 設定儲存節點
store node ( id=2, comment='Slave Node',event node=1 );

# 設定儲存路徑
store path ( server=1, client=2,conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432');

# 設定資料提供與複寫方向
store listen ( origin=1, provider=1, receiver=2 );

_EOF_



$ vi commitdata.sh
建立一個啟動複寫的sh檔

#!/bin/sh

basebin=/usr/lib/postgresql/8.4/bin

CLUSTER=slony_test1

DBSERVER=test
DBSLAVE1=testslave1

HOSTSERVER=192.168.220.130
HOSTSLAVE1=192.168.220.132

DBSERVER_USER=postgres
DBSLAVE1_USER=postgres

$basebin/slonik<<_EOF_

cluster name=$CLUSTER;

# 設定節點
node 1 admin conninfo='dbname=$DBSERVER host=$HOSTSERVER user=$DBSERVER_USER port=5432';
node 2 admin conninfo='dbname=$DBSLAVE1 host=$HOSTSLAVE1 user=$DBSLAVE1_USER port=5432';

# 設定複寫
subscribe set ( id=1, provider=1, receiver=2, forward=no);

_EOF_




設定與啟動Slony-I

$ sh configmaster.sh
執行設定檔

$ /usr/lib/postgresql/8.4/bin/slon slony_test1 "dbname=testslave1 host=192.168.220.132 user=postgres port=5432"
$ /usr/lib/postgresql/8.4/bin/slon slony_test1 "dbname=test host=192.168.220.130 user=postgres port=5432"
啟動Slony與連線

$ commitdata.sh
啟動複寫


檢視結果

在Ubuntu A上
$ psql --username postgres --dbname test
以postgres帳號進入 test DB

test=# iinsert into tb_master (id,name) values(1,'aab');
test=# iinsert into tb_master (id,name) values(2,'abb');
test=# iinsert into tb_master (id,name) values(3,'abc');
隨意插入三筆資料

到Ubuntu B上
$ psql --username postgres --dbname testslave1
以postgres帳號進入 testslave1 DB

testslave1=# select * from tb_master;
查詢結果
id | name
----+------------------
1 | aab
2 | abb
3 | abc
(3 rows)

確認資料有同步,完工

3 comments:

  1. The copy watch is very nice, this is a high-end style with a clear display.Luxury uk Replica Watches I really like this replica watch.Luxury uk Replica tag-heuer watches The store service is very good, the quality of the watch is very good, and the price is very cost-effective. I think I am very cheap and inexpensive.

    ReplyDelete
  2. The store has been solving problems for me, the service is very patient, this is a perfect shopping experience. cheap Christian Louboutin shoes When I received Christian Louboutin shoes, I thought it was a good copy of Christian Louboutin sneakers. I like this online store, this Christian Louboutin shoes are simple and generous.cheap women Christian Louboutin sandals polos Very satisfied with this purchase.

    ReplyDelete
  3. My boyfriend likes to copy watches. When you buy it back, Best Luxury Watches Online this gorgeous replica watch is amazing. The watch design and function are very fresh and elegant. The quality is relatively perfect. replica watches This copy watch is very special, the quality is very good, the price is very cheap

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...