0. chown postgres:postgres /usr/pgpool
1.进入pgpool的etc目录
cd /usr/pgpool/etc/
2.拷贝配置
cp pgpool.conf.sample-stream pgpool.conf
3.编辑参数 vi pgpool.conf
listen_addresses = '*'
port = 9999
backend_hostname0 = '192.168.1.108'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.2/data'
backend_hostname1 = '192.168.1.109'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.2/data'
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
debug_level = 1
pid_file_name = '/usr/pgpool/pgpool.pid'
logdir = '/usr/pgpool/log'
#后头配置 必须跟recover.conf必须一致
failover_command = '/usr/pgpool/etc/failedcommand.sh'
sr_check_user = 'repuser'
sr_check_password = 'repuser'
delay_threshold = 10000
# - Authentication -
enable_pool_hba = on
system_db_hostname ="192.168.1.108"
4 .在本服务器的postgresql中,为pgpool创建系统数据库以及schema
su postgres
cd /usr/pgsql-9.2/bin
./createuser -p 5432 pgpool
//在本地的postgresql数据库创建pgpool用户
./createdb -p 5432 -O pgpool pgpool
//在本地的postgresql数据库创建pgpool数据库,所有者为pgpool
5.建立日志目录
mkdir /usr/pgpool/log
6.failedcommand.sh 编辑Master库失败强制转到slave文件
cd /usr/pgpool/etc/
vi 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;
7.赋予文件可执行权限
chmod a+x /usr/pgpool/etc/failedcommand.sh
8.暂时没用设置Master服务器与Slave密钥登录认证,请查看http://blog.163.com/czg_e/blog/static/46104561201101922246729/
9.配置pgpool连接认证
cp /usr/pgpool/etc/pool_hba.conf.sample /usr/pgpool/etc/pool_hba.conf
vi /usr/pgpool/etc/pool_hba.conf
host all all 0.0.0.0/0 md5
9.设置pgpool pcp用户管理权限
通过pg md5对密码加密
/usr/pgpool/bin/pg_md5 repuser
提示你输入密码 我输入的是
424b116a28bd35e1e21b81872fb003d5
cp /usr/pgpool/etc/pcp.conf.sample /usr/pgpool/etc/pcp.conf
repuser:424b116a28bd35e1e21b81872fb003d5
echo "repuser:424b116a28bd35e1e21b81872fb003d5" /usr/pgpool/etc/pool_passwd
vi /usr/pgpool/etc/pcp.conf
postgres:e8a48653851e28c69d0506508fb27fc5
==================
echo "postgres:e8a48653851e28c69d0506508fb27fc5" /usr/pgpool/etc/pool_passwd
echo "postgres:e8a48653851e28c69d0506508fb27fc5" /usr/pgpool/etc/pool_passwd
配置PCP命令
pgpool-II有一个出于管理目的可以通过网络查询数据库节点信息、关闭pgpool-II等功能的接口(interface)。使用PCP命令首先必须通过用户验证,它不同于PostgreSQL的用户验证,需要在pcp.conf中定义用户名和密码。文件中的用户名/密码成对出现在每一行,用冒号隔开,密码是md5格式:
postgres:e8a48653851e28c69d0506508fb27fc5
==================
10.启动pgpool
cd /usr/pgpool/bin
./pgpool -f /usr/pgpool/etc/pgpool.conf -a /usr/pgpool/etc/pool_hba.conf -F /usr/pgpool/etc/pcp.conf
停止命令 pgpool stop
测试pgpool 是否正常运行
回到安装postgresql目录
/usr/pgsql-9.2/bin
./psql -h 192.168.1.108 -p 9999 -U repuser -d pgpool
psql: ERROR: MD5 authentication is unsupported in replication, master-slave and parallel modes.
HINT: check pg_hba.conf
解决上面错误需将pgpool.conf中 enable_pool_hba设置为 on;
CREATE TABLE testa
(
id bigint NOT NULL,
name character varying
);
插入了2个数据库表
======异常处理==========================
异常处理
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/pgpool/bin/pg_md5 -m -p -u postgres pool_passwd )。