Re: Request for Feedback on PostgreSQL HA + Load Balancing Architecture

On 6/18/25 18:12, vijay patil wrote:

> Hi Team,
>
> I am planning to implement a PostgreSQL High Availability (HA) and 
> Load Balancing setup and would greatly appreciate your feedback on the 
> architecture we've designed (see attached diagram).
>
> image.png
>
> *Overview of the Setup:*
>
>  *
>
>     Two PostgreSQL nodes (VM1 and VM2) configured with:
>
>      o
>
>         Asynchronous replication from the Primary DB (on VM1) to the
>         Standby DB (on VM2) using |repmgr|
>
>      o
>
>         HAProxy for failover management:
>
>          +
>
>             HAProxy 1 (active) on VM1
>
>          +
>
>             HAProxy 2 (passive) on VM2 with Keepalive for heartbeat
>             and failover detection
>
>      o
>
>         PgBouncer on both nodes for connection pooling
>
>  *
>
>     A Virtual IP (VIP) managed by HAProxy for routing traffic from the
>     application
>
>  *
>
>     Read/Write operations go to the Primary DB, and Read-Only queries
>     can be served from either node
>
> *Objectives:*
>
>  *
>
>     Ensure high availability with automatic failover
>
>  *
>
>     Enable basic load balancing for read-only queries
>
>  *
>
>     Maintain connection pooling and routing efficiency
>
> *Request for Feedback:*
>
>  *
>
>     Is this architecture considered a best practice within the
>     PostgreSQL community?
>
>  *
>
>     Are there any potential bottlenecks or failure points I should be
>     aware of?
>
>  *
>
>     Would you recommend any improvements or alternative tools for
>     achieving better reliability and performance?
>
Personally I will test long run via app -> pgbouncer -> pgpool-ii in 
order to combine :

* Query Routing (read/write automatic split)

* Load Balancing

* High Availability

* Query caching

Have you consider pgpool-ii ?  I know most support / service companies 
push for patroni, and manually separating read / write traffic from 
within the app, but I still find pgpool's query routing very nice.

>  *
>
>
> Thanks
>
> Vijay
>