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

cd postgresql-9.1.2

./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 ）。