postgresql pgpool搭建及高可用简单配置

leng

pgpool安装配置
安装环境：
系统debian-6.0.4
数据库：postgresql-9.3
pgpool：pgpool-II-3.3.1

pgpool机器
pgpool ip:172.16.2.150
pgpool ip:172.16.2.151

两台流复制机器
Mast:172.16.2.151 root leyou
slave:172.16.2.152 root leyou
中文手册：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

xuxiaohui9216

内练一口气，外练一口屁。

hnhx001

亲爱的；我全部的爱都交给你保管！

一改飞

没有过不去的坎，只有不走路的人，所以路都是自己走出来的

鸬鹚洲

我的世界总是一个人，寂寞让我变的坚强╮。