postgresql9.5.2, pgpool3.5.2, heartbeat3.0.5 ha hot standby configured under DEBIAN8

Project requirements through the heartbeat control PG and Pgpool master preparation process, so as to achieve high availability, PG's own flow replication Master plan reference My other main preparation project article, Pgpool here only uses the connection pool function. The app accesses the Pgpool pool via heartbeat virtual VIP.

Note: DEBIAN8 recommended or directly apt-get install heartbeat, compile a variety of dependencies will also prompt the source error, the configuration file path is the same, but the startup and shutdown scripts to write their own, but occasionally will error OPENHPI related, the foreigner said is the bug, However, the solution is as follows:

Vim/etc/systemd/system/openhpid.service[unit]description=daemon providing access to the SAF Hardware Platform Interface[service]type=simpleexecstart=/usr/sbin/openhpid-n-c/etc/openhpi/openhpi.conf[install]wantedby= Multi-user.target

First, the preparatory work

1, two debian8.4 XFS file format virtual machine, respectively configure two IP, leave a VIP behind with

2, according to another PG Stream Replication master plan, the configuration of PG's main and standby configuration

3, according to another Pgpool compiled installation for the connection pool configuration installation Pgpool

4, refer to my other article about Linux under the mailbox configuration, configure the mailbox

Second, the configuration heartbeat

1. Configuration files

Cd/etc/ha.dvim Authkeysauth crc#2 SHA1 hi! #3 MD5 hello!

#---------------------Heartbeat master configuration file----------------------vim ha.cfdebugfile/var/log/ha-debuglogfile/var/log/ Ha-loglogfacility local0# heartbeat interval keepalive * death threshold deadtime 30# warning Time warntime 10# first start heartbeat, wait for how long to start the main service resource Initdead 30# Connection Port Udpport 694# Heart Jumper Interface #bcast eth1# main node of the network card device, the heart of the backup Machine jumper interface ipucast eth0 192.168.180.223# automatic switching or switch off good auto_failback offnode Elink-masternode elink-slave#ping 192.168.180.1respawn Hacluster/usr/lib/heartbeat/ipfailapiauth ipfail gid= Haclient Uid=hacluster

#----------------------VIP Resource control file----------------------vim  Haresourceselink-master ipaddr::192.168.180.221/32/eth0 elinkresource.sh 

#---Resource control script, if the host has already received the resources, the standby machine even if the boot heartbeat will not start the following script----cd resource.dvim elinkresource.shispid () {         pid_result= ' Ps -ef | grep $1 | awk   ' {if ($8!~/grep/)  print $2} '         echo $1 ' Process number:   ' $pid _result        if [ -n  $pid _result  ]; Then                return  1        else                 return 0         Fi}start ()  {        echo  "***************************start  pg ha****************************** "        isPid  Postmaster        if [ $? -eq 0 ];then                 /etc/init.d/postgresql start         else                 echo  ' PostgreSQL already has a process, no need to start again '          fi        isPid pgpool.conf         if [ $? -eq 0 ];then                 pgpool -f /usr/local/etc/pgpool.conf -f  /usr/local/etc/pcp.conf -D        else                 echo  ' Pgpool already has a process, no need to start again '         fi         #pg_master_process_result = ' Ssh -l root  192.168.180.222  "Ps -ef | grep postmaster | grep -v grep"         key_result= ' su - postgres -c  ' pg_ controldata | grep cluster | grep  ' Archive recovery '          if [ -n  "$key _result"  ];then                 su - postgres -c  "Pg_ctl  promote "        else                 echo  ' This server is not a standby machine cannot be promote for host!!! '         fi        isPid  Sleep        if [ $? -eq 0 ];then                 /opt/monitorpg.sh &         else                echo   ' sleep monitoring process already exists without restarting '         fi}stop ()  {         echo  "****************************stop pg ha********************* "         #sleep_result = ' ps -ef | grep  ' Sleep 30 '  | awk  ' {if ($8!~/grep/)  print $3} '           #kill  -9  $sleep _result        pgpool -f  /usr/local/etc/pgpool.conf -f /usr/local/etc/pcp.conf -d -m fast stop         /etc/init.d/postgresql stop        mv /usr /local/pgsql/data/recovery.done /usr/local/pgsql/data/recovery.conf}case $1 in         start)                  start        ;;         stop)                  stop        ;;         restart)                  stop                 sleep 10                 start        ;;         *)                  echo  "Usage: $0{start|stop|restart}" Esac

2. Business Monitoring script

Heartbeat can only monitor the network layer, such as shutdown, stop Heartbeat can switch past, but if SQL is not queried, then this resource is invalid resources, of course, need to switch

Monitor () {        result= '/usr/local/pgsql/bin/psql -h  192.168.180.222 -p5432 -u postgres -w -d postgres --command  "Select  1 " | sed -n 3p | cut -c9-9 '           #echo   $result         if [ 1 -eq  "$ Result " ];then                 echo  ' Date '   database normal, detection value: ' $result  >> /opt/moni.log         else                 echo  ' Date '   database hangs, detection value: ' $result  >> /opt/moni.log                 service heartbeat stop                 break         fi}while sleep 10do        monitordone

3. Testing

Currently there are three main types of tests,

A, the host heartbeat down machine, whether the standby machine can properly take over the resources

Host:/etc/init.d/heartbeat stop, theoretically business monitoring scripts, PG, Pgpool, Heartbeat will stop, Recovery.done become recovery.conf

Standby Machine: Pg_controldata | grep cluster display for PRODUCTION,PG_CTL promote active standby, Pgpool, business monitoring script start, recovery.conf automatically changed to Recovery.done

Host recovery to standby: Start PostgreSQL and Heartbeat

b, the host business hangs, whether the standby machine can take over

The main is to determine whether the local database can be connected to a select query, if not, will stop the local heartbeat let the standby machine take over the resources

C, host hard shutdown, standby machine can take over resources

As long as my heartbeat is basically able to take over, but the original host recovery, you need to check whether the next recovery.done to become recover.conf, or start will be error

This article from "lly0205.mofile.com" blog, declined reprint!

postgresql9.5.2, pgpool3.5.2, heartbeat3.0.5 ha hot standby configured under DEBIAN8