2013年2月4日 星期一

pgpool 流複製主從安裝與配置(高可用、讀寫分離)


Centos5.4(x64)+postgresql9.12+pgpool 3.1.2
以下配置pgpool與主從均在一台伺服器,主要資料庫埠為5433 ;從資料庫埠為5432
1.下載並安裝postgresql9.1.2資料庫
cd /tmp
./configure --prefix=/usr/local/pgsql9.1
make
make install
2.下載並安裝pgpool
tar -zxvf pgpool-II-3.1.2.tar.gz
cd pgpool-II-3.1.2
./configure --prefix=/usr/local/pgpool --with-pgsql=/usr/local/pgsql9.1/
make
make install
3.資料庫配置主從流即時複製
創建postgres用戶
useradd postgres 
cd /data
建立主要資料庫存放目錄
mkdir masterdata
建立slave資料庫存放目錄
mkdir slavedata
對主要資料庫目錄賦予postgres許可權
chown -R postgres:postgres masterdata
slave資料庫目錄賦予postgres許可權
chown -R postgres:postgres slavedata
su - postgres
初始化主要資料庫
/usr/local/pgsql9.1/bin/initdb -D /data/masterdata
修改主要資料庫參數
vi /data/masterdata/postgresql.conf
listen_addresses = '*'
port = 5433 
logging_collector = on
wal_level = hot_standby
synchronous_commit = on
max_wal_senders = 2
wal_keep_segments = 32
synchronous_standby_names = '*'

vi /data/masterdata/pg_hba.conf
host    all             all             192.168.100.0/24        md5
host     replication     postgres        192.168.100.24/32       trust
啟動主要資料庫
以下操作在postgres用戶下執行
su - postgres
/usr/local/pgsql9.1/bin/pg_ctl -D /data/masterdata/ start
創建測試庫並將主庫設置為備份狀態
/usr/local/pgsql9.1/bin/psql -p 5433 -U postgres
create database test;
select pg_start_backup('/data/masterdata/');
\q
將主庫整體拷貝為slave
cp -rp /data/masterdata/* /data/slavedata/
配置slave資料庫
vi /data/slavedata/postgresql.conf
port = 5432
wal_level = minimal
hot_standby = on 
停止主要資料庫backup狀態
/usr/local/pgsql9.1/bin/psql -p 5433 -U postgres
select pg_stop_backup();
\q
設置slave伺服器流複製
vi  /data/slavedata/ recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.100.24 port=5432 user=postgres'
trigger_file = '/data/slavedata/trigger_activestb'
啟動重資料庫
/usr/local/pgsql9.1/bin/pg_ctl -D /data/slavedata/ start
4.配置pgpool
配置pgpool.conf
cp /usr/local/pgpool/etc/pgpool.conf.sample-stream  /usr/local/pgpool/etc/pgpool.conf
listen_addresses = '*'
port = 9998
backend_hostname0 = '127.0.0.1'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/data/masterdata
backend_hostname1 = '127.0.0.1'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/slavedata'
log_statement = on
log_per_node_statement = on
pid_file_name = '/usr/local/pgpool/pgpool.pid'
logdir = '/usr/local/pgpool/log'
log_connections = on
log_hostname = on
debug_level = 1
failover_command = '/usr/local/pgpool/etc/failedcommand.sh %d %H /data/slavedata/trigger_activestab'
sr_check_user = 'postgres'
delay_threshold = 10000


mkdir /usr/local/pgpool/log/
編輯Master庫失敗強制轉到slave
vi /usr/local/pgpool/etc/failedcommand.sh
#! /bin/sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.
failed_node=$1
new_master=$2
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
        exit 0;
fi
 /usr/bin/ssh -T $new_master /bin/touch $trigger_file

exit 0;
賦予檔可執行許可權
chmod a+x  /usr/local/pgpool/etc/failedcommand.sh 
設置Master伺服器與Slave金鑰登錄認證,請查看http://blog.163.com/czg_e/blog/static/46104561201101922246729/
配置pgpool連接認證
mv /usr/local/pgpool/etc/pool_hba.conf.sample  /usr/local/pgpool/etc/pool_hba.conf
vi  /usr/local/pgpool/etc/pool_hba.conf
host    all         all         192.168.100.0/24      md5
設置pgpool pcp用戶管理許可權
mv /usr/local/pgpool/etc/pcp.conf.sample  /usr/local/pgpool/etc/pcp.conf
通過pg md5對密碼加密
/usr/local/pgpool/bin/pg_md5 -p
vi  /usr/local/pgpool/etc/pcp.conf
postgres:剛才加密後的密碼
 5.啟動pgpool
pgpool -f /usr/local/pgpool/etc/pgpool.conf -a /usr/local/pgpool/etc/pool_hba.conf  -F /usr/local/pgpool/etc/pcp.conf
測試pgpool 是否正常運行
psql -p 9998 -U postgres


異常處理
2012-03-24 12:52:47 ERROR: pid 2424: pool_read_message_length: message length (8) in slot 1 does not match with slot 0(12)
2012-03-24 12:52:47 ERROR: pid 2424: Failed to read the authentication packet length. This is likely caused by the inconsistency of auth method among DB nodes. In this case you can check the previous error messages (hint: length field) from pool_read_message_length and recheck the pg_hba.conf settings.
通過pgsql不能登錄且日誌中輸入如上資訊,經排查發現為master庫中pg_hba.conf設置採用md5 方式認證,而slave pg_hba.con配置的為trust認證方式,後將slavepg_hba.conf檔配置為了md5方式即可通過驗證登錄。

psql: ERROR:  MD5 authentication is unsupported in replication, master-slave and parallel modes.
HINT:  check pg_hba.conf
通過pgsql不能登錄且提示如上資訊,解決上面錯誤需將pgpool.conf enable_pool_hba設置為 onpool_hba.conf中加入md5驗證(host    all         all         10.10.10.0/24         md5);pool_passwd中設置用戶名密碼驗證(/usr/local/pgpool/bin/pg_md5 -m -p -u postgres pool_passwd )。

沒有留言: