Pgpooll+Postgresql自动切换备库

Setup 1、配置SSH密钥验证 （在三节点中执行）

[root@localhost htdocs]#  mkdir ~/.ssh
mkdir: cannot create directory `/root/.ssh': File exists
[root@localhost htdocs]# chmod 700 ~/.ssh/
[root@localhost htdocs]# cd ~/.ssh/
[root@localhost .ssh]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
e6:01:c6:f8:57:07:44:37:7a:9c:72:7f:e8:37:06:6e [e-mail]
[root@localhost .ssh]# chmod 700 ~/.ssh/
[root@localhost .ssh]# 

[root@node2 .ssh]# ssh 108.88.3.236 cat /root/.ssh/id_rsa.pub >>authorized_keys
[root@node2 .ssh]# ssh 108.88.3.246 cat /root/.ssh/id_rsa.pub >>authorized_keys
[root@node2 .ssh]# ssh 108.88.3.237 cat /root/.ssh/id_rsa.pub >>authorized_keys
[root@node2 .ssh]# chmod 600 ~/.ssh/authorized_keys
[root@node2 .ssh]# scp authorized_keys  108.88.3.237:/root/.ssh/
[root@node2 .ssh]# scp authorized_keys  108.88.3.246:/root/.ssh/

[root@node2 .ssh]# ssh 108.88.3.246 date

setup 2、在pgpooll节点修改配置文件

修改 /usr/local/etc/pgpool.conf

#vi  /usr/local/etc/pgpool.conf

--------------------------------------------------------------------------------------------------------------------------------

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------


health_check_period = 1
                                   # Health check period
                                   # Disabled (0) by default
health_check_timeout = 20
                                   # Health check timeout
                                   # 0 means no timeout
health_check_user = 'postgres'
                                   # Health check user
health_check_password = ''
                                   # Password for health check user
health_check_max_retries = 0
                                   # Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
                                   # Amount of time to wait (in seconds) between retries.




#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------


failover_command = ' /usr/local/postgresql/bin/failover_stream.sh %d %H /backup/trigger'

－－－－－－－－－－－－－－－－ /usr/local/etc/pgpool.conf－－－－－－－－－－－－－－－－－－－－－－－

Setup 3、 编写failover_stream.sh 脚本

vi /usr/local/postgresql/bin/failover_stream.sh

[root@localhost data]# more /usr/local/postgresql/bin/failover_stream.sh
#! /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 /bin/touch $trigger_file


exit 0;
[root@localhost data]# chmod 755  /usr/local/postgresql/bin/failover_stream.sh

setup 4、 停止主节点

#/u03/postgresql/bin/pg_ctl -D /u03/postgresql/data -l logfile －m fast stop

setup 5、 查看PGpooll状态

2013-01-24 20:36:20 LOG:   pid 4994: connect_inet_domain_socket_by_port: health check timer expired
2013-01-24 20:36:20 ERROR: pid 4994: make_persistent_db_connection: connection to 108.88.3.237(5432) failed
2013-01-24 20:36:20 LOG:   pid 4994: health_check: health check timer has been already expired before attempting to connect to 0 th backend
2013-01-24 20:36:20 LOG:   pid 4994: set 0 th backend down status
2013-01-24 20:36:20 LOG:   pid 4994: starting degeneration. shutdown host 108.88.3.237(5432)
2013-01-24 20:36:20 LOG:   pid 4994: Restart all children
2013-01-24 20:36:20 LOG:   pid 4994: execute command:  /usr/local/postgresql/bin/failover_stream.sh 0 108.88.3.236 /backup/trigger
2013-01-24 20:36:21 LOG:   pid 4994: find_primary_node_repeatedly: waiting for finding a primary node
2013-01-24 20:36:24 LOG:   pid 4994: find_primary_node: primary node id is 1
2013-01-24 20:36:24 LOG:   pid 4994: failover: set new primary node: 1
2013-01-24 20:36:24 LOG:   pid 4994: failover: set new master node: 1
2013-01-24 20:36:24 LOG:   pid 4994: failover done. shutdown host 108.88.3.237(5432)
2013-01-24 20:36:25 LOG:   pid 5005: pcp child process received restart request
2013-01-24 20:36:25 LOG:   pid 4994: PCP child 5005 exits with status 256
2013-01-24 20:36:25 LOG:   pid 4994: fork a new PCP child pid 5049

Setup 6、测试输入

[root@localhost data]# su - postgres
[postgres@localhost ~]$ psql -p9999
psql (9.1.3)
Type "help" for help.


postgres=# show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role   
---------+--------------+------+--------+-----------+---------
 0       | 108.88.3.237 | 5432 | 3      | 0.500000  | standby
 1       | 108.88.3.236 | 5432 | 2      | 0.500000  | primary
(2 rows)
postgres=# insert into test values (3,'ghan');
INSERT 0 1
postgres=# insert into test values (3,'ghan');
INSERT 0 1
postgres=# insert into test values (3,'ghan');
INSERT 0 1
postgres=# 

异常情况：

postgres=# show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role   
---------+--------------+------+--------+-----------+---------
 0       | 108.88.3.237 | 5432 | 3      | 0.500000  | standby
 1       | 108.88.3.236 | 5432 | 2      | 0.500000  | standby
(2 rows)
手动加入pgpooll中
[postgres@localhost ~]$ pcp_attach_node -d 5 127.0.0.1 9898 postgres postgres 0
postgres=# show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role   
---------+--------------+------+--------+-----------+---------
 0       | 108.88.3.237 | 5432 | 1      | 0.500000  | primary
 1       | 108.88.3.236 | 5432 | 2      | 0.500000  | standby

重启pgpoooll 就恢复正常了，