what i have done is create frontends and backends for all of the load
balanced nodes, and separate f/e and b/e for the individual nodes. for
instance:
frontend mariadb
mode tcp
bind 192.168.120.3:3306
default_backend mariadb
frontend mariadb1
mode tcp
bind 192.168.120.3:3316
default_backend mariadb1
frontend mariadb2
mode tcp
bind 192.168.120.3:3326
default_backend mariadb2
frontend mariadb3
mode tcp
bind 192.168.120.3:3336
default_backend mariadb3
...
backend mariadb
source 192.168.120.3
mode tcp
option mysql-check user haproxy
server mariadb1 192.168.88.1:3306 check inter 10000 send-proxy-v2
server mariadb2 192.168.88.2:3306 check inter 10000 send-proxy-v2
server mariadb3 192.168.88.3:3306 check inter 10000 send-proxy-v2
backend mariadb1
source 192.168.120.3
mode tcp
option mysql-check user haproxy
server mariadb1 192.168.88.1:3306 check inter 10000 send-proxy-v2
backend mariadb2
source 192.168.120.3
mode tcp
option mysql-check user haproxy
server mariadb2 192.168.88.2:3306 check inter 10000 send-proxy-v2
backend mariadb3
source 192.168.120.3
mode tcp
option mysql-check user haproxy
server mariadb3 192.168.88.3:3306 check inter 10000 send-proxy-v2
by doing this, i can load balance across all mariadb nodes using port
3306, but also hit each of the nodes individually using the same VIP
name, but a different port (3316, 3326, 3336). i chose to keep the same
frontend IP, so that kerberos authentication still works, as the krb
principal is tied to the DNS name of the VIP.
essentially you would wind up with different VIPs for the R/W access and
R/O access.
HTH,
brendan kearney
On 3/15/23 4:12 AM, Илья Шипицин wrote:
there are several L7 balancing tool like pgPool.
as for haproxy, currently it does not provide such advanced postgresql
routing
ср, 15 мар. 2023 г. в 06:09, Muhammed Fahid <mfa...@posibolt.com>:
Hi,
I have A master and a slave PostgreSQL databases. I would like to
know that major read operations can be processed with slave for
reducing load in master ??
for example : I have a large number of products.when customers
want to list all products. Is it possible to read from a slave
database? instead of from the master database ?. If major read
operations are done in master its slows down the other operations
in master.