postgresql+slony-i安裝配置主從

標籤：postgresql主從

postgresql+slony-i安裝配置主從

slon軟體：
slony1-1.2.6

http://slony.info/downloads/1.2/source/

postgresql：

http://www.postgresql.org/download/
http://www.postgresql.org/ftp/source/v8.1.23/

 

一、postgresql安裝

 

方法1.rpm包安裝postfresql：
所需軟體包：
postgresql92-9.2.10-1PGDG.rhel5.i386.rpm
postgresql92-server-9.2.10-1PGDG.rhel5.i386.rpm
postgresql92-contrib-9.2.10-1PGDG.rhel5.i386.rpm 
postgresql92-devel-9.2.10-1PGDG.rhel5.i386.rpm 
postgresql92-libs-9.2.10-1PGDG.rhel5.i386.rpm
uuid-1.5.1-3.el5.i386.rpm

硬體IP：192.168.30.121(主)
　　　　192.168.20.122(從)

主、從伺服器安裝方法相同：

1，linux建立postgres使用者及使用者組
groupadd postgres
useradd -g postgres postgres

2.安裝包安裝順序：
rpm -ivh postgresql92-libs-9.2.10-1PGDG.rhel5.i386.rpm
rpm -ivh postgresql92-9.2.10-1PGDG.rhel5.i386.rpm 
rpm -ivh uuid-1.5.1-3.el5.i386.rpm #安裝contrib所依賴包
rpm -ivh postgresql92-devel-9.2.10-1PGDG.rhel5.i386.rpm #主從同步所依賴包
rpm -ivh postgresql92-server-9.2.10-1PGDG.rhel5.i386.rpm 
rpm -ivh postgresql92-contrib-9.2.10-1PGDG.rhel5.i386.rpm

3.初始化PostgreSQL 資料庫：
service postgresql-9.2 initdb

4.啟動
service postgresql-9.2 start

5.把PostgreSQL 服務加入到啟動列表
chkconfig postgresql-9.2 on 
chkconfig --list|grep postgres

 

方法2.源碼安裝：

1，linux建立postgres使用者及使用者組
groupadd postgres
useradd -g postgres postgres

2，解壓壓縮包

[[email protected]]# tar -xzvf /var/local/pgsql/postgresql-9.2.10.tar.gz

進入解壓目錄： cd /var/local/pgsql/postgresql-9.2.10
3，編譯安裝：

建立安裝目錄和資料目錄

mkdir /usr/local/pgsql 
mkdir /home/postgres/data

[[email protected] postgresql-9.2.10]# ./configure --prefix=/usr/local/pgsql -localstatedir=/home/postgres/data

處理報錯資訊：
checking for readline... no
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn‘t looking in the proper directory.
Use --without-readline to disable readline support.

解決：

缺少readline-devel依賴包

安裝 readline-devel包

我這裡選擇的是yum安裝，大家可以去網上下載一個適合自己的版本去安裝
yum install readline-devel-5.1-3.el5

安裝完畢後重新編譯即可

重新編譯：

[[email protected] postgresql-9.2.10]# ./configure --prefix=/usr/local/pgsql -localstatedir=/home/postgres/data

 沒有error即編譯正常可以安裝

[[email protected] postgresql-9.2.10]# make
All of PostgreSQL successfully made. Ready to install.

 

[[email protected] postgresql-9.2.10]# make install
PostgreSQL installation complete.

4.安裝完畢修改資料目錄許可權
chown -R postgres:postgres /usr/local/pgsql/
chown -R postgres:postgres /home/postgres/data/

修改postgres使用者的.bash_profile檔案：

[[email protected] ~]$ vi .bash_profile 
添加：

PGLIB=/usr/local/pgsql/lib
PGDATA=/home/postgres/data
PATH=$PATH:/usr/local/pgsql/bin 
MANPATH=$MANPATH:/usr/local/pgsql/man
export PGLIB PGDATA PATH MANPATH

[[email protected] ~]$ source .bash_profile

 

5.初始postgresql並啟動postgresql

初始化：
[[email protected] ~]$ /usr/local/pgsql/bin/initdb /home/postgres/data
Success. You can now start the database server using:

/usr/local/pgsql/bin/postmaster -D /home/postgres/data
or
/usr/local/pgsql/bin/pg_ctl -D /home/postgres/data -l logfile start

 啟動：

[[email protected] ~]$ /usr/local/pgsql/bin/pg_ctl -D /home/postgres/data start

============================================================================================

從庫安裝方式和主庫postgresql安裝一樣

============================================================================================

二，編譯安裝slony-i

1、解壓軟體包：

[[email protected] local]# tar -xjvf /var/local/slony1-1.2.6.tar.bz2

 

2、編譯安裝軟體包

[[email protected] ~]# cd /var/local/slony1-1.2.6
[[email protected] slony1-1.2.6]# ./configure --with-pgsourcetree=/usr/local/pgsql/bin

 

[[email protected] slony1-1.2.6]# make
All of Slony-I is successfully made. Ready to install

 

[[email protected] slony1-1.2.6]# make install
All of Slony-I is successfully installed

 

===================================================================================

