# 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 fi
# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file exit 0;
exit 0;
在主库的$PGDATA目录下面新建basebackup.sh
#! /bin/sh
# Recovery script for streaming replication. # This script assumes followings: #
# 1) Executed on the primary node by pgpool_recovery function. # 2) Executed as \# 3) \
# 4) can connect to \# password.
# 5) Password less access using ssh from the primary node to the # target node is possible. # 6) Arguments for the scripts are: # # # #
datadir=$1 desthost=$2 destdir=$3 port=$4
/usr/local/pgsql/bin/psql -p $port -c \postgres
rsync -C -a -c --delete -e ssh --exclude postgresql.conf --exclude postmaster.pid \\ --exclude postmaster.opts --exclude pg_log --exclude pg_xlog \\ --exclude recovery.conf $datadir/ $desthost:$destdir/
ssh -T $desthost mv $destdir/recovery.done $destdir/recovery.conf
/usr/local/pgsql/bin/psql -c \
$1: database cluster path on the primary node $2: hostname or IP address to be recovered $3: database cluster path on target node
$4: port number of of the primary database cluster
# 7) psql, rsync, ssh are available in the command search path.
在$PGDATA目录下面新建pgpool_remote_start文件（赋予可执行权限）
#! /bin/sh
if [ $# -ne 2 ] then
echo \ exit 1 fi DEST=$1 DESTDIR=$2
PGCTL=/usr/local/pgsql/bin/pg_ctl
ssh -T $DEST $PGCTL -w -D $DESTDIR -l /home/postgres/postgresql.log start
配置pool_hba.conf cd /usr/local/pgpool/etc/
cat /usr/local/pgsql/data/pg_hba.conf > pool_hba.conf
启动pgpool命令：
pgpool -n >/var/log/pgpool/pgpool.log 2>&1 & 停止pgpool命令： pgpool -m fast stop
登录pgpool命令：
psql -p 9999 -U postgres -d postgres
查看pgpool所有的节点信息： show pool_nodes;
status:
1:节点启动，还没有被连接 2：节点启动，已经被连接 3：节点down掉了
模拟节点down掉的情况：主库和备库一正常运行，备库二直接停止服务，当再重新启动备库二时，在主库上查看pool_nodes节点发现：
当用show pool_nodes;命令去查看的时候，发现有个节点的status为3的时候，说明这个节点已经down了，重新恢复的命令如下：（需要恢复的节点，数据库服务必须是停止状态） 节点恢复的命令：pcp_recovery_node -d 3600 172.16.144.145 9898 postgres 123456 2 172.16.144.145 pgpool(安装的主机IP) 9898：pgpool的端口号 postgres:用户名 123456：密码 2：节点ID
命令执行后成功发现：
此时status状态值由3变为2，代表这个节点正常了。
把一个新节点加入到pgpool中的命令：
pcp_attach_node -d 5 172.16.144.200 5432 postgres 123456 1 172.16.144.200要加入节点的数据库IP地址 5432：要加入节点的数据库端口号 postgres:要加入节点的数据库的用户名 123456：要加入节点的数据库密码