pgpool 流复制主从安装与配置（高可用、读写分离）[转]
Centos5.4(x64)+postgresql9.12+pgpool 3.1.2
以下配置pgpool与主从均在一台服务器，主数据库端口为5433 ；从数据库端口为5432。
1.下载并安装postgresql9.1.2数据库
cd /tmp
wget http://ftp.postgresql.org/pub/source/v9.1.2/postgresql-9.1.2.tar.gz
tar -zxvf postgresql-9.1.2.tar.gz
./configure --prefix=/usr/local/pgsql9.1
make
make install
2.下载并安装pgpool
wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.1.2.tar.gz
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认证方式，后将slave的pg_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设置为 on；pool_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 ）。
