转一篇pgpool配置
http://dz.sdut.edu.cn/blog/subaochen/2013/08/postgresql-9-1的failover配置及其管理/
环境介绍
在两台虚拟机上分别安装debian 7.1.0、pgpool II 3.3.0、postgresql 9.1，其中debian为最小安装，postgresql 9.1为debian 7.1.0的默认版本，pgpool II 3.3.0为手工安装（debian 7.1.0默认安装版本为3.1，这里采用3.3版本是为了将来使用3.3版本的watchdog功能）。
两台虚拟机的IP地址分别为172.16.76.128（db1），172.16.76.129（db2），hostname分别为db1，db2，后面一律使用hostname引用这两台服务器。其中，初始状态下db1为primary server，db2为standby server，我们将测试primary server停掉的时候，db2能否自动切换为primary server，即failover。
准备工作
首先设置两台服务器的hosts文件：
172.16.76.128 db1 172.16.76.129 db2
然后设置两台服务器使得postgres用户可以无密码相互访问：
在db1执行：
su - postgres ssh-keygen ssh-copy-id postgres@db2
在db2执行：
su - postgres ssh-keygen ssh-copy-id postgres@db1
配置步骤
pgpool的配置
pgpool的配置主要涉及到两个文件：pcp.conf和pgpool.conf。pcp.conf文件很简单：
#postgres:postgres postgres:e8a48653851e28c69d0506508fb27fc5
即，当我们使用pcp和pgpool通讯的时候将使用postgres:postgres作为用户名和密码。
pgpool.conf文件的主要内容如下：
listen_addresses = '*' port = 9999 socket_dir = '/var/run/postgresql' pcp_port = 9898 pcp_socket_dir = '/var/run/postgresql' backend_hostname0 = 'db1' backend_port0 = 5432 backend_weight0 = 1 backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'db2' backend_port1 = 5432 backend_weight1 = 1 backend_flag1 = 'ALLOW_TO_FAILOVER' replication_mode = off load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 10 sr_check_user = 'postgres' sr_check_password = '111111' health_check_period = 30 health_check_timeout = 20 health_check_user = 'postgres' health_check_password = '111111' failover_command = '/usr/local/bin/failover_stream.sh %d %H /tmp/trigger_file'
上面的failover_command引用了一个failover_stream.sh脚本，目的是在standby server创建一个/tmp/trigger_file文件，以便standby server提升为primary server，内容如下：
#! /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
# Create the trigger file.
/usr/bin/ssh -T $new_master /usr/bin/touch $trigger_file
exit 0;
这样，pgpool就算是配置完毕了，在实验阶段可以这样启动pgpool，以便观察pgpool的log输出：
pgpool -nd
或者将log写入到文件中：
pgpool -nd > /tmp/pgpool.log 2>&1 &
db1的配置
数据库的配置主要涉及到pg_hba.conf,postgresql.conf,recovery.conf三个配置文件。
为了测试方便，这里忽略了数据库的权限认证，因此pg_hba.conf文件内容如下：
local all postgres trust local all all trust host all all 127.0.0.1/32 trust host all all 172.16.76.0/24 trust local replication postgres trust host replication postgres 127.0.0.1/32 trust host replication postgres ::1/128 trust host replication postgres 172.16.76.0/24 trust
postgresql.conf文件主要内容如下：
# primary server需要 wal_level = hot_standby # 这个不是必需的 archive_mode = on archive_command = 'cp %p /var/lib/postgresql/9.1/main/archive/%f' # primary server需要 max_wal_senders = 32 # standby server需要 hot_standby = on
还需要在$PGDATA目录（在debian下面默认是/var/lib/postgresql/9.1/main）下面创建archive目录：
mkdir /var/lib/postgresql/9.1/main/archive chown postgres.postgres /var/lib/postgresql/9.1/main/archive
创建recovery.done文件内容如下：
standby_mode=on primary_conninfo='host=db2' trigger_file='/tmp/trigger_file' recovery_target_timeline='latest'
这样就可以启动postgresql了：
service postgresql restart
db2的配置
db2的pg_hba.conf和postgresql.conf和db1完全相同，同样的，也需要创建archive目录，不再赘述。
db2的recovery.conf文件内容如下：
standby_mode=on primary_conninfo='host=db1' trigger_file='/tmp/trigger_file' recovery_target_timeline='latest'
现在首先需要停掉db2的postgresql，然后构建standby server，命令序列如下：
service postgresql stop rm -rf /var/lib/postgresql/9.1/main/* su - postgres /usr/lib/postgresql/9.1/bin/pg_basebackup -Fp -D /var/lib/postgresql/9.1/main -x -v -h db1 -w mkdir /var/lib/postgresql/9.1/main/archive exit ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem /var/lib/postgresql/9.1/main/server.crt ln -s /etc/ssl/private/ssl-cert-snakeoil.key /var/lib/postgresql/9.1/main/server.key cp ..../recovery.conf /var/lib/postgresql/9.1/main
现在可以启动db2了：
service postgresql start
观察db2的log，如果说：database system is ready to accept read only connections，那就说明standby server已经配置成功！
pgpool的使用
postgresql的primary server和standby server跑起来后，我们就可以使用pgpool来观察和操作了：
psql -p 9999 -h db1 -U postgres
列出当前集群服务器的状态：
postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role ---------+----------+------+--------+-----------+--------- 0 | db1 | 5432 | 2 | 0.500000 | primary 1 | db2 | 5432 | 2 | 0.500000 | standby
其中的status根据pgpool documentation的描述为：
0：从未使用，直接忽略
1：server已经启动，但是连接池中没有连接
2：server已经启动，并且在连接池中存在连接
3：server没有启动或者联系不上
创建测试数据库和测试数据：
postgres=# create database test; CREATE DATABASE postgres=# c test; 您现在已经连线到数据库 "test",用户 "postgres". test=# create table t(id int); CREATE TABLE test=# insert into t select * from generate_series(1,100);
然后分别登录到db1和db2上查看测试数据库的内容，验证pgpool确实已经正常工作了。
failover的管理
现在我们模拟primary server停掉的情况，在db1上面执行：
service postgresql stop
重新链接到pgpool可以看到：
subaochen@debian:~$ psql -p 9999 -h db1 -U postgres psql (9.1.9) 输入 "help" 来获取帮助信息. postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role ---------+----------+------+--------+-----------+--------- 0 | db1 | 5432 | 3 | 0.500000 | standby 1 | db2 | 5432 | 2 | 0.500000 | primary
可以看出，db2已经成功变身为primary server，而db1由于已经停掉，因此状态为3（pgpool无法链接到db1）。这时可以尝试通过pgpool往数据库里面添加更多的测试数据，此处略。
db1在重新启动前需要做如下的工作：
- 将recovery.done更名为recovery.conf，因为此时的db1只能作为standby server启动
- 复制db2的/var/lib/postgresql/9.1/main/pg_xlog里面最新的.history文件到db1的pg_xlog目录下，否则会报告timeline mismatch。这个问题在9.3版本已经解决，在9.1/9.2版本必须手工处理
然后启动db1：
service postgresql start
然后重新将db1加入到pgpool集群中：
pcp_attach_node -d 5 localhost 9898 postgres postgres 0
再次show pool_nodes会发现：
test=# show pool_nodes; node_id | hostname | port | status | lb_weight | role ---------+----------+------+--------+-----------+--------- 0 | db1 | 5432 | 1 | 0.500000 | standby 1 | db2 | 5432 | 2 | 0.500000 | primary
此时db1已经作为standby server加入到集群中了，只是还没有连接请求而已。
standby服务器的人工恢复
如果发现show pool_nodes显示如下的信息：
node_id | hostname | port | status | lb_weight | role ---------+----------+------+--------+-----------+--------- 0 | db1 | 5432 | 2 | 0.500000 | primary 1 | db2 | 5432 | 3 | 0.500000 | standby
并且很长时间状态都不能恢复为2的话，多半standby的自动恢复(failover)失败，最简单的办法就是手工重新构建standby服务器,参见数据库构建脚本dbsetup.sh
failover机制浅析
standby server能够成功promote为primary server的关键是trigger file，即在recovery.conf文件中定义的
trigger_file='/tmp/trigger_file'
也就是说，只要在/tmp目录创建一个名字为trigger_file的文件，postgresql即开始一个promote的过程。整个过程大致如下图所示：
注意事项
pgpool的运行身份：必须和postresql的运行身份一致，否则postgresql无法自动删除pgpool创建的trigger_file