这里利用pgpool-ii实现PG的高可用。基于流复制的方式，两节点自动切换：
1、单pgpool
a.环境：
pgpool：192.168.238.129
data1：192.168.238.130
data2：192.168.238.131
b.图例
c.配置互信
ssh-copy-id ha@node1
ssh-copy-id ha@node2
d.数据库节点配置，请参照《 使用pg_basebackup搭建PostgreSQL流复制环境 》。
e.pgpool配置：
listen_addresses = '*'
backend_hostname0 = 'node1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/home/ha/pgdb/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'node2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/home/ha/pgdb/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
pid_file_name = '/home/ha/pgpool/pgpool.pid'
logdir = '/home/ha/pgpool/log'
health_check_period = 1
health_check_user = 'ha'
health_check_password = 'ha'
failover_command = '/home/ha/pgdb/fail.sh %H'
recovery_user = 'ha'
recovery_password = 'ha'
f.fail.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.
new_master=$1
trigger_command="/home/ha/pgdb/bin/pg_ctl -D /home/ha/pgdb/data promote -m fast"
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi
# Create the trigger file.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;
g.建立pool_passwd
pg_md5 -m -p -u postgres pool_passwd
PS：在9.1之前一直用的是trigger_file，这里建议用promote -m fast的方式，因为
“ pg_ctl promote -m fast will skip the checkpoint at end of recovery so that we can achieve very fast failover when the apply delay is low. Write new WAL record XLOG_END_OF_RECOVERY to allow us to switch timeline correctly for downstream log readers. If we skip synchronous end of recovery checkpoint we request a normal spread checkpoint so that the window of re-recovery is low. Simon Riggs and Kyotaro Horiguchi, with input from Fujii Masao. Review by Heikki Linnakangas ”
h.测试
pgpool节点
[ha@node0 pgdb]$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
[1] 22928
[ha@node0 pgdb]$ psql -h 192.168.238.129 -p 9999 -d postgres -U ha
Password for user ha:
psql (9.4.5)
Type "help" for help.
postgres=# insert into test values (8);
INSERT 0 1
postgres=# select * from test ;
id
----
1
2
3
4
6
8
(6 rows)
node1节点：
[ha@localhost pgdb]$ ps -ef | grep post
root 2124 1 0 Dec26 ? 00:00:00 /usr/libexec/postfix/master
postfix 2147 2124 0 Dec26 ? 00:00:00 qmgr -l -t fifo -u
postfix 13295 2124 0 06:01 ? 00:00:00 pickup -l -t fifo -u
ha 13395 1 0 06:06 pts/3 00:00:00 /home/ha/pgdb/bin/postgres
ha 13397 13395 0 06:06 ? 00:00:00 postgres: checkpointer process
ha 13398 13395 0 06:06 ? 00:00:00 postgres: writer process
ha 13399 13395 0 06:06 ? 00:00:00 postgres: wal writer process
ha 13400 13395 0 06:06 ? 00:00:00 postgres: autovacuum launcher process
ha 13401 13395 0 06:06 ? 00:00:00 postgres: stats collector process
ha 13404 13395 0 06:07 ? 00:00:00 postgres: wal sender process rep 192.168.238.131(59415) streaming 0/21000060
ha 13418 4087 0 06:07 pts/3 00:00:00 grep post
[ha@localhost pgdb]$ kill -9 13395
pgpool节点：