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

>
> Is this architecture considered a best practice within the PostgreSQL
> community?

I would say Patroni is probably "best practice", but there are other people
out there happy with, and expert with, repmgr as well.

Are there any potential bottlenecks or failure points I should be aware of?

How is pgbouncer supposed to know which db to go to?I would think since
HAProxy has some way to tell, that pgbouncer should go on other side of
haproxy (between the app and haproxy)

Would you recommend any improvements or alternative tools for achieving
> better reliability and performance?

Seems you need a witness server or some sort. Your HA system should have a
few main goals:

* Immediately fail over to a replica should the primary go down
* Reroute the application traffic
* Rebuild the old primary as a replica
* Never, ever allow there to be more than one primary server

Secondary goals:
* make the non-Postgres things redundant (as with keepalived and haproxy)
* provide read/write traffic routing (as you kind of have here)
* some sort of backup system (don't see any here)

Your design will also depend on how bulletproof you want your HA system to
be, and how much data your application is allowed to lose.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support