Skip to content

Instantly share code, notes, and snippets.

@arkady-emelyanov
Last active October 25, 2023 22:02
Show Gist options
  • Save arkady-emelyanov/af2993ab242f9a1ec0427159434488c4 to your computer and use it in GitHub Desktop.
Save arkady-emelyanov/af2993ab242f9a1ec0427159434488c4 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_02 127.0.0.1:5433 check inter 5s
@maxexcloo
Copy link

Is there any way to use this with a password?

@gplv2
Copy link

gplv2 commented May 9, 2018

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

@FractalizeR
Copy link

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.

@arkady-emelyanov
Copy link
Author

@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.

@FractalizeR
Copy link

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.

@Elias481
Copy link

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..

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment