Configure HAProxy for PostgreSQL
The architecture of the highly available BRIX cluster involves the interaction of BRIX application microservices with a PostgreSQL database cluster. To ensure secure load balancing in the system, create a HAProxy configuration for PostgreSQL. This way, in case of a failure, all traffic will be redirected to the Master-Replica(s) cluster, which guarantees continuous operation of the system.
Examples of a HAProxy сonfiguration:
- Example HAProxy configuration for connecting to PostgreSQL.
- Example HAProxy configuration for connecting to PGBouncer.
Example of a 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 is used for balancing. It automatically checks port 8008 of Patroni service on PostgreSQL servers with the master role.
The operation traffic to the cluster is distributed as follows:
- 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.
In case of a failure, all traffic will be redirected to the Master-Replica(s) cluster, i.e. write and read operations will start to arrive here.
In order to create a HAProxy configuration for PostgreSQL, follow these steps:
- Open the
haproxy.cfg configuration file for editing using the following command:
sudo nano /etc/haproxy/haproxy.cfg
- Edit the
haproxy.cfg configuration file:
Configuration example:
### 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 of a HAProxy configuration using SSL
Enabling SSL is possible if OpenSSL support is built in. In the crt parameter, 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, and .sctl (reserved solutions).
In the ca-file parameter, specify the path to the PEM file containing the root certificate. For more details, refer to the Configuration Manual for the used version of HAProxy. For 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 to apply changes:
sudo systemctl restart haproxy
Example of a 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. HAProxy is used for balancing. It automatically checks port 8008 of Patroni service on PostgreSQL servers with the master role.
The operation traffic to the cluster is distributed as follows:
- 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.
In case of a failure, all traffic will be redirected to the Master-Replica(s) cluster, i.e. write and read operations will start to arrive here.
In order to create a HAProxy configuration through the PGBouncer program, perform the following actions:
- Open the configuration file
haproxy.cfg for editing using the following command:
sudo nano /etc/haproxy/haproxy.cfg
- Edit the
haproxy.cfg configuration file:
Configuration example
### 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 of a HAProxy configuration using SSL
Enabling SSL is possible if OpenSSL support is built in. In the crt parameter, 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, and .sctl (reserved solutions).
In the ca-file parameter, specify the path to the PEM file containing the root certificate. For more details, refer to the Configuration Manual for the used version of HAProxy. For 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 to apply changes:
sudo systemctl restart haproxy
