Streamlining Postgres Performance: Read-Only App Configuration with Patroni

In Postgres HA cluster passive nodes are always underutilized and master node is overutilized. In order to minimize this gap we can make use of existing setup to redirect read-only requests to passive replicas.

There are certain requirements when you require to access non-live and read only data from production. Even if it's not explicitly stated by application team you can check if the application can suffice with this access mode. We will see how we can setup Postgres production access for such applications.

Below is the architecture of whole setup we did for Postgres HA.

You can see pgnode2 and pgnode3 are passive Postgres nodes having replicated data from pgnode1. Applications which needs live data and write access will get redirected to pgnode1. We are setting up read only access to passive nodes which will have following advantages:

• Reduced load on master node
• Balanced usage of resources
• Access to third party apps
• Extra layer of security as passive replicas are read-only

Setup

You need to create a listener on HAProxy which will be dedicated to read-only apps and will point to follower (non-leader) Postgres replicas.

Add below code snippet in haproxy.cfg and restart haproxy service to make changes effective.

listen bootvar_readonly
    bind *:5433
    option httpchk
    http-check expect status 503
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pgdb1_5432 192.168.56.204:5432 maxconn 100 check port 8008
    server pgdb2_5432 192.168.56.205:5432 maxconn 100 check port 8008
    server pgdb3_5432 192.168.56.206:5432 maxconn 100 check port 8008

Below line in the snippet tells haproxy to expect 503 status from 8008 port of patroni. If it receives 503 status all traffic coming to haproxy1:5433 port will be redirected to those servers with 5432 port (Postgres Port).

http-check expect status 503

Note: Request status will be handled by Patroni. If node is leader patroni will return status 200 and if it's a replica then it returns 503.

Application can now connect to passive postgres replicas using below details:

Host: haproxy1 (haproxy1.bootvar.com), haproxy2 (haproxy2.bootvar.com)
Port: 5433
Credentials: Postgres User credentials