Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

maxexcloo commented Feb 21, 2018

Is there any way to use this with a password?

@gplv2

This comment has been minimized.

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

This comment has been minimized.

Copy link

FractalizeR commented Jul 2, 2019

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

This comment has been minimized.

Copy link
Owner Author

arkady-emelyanov commented Jul 2, 2019

@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

This comment has been minimized.

Copy link

FractalizeR commented Jul 3, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.