Zabbix system to split read / write connections to both HAPROXY listener
Hello everyone,
We are using PGPOOL since few years.
Our usage is the current (everything is on Oracle linux 8):
1 keepalived installed on both PGPOOL (version 4.5.7) servers.
PGOOL manager failover redirection & pooling (no replication, no watchdog)
Then pgpool connect in coming sessions to a 3 nodes Patroni cluster (version 4.0.6) with PGBACKREST (2.56).
Patroni replication is done asynchronously through replication slots.
Our ZABBIX DB is 3,5To sized with a lot of dashboards and workload.
We tried to use PGPOOL load balancing but it isn't working because read queries are blocking replication process with this kind of errors spamming the PG logs:
FATAL: connection was terminated due to conflict with recovery
So pgpool loadbalancing is disabled.
We would like to stop using PGPOOL because its the root cause of 2/3 cluster incidents.
We are setuping a HAproxy with 2 connections strings one for read and the other for writes (and will probably use pgbouncer on the PG servers for pooling).
The main question is the below, how to setup a read/write detection before connections comes to HAPROXY ?
Our research always lead to PGPOOL.... is there any other solution ?
Thank you in advance.
Regards,
We are using PGPOOL since few years.
Our usage is the current (everything is on Oracle linux 8):
1 keepalived installed on both PGPOOL (version 4.5.7) servers.
PGOOL manager failover redirection & pooling (no replication, no watchdog)
Then pgpool connect in coming sessions to a 3 nodes Patroni cluster (version 4.0.6) with PGBACKREST (2.56).
Patroni replication is done asynchronously through replication slots.
Our ZABBIX DB is 3,5To sized with a lot of dashboards and workload.
We tried to use PGPOOL load balancing but it isn't working because read queries are blocking replication process with this kind of errors spamming the PG logs:
FATAL: connection was terminated due to conflict with recovery
So pgpool loadbalancing is disabled.
We would like to stop using PGPOOL because its the root cause of 2/3 cluster incidents.
We are setuping a HAproxy with 2 connections strings one for read and the other for writes (and will probably use pgbouncer on the PG servers for pooling).
The main question is the below, how to setup a read/write detection before connections comes to HAPROXY ?
Our research always lead to PGPOOL.... is there any other solution ?
Thank you in advance.
Regards,
Attached Files