MIME-Version: 1.0
References: <CAD5k+7wgFmO7V_ncFCHBOzO0d9H51O5aKDD+hcnrCbUgZomqiQ@mail.gmail.com>
In-Reply-To: <CAD5k+7wgFmO7V_ncFCHBOzO0d9H51O5aKDD+hcnrCbUgZomqiQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 21 Jul 2025 15:26:37 -0400
Message-ID: <CAKAnmmLxh5bzdi_f7FJX+fq7YqEGMZ9+FVKBi0uKG44ssTEgLQ@mail.gmail.com>
Subject: Re: Request for Feedback on PostgreSQL HA + Load Balancing Architecture
To: vijay patil <vijay.postgres@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000065e4e8063a757611"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLxh5bzdi_f7FJX%2Bfq7YqEGMZ9%2BFVKBi0uKG44ssTEgLQ%40mail.gmail.com>
Precedence: bulk

--00000000000065e4e8063a757611
Content-Type: text/plain; charset="UTF-8"

>
> 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

--00000000000065e4e8063a757611
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex">Is this architecture considered a best practice within the Po=
stgreSQL community?</blockquote><div><br></div></div><div>I would say Patro=
ni is probably &quot;best practice&quot;, but there are other people out th=
ere happy with, and expert with, repmgr as well.</div><div><br></div><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex">Are there any potential bottlen=
ecks or failure points I should be aware of?</blockquote><div><br class=3D"=
gmail-Apple-interchange-newline"></div><div>How is pgbouncer supposed=C2=A0=
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 hap=
roxy)</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
>Would you recommend any improvements or alternative tools for achieving be=
tter reliability and performance?</blockquote><div><br></div><div>Seems you=
 need a witness server or some sort. Your HA system should have a few main =
goals:</div><div><br></div><div>* Immediately fail over to a replica should=
=C2=A0the primary go down</div><div>* Reroute the application traffic</div>=
<div>* Rebuild the old primary as a replica</div><div>* Never, ever allow t=
here to be more than one primary server</div><div><br></div><div>Secondary =
goals:</div><div>* make the non-Postgres things redundant (as with keepaliv=
ed and haproxy)</div><div>* provide read/write traffic routing (as you kind=
 of have here)</div><div>* some sort of backup system (don&#39;t see any he=
re)</div><div><br></div><div>Your design will also depend on how bulletproo=
f you want your HA system to be, and how much data your application is allo=
wed to lose.</div><div><br></div><div>Cheers,</div></div><div dir=3D"ltr" c=
lass=3D"gmail_signature"><div dir=3D"ltr"><div>Greg</div><div><br></div><di=
v>--</div><div>Crunchy Data - <a href=3D"https://www.crunchydata.com" targe=
t=3D"_blank">https://www.crunchydata.com</a></div><div>Enterprise Postgres =
Software Products &amp; Tech Support</div><div><br></div></div></div></div>

--00000000000065e4e8063a757611--