Skip to content

Instantly share code, notes, and snippets.

@ahmpro
Forked from arkady-emelyanov/haproxy.cfg
Created September 8, 2017 16:41
Show Gist options
  • Save ahmpro/86798d136196842b20cf68a33d7772a8 to your computer and use it in GitHub Desktop.
Save ahmpro/86798d136196842b20cf68a33d7772a8 to your computer and use it in GitHub Desktop.
haproxy check: postgresql is master
# Sample haproxy postgresql master check
#
# haproxy listen: 5431
# pg, instance #1 listen: 5432 (master node)
# pg, instance #2 listen: 5433 (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 5431 -U web template1
# psql (9.4.13)
# Type "help" for help.
#
# template1=#
#
frontend fr_pg
mode tcp
bind *:5431
default_backend bk_pg
backend bk_pg
option tcp-check
tcp-check connect
#
# write: startup message
#
# startup message params:
# user: web
# 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 77656200 # "web"
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 pg_01 127.0.0.1:5432 check inter 5s
server pg_01 127.0.0.1:5433 check inter 5s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment