pgpool安装配置
安装环境：
系统debian-6.0.4
pgpool：pgpool-II-3.3.1
pgpool机器
pgpool ip:172.16.2.150
pgpool ip:172.16.2.151
两台流复制机器
Mast:172.16.2.151
slave:172.16.2.152
中文手册：http://www.pgpool.net/docs/latest/pgpool-zh_cn.html
#########################################################################################
安装postgresql-9.3
vi /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main
vi /etc/apt/sources.list
deb-src http://ftp.debian.org/debian/ squeeze-updates main
deb http://ftp.tw.debian.org/debian wheezy main
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
过程中会报错
W: There is no public key available for the following key IDs:
8B48AD6246925553
解决： gpg --keyserver subkeys.pgp.net --recv 8B48AD6246925553
gpg --export --armor 8B48AD6246925553 | apt-key add -
apt-get update
aptitude install postgresql-9.3
#########################################################################################
安装pgpool-II-3.3.1
安装编译环境
aptitude install build-essential
tar -zxvf pgpool-II-3.3.1.tar.gz
cd pgpool-II-3.3.1
./configure
make
make install
#########################################################################################
编译过程中会报没有postgresql-server-dev-X.Y错误
aptitude install flex
aptitude install postgresql-server-dev-9.3
aptitude install postgresql-contrib-9.3
#########################################################################################
master配置
vi /etc/postgresql/9.3/main/postgresql.conf
listen_addresses = '*'
vi /etc/postgresql/9.3/main/pg_hba.conf 加入以下
host all all 127.0.0.1/32 trust
host all all 172.16.2.0/32 trust
host all all 172.16.2.152/32 trust
host all all 172.16.2.151/32 trust
host all all 172.16.2.150/32 trust
cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
cd /usr/local/etc
vi pgpool.conf
修改如下
listen_addresses = '*'
parallel_mode = off
load_balance_mode = on
socket_dir = '/var/run/postgresql'
pcp_socket_dir = '/var/run/postgresql'
pid_file_name = '/var/run/postgresql/pgpool.pid'
backend_hostname0 = '172.16.2.151'
backend_port0 = 5432
backend_weight0 = 9
backend_data_directory0 = '/var/lib/postgresql/9.3/main/'
backend_hostname1 = '172.16.2.152'
backend_port1 = 5432
backend_weight1 = 9
backend_data_directory1 = '/var/lib/postgresql/9.3/main/'
backend_flag1 = 'ALLOW_TO_FAILOVER'
#########################################################################################
cp pool_hba.conf.sample pool_hba.conf
vi pool_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 172.16.2.0/32 trust
host all all 172.16.2.152/32 trust
host all all 172.16.2.151/32 trust
host all all 172.16.2.150/32 trust
#########################################################################################
su postgres
createuser -p 5432 pgpool
createdb -p 5432 -O pgpool pgpool
cp pcp.conf.sample pcp.conf
/usr/local/bin/pg_md5 postgres
vi pcp.conf
加入如下：
postgres:e8a48653851e28c69d0506508fb27fc5
#########################################################################################
pgpool看门狗配置
172.16.2.150上的配置采用心跳模式（查询模式则注释掉）
vi pgpool.conf
需要修改的地方如下
use_watchdog = on
trusted_servers = '172.16.2.151,172.16.2.152'
wd_hostname = '172.16.2.150'
# - Virtual IP control Setting -
delegate_IP = '172.16.2.153'（虚拟ip，也就是两台pgpool对外的ip地址）
# -- heartbeat mode --
heartbeat_destination0 = '172.16.2.150'
heartbeat_destination1 = '172.16.2.151'
# -- query mode --
此模式下注释掉
# - Other pgpool Connection Settings -
other_pgpool_hostname1 = '172.16.2.151'（指定需要监控的 pgpool-II 服务器主机）
other_wd_port1 = 9000
172.16.2.151上配置同样采用心跳模式
需要修改的地方如下
vi pgpool.conf
use_watchdog = on
trusted_servers = '172.16.2.151,172.16.2.152'
wd_hostname = '172.16.2.151'
# - Virtual IP control Setting -
delegate_IP = '172.16.2.153'（虚拟ip，也就是两台pgpool对外的ip地址）
# -- heartbeat mode --
heartbeat_destination0 = '172.16.2.150'
heartbeat_destination1 = '172.16.2.151'
# -- query mode --
此模式下注释掉
# - Other pgpool Connection Settings -
other_pgpool_hostname0 = '172.16.2.150'（指定需要监控的 pgpool-II 服务器主机）
other_wd_port0 = 9000
#########################################################################################
启动pgpool
在此过程中先启动的则会成为主，另一台作为备机启动没有先后顺序
/usr/local/bin/pgpool -dn -f /usr/local/etc/pgpool.conf -a /usr/local/etc/pool_hba.conf -F /usr/local/etc/pcp.conf
