haproxy check: postgresql is master

haproxy.cfg

# 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_02 127.0.0.1:5433 check inter 5s

Is there any way to use this with a password?

This is an awesome config. I've been looking and testing this and it works really great. takes haproxy + psql balancing to new possibilities . I do wonder, I tried to find a way to calculate the package length (maybe write a generator to build this) , any chance to share with me how you calculate this? I noticed I get CEST >FATAL: 08P01: invalid startup packet layout: expected terminator as last byte when I make it longer, but I would love to know how you calculate the package length exactly. Thanks very much for sharing

Before using this in production, keep in mind that when new Postgres version is out, you might need to parse and understand it's binary protocol changes in order to improve the script.

@FractalizeR

Before using this in production, keep in mind that when new Postgres version is out, you might need to parse and understand it's binary protocol changes in order to improve the script.

Just FYI: Version 3 has been used since 7.4, and still used in PG12: https://www.postgresql.org/docs/12/protocol.html
This configuration file implements v3 protocol.

Yeah, I understand, that protocol changes are rare. The problem may come to the project not from making this particular decision, but from making same class of decisions on regular terms.

Thanks this is what I needed (to deliver a statement and an example implementation, anyway I'm not convinced that this is the way to go).

I would send the close immediately after query to properly close connection also on non-master and get according state instead of timeout and included the plain authentication (thought often it might be ok to go completely without auth and plain auth does at least add something to no-auth and using ident for auth would be quite huge overhead, but this checks the server a bit more).

Also of course in the fromtend one should only forward to the backend if exactly one server is available, to make it a bit safe for work..