centos6.5安装pgpool

设置主备机免密码传输

主机： 172.17.254.90
cd ~/.ssh
ssh-keygen -t rsa
cat id_rsa.pub >>authorized_keys                #本机ssh信任
ssh-copy-id -i root@172.17.254.91               #他机ssh信任

备机： 172.17.254.91
cd ~/.ssh
ssh-keygen -t rsa
cat id_rsa.pub >>authorized_keys                #本机ssh信任
ssh-copy-id -i root@172.17.254.90               #他机ssh信任

安装pgpool

yum install http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-6-x86_64/pgpool-II-pg93-3.4.1-3pgdg.rhel6.x86_64.rpm
yum install http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-6-x86_64/pgpool-II-pg93-extensions-3.4.1-3pgdg.rhel6.x86_64.rpm
cp /usr/pgsql-9.3/lib/* /opt/postgres/9.3/lib/postgresql
cp /usr/pgsql-9.3/share/extension/* /opt/postgres/9.3/share/postgresql/extension
/opt/postgres/9.3/bin/psql -U postgres -f /opt/postgres/9.3/share/postgresql/extension/pgpool-regclass.sql template1
/opt/postgres/9.3/bin/psql -U postgres -f /opt/postgres/9.3/share/postgresql/extension/pgpool-recovery.sql template1
/opt/postgres/9.3/bin/psql -U postgres -f /usr/share/pgpool-II/insert_lock.sql template1
#在既存数据库上执行：
CREATE EXTENSION pgpool_regclass;CREATE EXTENSION pgpool_recovery;
#开启pgpool服务
chkconfig pgpool on

设置主机配置文件

vi /opt/postgres/9.3/data/postgres.conf
wal_level = hot_standby                 #日志模式为hot_standby
max_wal_senders = 5                     #wal_senders数目
wal_keep_segments = 256                 #主备同期用的wal数目，每个16M（设置的大一点，以防基础备份时间太长，主库数据库变化太大，超过16M*256,则备机恢复失败）
synchronous_standby_names = 'standby1'  #设置同步备机名字
synchronous_commit=on                   #on：同步备库到磁盘

#创建异步的配置文件，备机挂了后启用，否则主机写操作一直等待
cp postgresql.conf postgresql.conf.async
chown  postgres:postgres postgresql.conf.async
vi postgresql.conf.async:
    synchronous_standby_names = ''

#配置replication用户
vi pg_hba.conf
    host    replication     postgres        0.0.0.0/0            md5

设置备机配置文件

vi postgresql.conf
    hot_standby = on
cp /opt/postgres/9.3/share/postgresql/recovery.conf.sample /opt/postgres/9.3/data/recovery.conf
chown postgres:postgres /opt/postgres/9.3/data/recovery.conf

vi recovery.conf
    standby_mode = on
    primary_conninfo = 'host=172.17.254.90 port=5432 user=postgres password=sa  application_name=standby1'  #连接主机的字符串
    trigger_file = '/tmp/trigger_file0'                                                                     #备机recover的触发文件
    recovery_target_timeline = latest

其他设置

cd /etc/pgpool-II
cp  pcp.conf  pcp.conf.bk
cp  pgpool.conf  pgpool.conf.bk
cp  pool_hba.conf  pool_hba.conf.bk
vi /etc/pgpool-II/pcp.conf:
    postgres:c12e01f2a13ff5587e1e9e4aedb8242d       #密码： pg_md5 sa
vi pool_hba.conf:
    host    all         all         0.0.0.0/0          md5


vi pool_passwd:
    postgres:md5a5e20f3305a136f537aabcbfa853f467    #密码：从以下语句获得
    pacer:md5e179eed94de3a74d9da4ffd2a23e74d6       #select rolname,rolpassword from pg_authid


cat pgpool.conf.sample-stream >pgpool.conf          #以pgpool.conf.sample-stream为蓝本修改

主机设置

enable_pool_hba = on
listen_addresses = '*'

backend_hostname0 = '172.17.254.90'     #pg主机
backend_port0 = 5432
backend_weight0 = 1                     #pg主机负载均衡权重

backend_hostname1 = '172.17.254.91'     #pg备机
backend_port1 = 5432
backend_weight1 = 999999                #pg备机负载均衡权重

sr_check_user = 'postgres'
sr_check_password = 'sa'

num_init_children = 300                 #pgpool子进程数量
log_destination = 'syslog'
health_check_period = 5                 #健康检查的间隔
health_check_max_retries = 3            #健康检查重试次数
health_check_user = 'postgres'
health_check_password = 'sa'

failover_command = '/opt/postgres/9.3/bin/failover_stream.sh %d %H /tmp/trigger_file0'  #failover脚本

delay_threshold = 10000000              #可接受的延迟数据量，超过此数据量，查询发送到主机
recovery_user = 'postgres'
recovery_password = 'sa'

#watchdog
use_watchdog = on
wd_hostname = '172.17.254.90'           #本机ip
delegate_IP = '172.17.254.89'           #虚拟ip，主备一致

if_up_cmd = 'ifconfig eth4:0 inet $_IP_$ netmask 255.255.255.0   #eth4为ip对应的网卡
if_down_cmd = 'ifconfig eth4:0 down'

heartbeat_destination0 = '172.17.254.91'#pgpool备机ip
heartbeat_destination_port0 = 9694
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'sa'
other_pgpool_hostname0 = '172.17.254.91'#pgpool备机ip
other_pgpool_port0 = 5432
other_wd_port0 = 9000
heartbeat_device0 = 'eth4'              #ip对应的网卡

备机设置

enable_pool_hba = on
listen_addresses = '*'

backend_hostname0 = '172.17.254.90'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = '172.17.254.91'
backend_port1 = 5432
backend_weight1 = 999999

sr_check_user = 'postgres'
sr_check_password = 'sa'

num_init_children = 300
log_destination = 'syslog'
health_check_period = 5
health_check_max_retries = 3
health_check_user = 'postgres'
health_check_password = 'sa'

failover_command = '/opt/postgres/9.3/bin/failover_stream.sh %d %H /tmp/trigger_file0'

delay_threshold = 10000000
recovery_user = 'postgres'
recovery_password = 'sa'

#watchdog
use_watchdog = on
wd_hostname = '172.17.254.91'
delegate_IP = '172.17.254.89'

if_up_cmd = 'ifconfig eth6:0 inet $_IP_$ netmask 255.255.255.0
if_down_cmd = 'ifconfig eth6:0 down'

heartbeat_destination0 = '172.17.254.90'
heartbeat_destination_port0 = 9694
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'sa'
other_pgpool_hostname0 = '172.17.254.90'
other_pgpool_port0 = 5432
other_wd_port0 = 9000
heartbeat_device0 = 'eth6'

备机基础备份

#注意修改主机data目录下所有文件所属为postgres
/opt/postgres/9.3/bin/pg_basebackup -D /opt/postgres/9.3/data -Fp -Xs -v -P -h 172.17.254.90 -p 5432 -U postgres
#修改data目录下所有文件所属为postgres
chown -R postgres:postgres /opt/postgres/9.3/data

重启postgres

#主机：
service postgres-9.3-openscg restart
#备机：
service postgres-9.3-openscg restart

启动pgpool

#主机：
service pgpool start
#备机：
service pgpool start

检证

/opt/postgres/9.3/bin/psql -h 172.17.254.91 -p 5432 -U postgres -c "select * from test;";
/opt/postgres/9.3/bin/psql -h 172.17.254.89 -p 5432 -U postgres -c "select * from test;";
/opt/postgres/9.3/bin/psql -h 172.17.254.91 -p 5432 -U postgres -c "select now()-pg_last_xact_replay_timestamp();"

来源张永光的博客