Hi everyone,
Here is a python generator for an haproxy configuration script that will sense if a postgresql server is a master or a slave by running a query on it with a specific test user (pgc). The user used for that has a trust relation with the db, hence no pw authentication.
See https://github.com/gplv2/haproxy-postgresql
I found the base of this work online, tried to contact the author that had the original idea but I haven’t been able to talk to him and asked about his implementation. So I analysed this haproxy config, improved upon it and dug into how he implemented this so I could create some python code that generates a custom configuration.
The idea is when you perform failover (promote the slave, demote the master , haproxy will notice that the master changed and use the new master server. We use repmgrd by 2nd Quandrant for this.
The only side effect I haven’t been able to figure out yet is this:
<template1-pgc-2018-12-07 10:12:11 CET>LOG: could not receive data from client: Connection reset by peer
<template1-pgc-2018-12-07 10:12:15 CET>LOG: could not receive data from client: Connection reset by peer
<template1-pgc-2018-12-07 10:12:18 CET>LOG: could not receive data from client: Connection reset by peer
It looks like haproxy terminates the connections dirty , although the test code performs a proper exit. It has not been a real problem so far just an annoyance.
The setup we use is 2 node system where haproxy -> pgbouncer -> postgresql are on the same host , and tests are performed directly to postgresql (pgbouncer is bypassed for testing to ensure connection slots on the database)
for master functionality: if more than 1 master is detected, your cluster has an issue and you should not write to any of them , there is a test included to ensure this.
for an example config : https://github.com/gplv2/haproxy-postgresql/blob/master/haproxy-example.cfg
Hope this inspires others like it did me.