在主庫從庫均要安裝slony，安裝方式同上

====================================================================================

 三,主從配置

1.postgresql添加複製使用者

 su - postgres 使用者下

[[email protected] ~]$ psql
psql (9.2.10)
Type "help" for help.

postgres=#create role repl password ‘123456‘ login superuser replication;

主從都執行此語句

 

2.postgresql設定檔

postgresql.conf

主從都改：

vi /home/postgres/data/postgresql.conf

添加： listen_addresses = ‘*‘

主庫pg_hba.conf

 vi /home/postgres/data/pg_hba.conf 
添加：
host all repl 192.168.30.122/32 md5

 

從庫pg_hba.conf

 vi /home/postgres/data/pg_hba.conf 
添加：
host all repl 192.168.30.121/32 md5

 

 修改配置後重啟主從伺服器都重啟postgresql服務：

[[email protected] ~]$/usr/local/pgsql/bin/pg_ctl -D /home/postgres/data restart

 

3.建立測試資料庫和測試表

 

主從庫均需要建立資料庫和表，slony不能同步DDL語句。

以下以在主要資料庫伺服器上建立主要資料庫和資料表 test 為例見解，其他資料庫和資料表請參考建立.

 

/usr/local/pgsql/bin/createdb test

cat sql.txt |psql -Urepl test  -W123456  
sql.txt 檔案最好是 UTF-8 格式,特別是存在中文字元時) 例：sql.txt 
CREATE TABLE tb_depart(Id int primary key,Name char(8)); 
在從資料庫伺服器上建立與主要資料庫伺服器上相同的資料庫test

建立後查看：
[[email protected] ~]$ psql -Urepl test

test=# \d
List of relations
Schema | Name | Type | Owner 
--------+-----------+-------+--------
public | tb_depart | table | repl
(1 row)

 

test=# \d tb_depart;
Table "public.tb_depart"
Column | Type | Modifiers 
--------+--------------+-----------
id | integer | not null
name | character(8) | 
Indexes:
"tb_depart_pkey" PRIMARY KEY, btree (id)

test=#\q

 

在從資料庫伺服器上建立與主要資料庫伺服器上相同的資料庫test,同樣的表

 4.slony-i配置主從同步

只需要在從庫配置只需即可：
在/home/postgres/目錄下建立指令碼檔案：

slony_0.sh檔案內容如下：

 

#!/bin/sh 
/usr/local/pgsql/bin/slonik << _END_ 
#
# Define cluster namespace and node connection information # 
#叢集名稱 
cluster name = testdb;
# 定義複製節點 
node 1 admin conninfo = ‘dbname=test host=192.168.30.121 port=5432 user=repl‘; 
node 2 admin conninfo = ‘dbname=test host=localhost port=5432 user=repl‘; 
DROP SET (id=1, origin=1); 
uninstall node (id=1); 
uninstall node (id=2); 
echo ‘Drop testdb set‘; 
_END_

slony_1.sh檔案內容如下：

#!/bin/sh 
/usr/local/pgsql/bin/slonik << _END_ 
cluster name = testdb; 
# 定義複製節點 
node 1 admin conninfo = ‘dbname=test host=192.168.30.121 port=5432 user=repl‘; 
node 2 admin conninfo = ‘dbname=test host=localhost port=5432 user=repl‘; 
echo ‘Cluster defined, nodes identified‘; 
# 初始化叢集,id從1開始 
init cluster (id=1, comment=‘Master Node‘); 
# 設定儲存節點 
store node (id=2, comment=‘Slave Node‘); 
echo ‘Nodes defined‘; 
# 設定儲存路徑 
store path (server=1, client=2, conninfo=‘dbname=test host=192.168.30.121 port=5432 user=repl‘); 
store path (server=2, client=1, conninfo=‘dbname=test host=localhost port=5432 user=repl‘); 
#設定偵聽事件和訂閱者向,複製中角色,主節點是原始提供者,從節點是接受者 
store listen (origin=1, provider = 1, receiver =2); 
store listen (origin=2, provider = 2, receiver =1); 
_END_

 

slony_2.sh檔案內容如下：

#!/bin/sh 
/usr/local/pgsql/bin/slonik << _END_ # 
# Define cluster namespace and node connection information # 
cluster name = testdb; 
node 1 admin conninfo = ‘dbname=test host=192.168.30.121 port=5432 user=repl‘; 
node 2 admin conninfo = ‘dbname=test host=localhost port=5432 user=repl‘; 
# 設定參與同步的資料表 
#先建立一個複製集,id也是從1開始 
#向自己的複製集種添加表,每個需要複製的表一條set命令 
#id從1開始,逐次遞加,步進為1； 
#fully qualified name是表的全稱：模式名.表名 
#這裡的複製集id需要和前面建立的複製集id一致 
#假如某個表沒有主鍵,但是有唯一鍵字,那麼可以用key關鍵字 
#指定其為複製鍵字,如下面的key參數 
#set add table ( set id = 1, origin = 1,id = 4, fully qualified name = ‘public.history‘,key = "column",comment = ‘Table history‘ ); 
#對於沒有唯一列的表,需要這樣處理,這一句放在 create set 的前面 
#table add key (node id = 1, fully qualified name = ‘public.history‘); 
# 這樣設定結果集 
#set add table (set id=1, origin=1, id=4, fully qualified name = ‘public.history‘, #comment=‘history table‘, key = serial);

