PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

We will demonstrate this recipe by using three PostgreSQL servers and one server for HAProxy. We'll make these assumptions:

• There are three PostgreSQL servers with the following attributes:
• pgha1 at 10.0.30.1
• pgha2 at 10.0.30.2
• pgha3 at 10.0.30.3
• There is a dedicated HAProxy node named pgha-proxy.

Follow these steps to configure HAProxy on the pgha-proxy server:

1. Add the following block near the end of the /etc/haproxy/haproxy.cfg file:

frontend ft_pg_ro 
    bind *:5500 
    default_backend bk_pg_ro 
 
backend bk_pg_ro 
    balance leastconn 
    option pgsql-check user haproxy_check 
 
    server postgresql_pgha1 pgha1:5432 check 
    server postgresql_pgha2 pgha2:5432 check 
    server postgresql_pgha3 pgha3:5432 check 

2. Trigger HAProxy to reload the configuration ...