# Configure HAProxy for PostgreSQL

> [HTML Version](haproxy-postgresql.html)

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:

1. [Example HAProxy configuration for connecting to PostgreSQL](#haproxypostgresql).

2. [Example HAProxy configuration for connecting to PGBouncer](#haproxypgbouncer).

## 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](configure-postgresql.md). HAProxy is used for balancing. It automatically checks port \[OBJECT\] of Patroni service on PostgreSQL servers with the \[OBJECT\] role.

The operation traffic to the cluster is distributed as follows:

- Write operations coming to \[OBJECT\] are directed to the server with the \[OBJECT\] role.

- Read operations coming to \[OBJECT\] are directed to servers with the \[OBJECT\] 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:

1. Open the \[OBJECT\] configuration file for editing using the following command:

````
sudo nano /etc/haproxy/haproxy.cfg

2. ````
Edit the \[OBJECT\] 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 \[OBJECT\] parameter, specify the path to the PEM file containing the required certificates and associated private keys ([fullchain certificate](fullchain-sertificate.md)). 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 \[OBJECT\] 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](https://cbonte.github.io/haproxy-dconv/2.5/configuration.html#5.1-crt):

````
\### 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 ###
````

3. ````
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](configure-postgresql.md) article, through the [PGBouncer](pgbouncer-installation.md) program. HAProxy is used for balancing. It automatically checks port \[OBJECT\] of Patroni service on PostgreSQL servers with the \[OBJECT\] role.

The operation traffic to the cluster is distributed as follows:

- Write operations coming to \[OBJECT\] are directed to the server with the \[OBJECT\] role.

- Read operations coming to \[OBJECT\] are directed to servers with the \[OBJECT\] 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:

1. Open the configuration file \[OBJECT\] for editing using the following command:

````
sudo nano /etc/haproxy/haproxy.cfg

2. ````
Edit the \[OBJECT\] 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 \[OBJECT\] parameter, specify the path to the PEM file containing the required certificates and associated private keys ([fullchain certificate](fullchain-sertificate.md)). 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 \[OBJECT\] 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](https://cbonte.github.io/haproxy-dconv/2.5/configuration.html#5.1-crt):  


````
\### 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 ###
````

3. ````
Restart HAProxy to apply changes:

````
sudo systemctl restart haproxy
````