create set (id=1, origin=1, comment=‘testdb tables‘); 
set add table ( set id=1, origin=1,id=1, fully qualified name=‘public.tb_depart‘,comment=‘Table tb_depart‘ ); 
set add table ( set id=1, origin=1,id=2, fully qualified name=‘public.tb_user‘,comment=‘Table tb_user‘ ); 
set add table ( set id=1, origin=1,id=3, fully qualified name=‘public.tb_manager‘,comment=‘Table tb_manager‘ ); 
set add table ( set id=1, origin=1,id=4, fully qualified name=‘public.tb_test‘,comment=‘Table tb_test‘ ); 
echo ‘set 1 of testdb tables created‘; 
_END_

 

slony_3.sh檔案內容如下：

#/bin/sh 
/usr/local/pgsql/bin/slon testdb "dbname=test host=192.168.30.121 port=5432 user=repl" > ~/slon_gb_1.out 2>&1 & 
/usr/local/pgsql/bin/slon testdb "dbname=test host=localhost port=5432 user=repl" > ~/slon_gb_2.out 2>&1 & 

/usr/local/pgsql/bin/slonik << _END_
# Define cluster namespace and node connection information # 
cluster name = testdb; #提供串連參數 
node 1 admin conninfo = ‘dbname=test host=192.168.30.121 port=5432 user=repl‘; 
node 2 admin conninfo = ‘dbname=test host=localhost port=5432 user=repl‘;

# 提交訂閱複製集 
subscribe set (id=1, provider=1, receiver=2, forward=no); 
echo ‘set 1 of gb tables subscribed by node 2‘; 
_END_

[[email protected] cluster_shells]$ chmod u+x slony_*.sh

 

 

slony_main.sh檔案內容如下：

#!/bin/sh 
case $1 in 
start) 
cd /home/postgres/
sh slony_3.sh
;; 
stop) 
killall -KILL slon 
;; 
rebuild) 
cd /home/postgres 
killall -KILL slon 
sh slony_0.sh >> /dev/null 2>&1 
sh slony_1.sh 
sh slony_2.sh 
sh slony_3.sh 
;; 
*) 
echo "Please input start or stop or rebuild!!" 
;; 
esac

 

[[email protected] cluster_shells]$ chmod u+x slony_main.sh

 

 

測試同步：

在從庫端執行：

按照下面執行順序

./slony_0.sh

 ./slony_1.sh 
<stdin>:5: Cluster defined, nodes identified
<stdin>:10: Nodes defined

 

 ./slony_2.sh 
<stdin>:8: set 1 of testdb tables created

 

 ./slony_3.sh 
<stdin>:7: set 1 of gb tables subscribed by node 2

 

修改資料前：
主庫端：
[[email protected] ~]$ psql test

test=# \d
List of relations
Schema | Name | Type | Owner 
--------+-----------+-------+--------
public | tb_depart | table | repl
(1 row)

 

 

test=# \d tb_depart
Table "public.tb_depart"
Column | Type | Modifiers 
--------+--------------+-----------
id | integer | not null
name | character(8) | 
Indexes:
"tb_depart_pkey" PRIMARY KEY, btree (id)
Triggers:
_testdb_logtrigger_1 AFTER INSERT OR DELETE OR UPDATE ON tb_depart FOR EACH ROW EXECUTE PROCEDURE _testdb.logtrigger(‘_testdb‘, ‘1‘, ‘kv‘)

已建立觸發器，用於同步。

暫時無資料；
test=# select * from tb_depart;
id | name 
----+------
(0 rows)

 

  

備庫端：
[[email protected] ~]$ psql test
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# \d
List of relations
Schema | Name | Type | Owner 
--------+-----------+-------+--------
public | tb_depart | table | repl
(1 row)

 

test=# \d tb_depart
Table "public.tb_depart"
Column | Type | Modifiers 
--------+--------------+-----------
id | integer | not null
name | character(8) | 
Indexes:
"tb_depart_pkey" PRIMARY KEY, btree (id)
Triggers:
_testdb_denyaccess_1 BEFORE INSERT OR DELETE OR UPDATE ON tb_depart FOR EACH ROW EXECUTE PROCEDURE _testdb.denyaccess(‘_testdb‘)
slony建立了觸發器---雙向同步；


暫時無資料
test=# select * from tb_depart;
id | name 
----+------
(0 rows)

test=#

 

主庫端添加資料：

test=# insert into tb_depart values(1,‘aaa‘);
INSERT 0 1

 

test=# select * from tb_depart;
id | name 
----+----------
1 | aaa 
(1 row)

test=#

備庫端查看：
test=# select * from tb_depart;
id | name 
----+----------
1 | aaa 
(1 row)

同步成功。

本文出自 “使用者和預存程序” 部落格，請務必保留此出處http://9548010.blog.51cto.com/9538010/1652757

postgresql+slony-i安裝配置主從