haproxy check: postgresql is master

haproxy.cfg

# haproxy postgresql master check
#
# haproxy listen: 5432
# pg, instance #1 listen: 5432 (master node)
# pg, instance #2 listen: 5432 (replica node)
# external failover, promoting replica to master in case of failure
# passwordless auth for user web
# template1 database is accessible by user web
#
# haproxy will pass connection to postgresql master node:
# $ psql -h 127.0.0.1 -p 5432 -U pgc template1
# psql (9.4.13)
# Type "help" for help.
#
# template1=#
#

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    spread-checks 5

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout queue           1m
    timeout connect         1s
    timeout client          3600s
    timeout server          3600s
    timeout check           2s
    maxconn                 500
#---------------------------------------------------------------------
# statistics
#---------------------------------------------------------------------
# Host HA-Proxy's web stats on Port 8182.

listen HAProxy-Statistics *:8182
    mode http
    option httplog
    stats enable
    stats uri /haproxy?stats
    stats refresh 20s
    stats realm PSQL Haproxy\ Statistics  # Title text for popup window
    stats show-node
    stats show-legends
    stats show-desc PSQL load balancer stats (master)
    stats auth pgadmin:snowball1

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------

frontend front_pg
	mode tcp
	bind *:5432
	default_backend backend_pg

backend backend_pg
	option tcp-check
	tcp-check connect

	#
	# write: startup message
	#
	# startup message params:
	# user: pgcheck
	# database: template1
	#
	tcp-check send-binary 00000025 # packet length
	tcp-check send-binary 00030000 # protocol version
	tcp-check send-binary 7573657200 # "user"
	tcp-check send-binary 70676300 # "pgc"
	tcp-check send-binary 646174616261736500 # "database"
	tcp-check send-binary 74656d706c6174653100 # "template1"
	tcp-check send-binary 00 # terminator

	# expect: Auth
	tcp-check expect binary 52 # Auth request
	tcp-check expect binary 00000008 # packet length
	tcp-check expect binary 00000000 # auth response ok 

	# write: run simple query
	# "select pg_is_in_recovery();"
	#
	tcp-check send-binary 51 # simple query
	tcp-check send-binary 00000020 # packet length 
	tcp-check send-binary 73656c65637420 # "select "
	tcp-check send-binary 70675f69735f696e5f7265636f7665727928293b # "pg_is_in_recovery();"
	tcp-check send-binary 00 # terminator

	# expect: Row description packet
	#
	tcp-check expect binary 54 # row description packet (1 byte)
	tcp-check expect binary 0000002a # packet length: 42 (0x2a)
	tcp-check expect binary 0001 # field count: 1
	tcp-check expect binary 70675f69735f696e5f7265636f7665727900 # field name: pg_is_in_recovery
	tcp-check expect binary 00000000 # table oid: 0
	tcp-check expect binary 0000 # column index: 0
	tcp-check expect binary 00000010 # type oid: 16
	tcp-check expect binary 0001 # column length: 1
	tcp-check expect binary ffffffff # type modifier: -1
	tcp-check expect binary 0000 # format: text

	# expect: query result data
	#
	# "f" means node in master mode
	# "t" means node in standby mode (read-only)
	#
	tcp-check expect binary 44 # data row packet
	tcp-check expect binary 0000000b # packet lenght: 11 (0x0b)
	tcp-check expect binary 0001 # field count: 1
	tcp-check expect binary 00000001 # column length in bytes: 1
	tcp-check expect binary 66 # column data, "f"

	# write: terminate session
	tcp-check send-binary 58 # Termination packet
	tcp-check send-binary 00000004 # packet length: 4 (no body)

	# server list to check
	server pgmaster 192.168.60.144:5432 check inter 5000 fastinter 2000 downinter 5000 rise 2 fall 3 port 6432
	server pgstandby 192.168.60.145:5432 check inter 5000 fastinter 2000 downinter 5000 rise 2 fall 3 port 6432 backup