The architecture of the highly available ELMA365 cluster involves the interaction of ELMA365 application microservices with a PostgreSQL database cluster. The HAProxy configuration discussed in this article is designed for load balancing traffic to the Master-Replica(s) cluster.
Example HAProxy сonfigurations:
- Example HAProxy configuration for connecting to PostgreSQL.
- Example HAProxy configuration for connecting to PGBouncer.
Example HAProxy configuration for connecting to PostgreSQL
This configuration is prepared for load balancing traffic in a PostgreSQL cluster deployed according to the description in PostgreSQL Cluster.
- HAProxy queries the Patroni service port
8008 to automatically determine the active PostgreSQL server with the master role; - Write operations coming to
haproxy-server.your_domain:5000 are directed to the server with the master role ; - Read operations coming to
haproxy-server.your_domain:5001are directed to servers with the slave role.
Open the haproxy.cfg configuration file for editing:
sudo nano /etc/haproxy/haproxy.cfg
Example configuration to set up load balancing for a PostgreSQL cluster using HAProxy to add to the file haproxy.cfg:
### PostgreSQL ###
listen postgres_master
bind haproxy-server.your_domain:5000
option tcplog
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:5432 check port 8008
server postgres-server2 postgres-server2.your_domain:5432 check port 8008
server postgres-server3 postgres-server3.your_domain:5432 check port 8008
listen postgres_replicas
bind haproxy-server.your_domain:5001
option tcplog
option httpchk OPTIONS /replica
balance roundrobin
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:5432 check port 8008
server postgres-server2 postgres-server2.your_domain:5432 check port 8008
server postgres-server3 postgres-server3.your_domain:5432 check port 8008
### PostgreSQL ###
Example HAProxy configuration using SSL
Enabling SSL is possible if OpenSSL support is built in. In the parameter crt, specify the path to the PEM file containing the required certificates and associated private keys (fullchain certificate). If the file does not contain a private key, HAProxy will attempt to load the key from the same path with a .key suffix.
If a directory name is used instead of a PEM file, all files found in that directory will be loaded in alphabetical order, excluding files ending in .issuer, .ocsp, or .sctl (reserved solutions). Refer to the configuration manual for the used version of HAProxy for more details. Example for HAProxy 2.5:
### PostgreSQL ###
listen postgres_master
bind haproxy-server.your_domain:5000 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem
option tcplog
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:5432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
server postgres-server2 postgres-server2.your_domain:5432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
server postgres-server3 postgres-server3.your_domain:5432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
listen postgres_replicas
bind haproxy-server.your_domain:5001 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem
option tcplog
option httpchk OPTIONS /replica
balance roundrobin
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:5432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
server postgres-server2 postgres-server2.your_domain:5432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
server postgres-server3 postgres-server3.your_domain:5432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
### PostgreSQL ###
|
Restart HAProxy with the command:
sudo systemctl restart haproxy
Example HAProxy configuration for connecting to PGBouncer
This configuration is prepared for load balancing traffic in a PostgreSQL cluster, deployed according to the description in the PostgreSQL cluster article, through the PGBouncer program installed as described in Install PGBouncer.
- HAProxy queries the Patroni service port
8008 to automatically determine the active PostgreSQL server with the master role; - Write operations coming to
haproxy-server.your_domain:5000 are directed to the server with the master role; - Read operations coming to
haproxy-server.your_domain:5001 are directed to servers with the slave role.
Open the configuration file haproxy.cfg for editing:
sudo nano /etc/haproxy/haproxy.cfg
Example configuration to set up load balancing for PGBouncer with HAProxy to add to the file haproxy.cfg:
### PostgreSQL ###
listen postgres_master
bind haproxy-server.your_domain:5000
option tcplog
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:6432 check port 8008
server postgres-server2 postgres-server2.your_domain:6432 check port 8008
server postgres-server3 postgres-server3.your_domain:6432 check port 8008
listen postgres_replicas
bind haproxy-server.your_domain:5001
option tcplog
option httpchk OPTIONS /replica
balance roundrobin
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:6432 check port 8008
server postgres-server2 postgres-server2.your_domain:6432 check port 8008
server postgres-server3 postgres-server3.your_domain:6432 check port 8008
### PostgreSQL ###
Example HAProxy configuration using SSL
Enabling SSL is possible if OpenSSL support is built in. In the parameter crt, specify the path to the PEM file containing the required certificates and associated private keys (fullchain certificate). If the file does not contain a private key, HAProxy will attempt to load the key from the same path with a .key suffix.
If a directory name is used instead of a PEM file, all files found in that directory will be loaded in alphabetical order, excluding files ending in .issuer, .ocsp, or .sctl (reserved solutions).
In the parameter ca-file, specify the path to the PEM file containing the root certificate. Refer to the Configuration Manual for the used version of HAProxy for more details. Example for HAProxy 2.5:
### PostgreSQL ###
listen postgres_master
bind haproxy-server.your_domain:5000 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem
option tcplog
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:6432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
server postgres-server2 postgres-server2.your_domain:6432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
server postgres-server3 postgres-server3.your_domain:6432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
listen postgres_replicas
bind haproxy-server.your_domain:5001 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem
option tcplog
option httpchk OPTIONS /replica
balance roundrobin
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
server postgres-server1 postgres-server1.your_domain:6432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA.your_domain.pem
server postgres-server2 postgres-server2.your_domain:6432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA.your_domain.pem
server postgres-server3 postgres-server3.your_domain:6432 check port 8008 ssl crt /etc/haproxy/ssl/haproxy-server.your_domain.pem ca-file /etc/haproxy/ssl/rootCA_your_domain.pem
### PostgreSQL ###
|
Restart HAProxy with the command:
sudo systemctl restart haproxy
Found a typo? Highlight the text, press ctrl + enter and notify us
