-
-
Save arkady-emelyanov/af2993ab242f9a1ec0427159434488c4 to your computer and use it in GitHub Desktop.
# 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 |
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..
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.