Comments on: Configure HAProxy with PostgreSQL Using Built-in pgsql-check https://www.percona.com/blog/configure-haproxy-with-postgresql-using-built-in-pgsql-check/ Mon, 02 Oct 2023 15:16:04 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Glenn Plas https://www.percona.com/blog/configure-haproxy-with-postgresql-using-built-in-pgsql-check/#comment-10971449 Mon, 18 Nov 2019 11:56:33 +0000 https://www.percona.com/blog/?p=63230#comment-10971449 Oh Cool, didn’t expect the exposure .. I had to make it myself at the time since it didn’t exist afaik. It’s working really great for a good year now on the busiest database (city of Brussels) and a few other clusters less busy. we have elaborated on the configuration as well doing the inverse, e.g balancing slave queries as well, playing with backup servers (as in haproxy backup parameter). So we have standby slaves that take over when the primary slave doesn’t work anymore. (which serves some read-only requests). Working fine. The only thing I think would be worth some thinking about is this setting :

# close open sessions in case the downed server is still running but is out of sync with the master
default-server on-marked-down shutdown-sessions

As a little glitch with the check (network/failed haproxy check) will disconnect all pending connections, which could otherwise be recovered on TCP level. Good article, there is not enough out there on making true HA postgres clusters. tx

]]> By: Jobin Augustine https://www.percona.com/blog/configure-haproxy-with-postgresql-using-built-in-pgsql-check/#comment-10971410 Mon, 11 Nov 2019 02:50:10 +0000 https://www.percona.com/blog/?p=63230#comment-10971410 Yes Glen, I already saw your repo sometime back. That method is there in my plan as one of the next blog post item in the series. Thank you for your feedback.

]]> By: Glenn Plas https://www.percona.com/blog/configure-haproxy-with-postgresql-using-built-in-pgsql-check/#comment-10971409 Sun, 10 Nov 2019 17:45:37 +0000 https://www.percona.com/blog/?p=63230#comment-10971409 You can implement the check inside haproxy itself simulating psql protocol, using . No need to maintain the hba.conf. I wrote this check and shared it here https://github.com/gplv2/haproxy-postgresql

]]> By: Jobin Augustine https://www.percona.com/blog/configure-haproxy-with-postgresql-using-built-in-pgsql-check/#comment-10971407 Sat, 09 Nov 2019 03:31:44 +0000 https://www.percona.com/blog/?p=63230#comment-10971407 Individual statement routing is NOT something I would recommended. because each packet in the routing server need to be analyzed which slows down the routing. It becomes very complicated when there are statements which are part of long running transactions and Many things in application start breaking if the physical standby gets delayed.
Instead, Oracle like approach is preferred :- Connection routing, where application module decides whether it needs a read-only or read-write connection. HAProxy setup discussed in these blogs helps in Layer 4 routing which is of least overhead and a stable well proven methodology.

]]> By: Mike (@angrymirror) https://www.percona.com/blog/configure-haproxy-with-postgresql-using-built-in-pgsql-check/#comment-10971405 Fri, 08 Nov 2019 19:50:10 +0000 https://www.percona.com/blog/?p=63230#comment-10971405 Thanks for the article! What solution would you suggest for routing r/o and r/w queries.

]]>