gem-hunter
Language: Python
Create a haproxy loadbalancer configuration that ensures the target server to be a postgresql master
Use this to determine who's the master server in a postgresql cluster setup. this sends a select pg_is_in_recovery() command to the psql servers to see if it is in standby more or not. haproxy will only mark masters as up. The standby node is in backup mode, so haproxy will not try to write to it unless the master node is down AND the standby node is promoted to a master
You need to setup a trust connection between haproxy and postgresql. In this example there are 2 pgbouncers in between haproxy and postgresql. This means you need to install the postgresql nodes at a different port (here 6432). Trust connection suggestions will be shown when you run the script.
calculates byte length so the tcp package is well constructed, when changing length of username, you need to adjust.
This is tested in conjunction with repmgrd, pgbouncer, keepalived/haproxy architected setup, but without a witness server.
The latest version will block all connections when it detects more or less than a single master server. You do not want to write your data in just any server. In the case you have 2 disconnected masters servers (meaning 2 servers that aren't standby/slaves) they both will pass this check. Hence a built-in ACL will prevent writing to any servers, which as a DBA is what you want. It's better to block this than to accept , also this way you don't show bias towards a node when using the backup directive for example for your second node. It's not a good idea when a former master comes back after a failure, it will be the primary candidate to write to, but in such cases, your standby server should already be promoted and the old primary should not be written to anymore without resyncing it with the freshly promoted standby.
use this Vagrant setup to see it in action : https://github.com/gplv2/vagrant-postgres
HA_MASTER_NAME = "node1"
HA_MASTER_DSN = "192.168.1.144:5432"
HA_STANDBY_NAME = "node2"
HA_STANDBY_DSN = "192.168.1.145:5432"
HA_VIP_IP = "192.168.1.141"
HA_CHECK_USER = "pgcheck"
HA_CHECK_PORT = "6432"
HA_LISTEN_PORT = "5432"
HA_STATS_USER = "hapsql"
HA_STATS_PASSWORD = "snowball1"
edit config.py, set vars
run it : ./create_haproxy_check.py standby mystandby
alternatively, you can also test the redirect config : ./create_haproxy_check.py redirect myredirect
There are 2 templates now, standby will make haproxy mark slaves as bad candidates. the redirect template will allow you to filter out a rogue client to redirect it towards the correct master server while letting legitimate connections (monitoring, admin etc) pass based on ip address ACL's
Creating haproxy project mytest
Creating configs/mytest/haproxy-mytest.cnf
Add the following lines to pg_hba.conf:
# special loadbalancer account in trust
host template1 pgcheck 192.168.1.141/32 trust
host template1 pgcheck 192.168.1.144/32 trust
host template1 pgcheck 192.168.1.145/32 trust
Add the following lines to pg_hba.conf:
# repmgr account
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 192.168.1.144/32 trust
host replication repmgr 192.168.1.145/32 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 192.168.1.144/32 trust
host repmgr repmgr 192.168.1.145/32 trust