Date: Sun, 4 Aug 2019 02:27:39 +0200
From: =?utf-8?Q?J=C4=81nis_P=C5=ABris?= <janis@puris.lv>
To: pgsql-admin@postgresql.org
Message-ID: <6ae63a77-5f9c-4f57-b409-e79a8b0244fb@Spark>
In-Reply-To: <5c247980-5a8f-4864-9e5a-3d330adcc253@Spark>
References: <5c247980-5a8f-4864-9e5a-3d330adcc253@Spark>
Subject: HAProxy + Patroni + pgBouncer High Availability setup
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="5d462680_749abb43_bb71"
Precedence: bulk

--5d462680_749abb43_bb71
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello,

I have a working HAProxy, Patroni, PG stack and would like to add pgbounc=
er in the mix.

Current topology descr.
Haproxy is checking Patroni API, if the node it is responsible for is pri=
mary, if so, the requests are routed to it's postgresql server. When a fa=
ilure occurs Patroni would do its magic and a different node in the clust=
er is promoted, HAProxy sees it via Patroni API checks and requests are r=
erouted to this new node. It works=21

The problem
if I'd deploy pgBouncer between HAProxy and PostgreSQL, it would be a sin=
gle point of failure. =46or example, if pgBouncer fails on primary, Patro=
ni would not mind and no other node would take primary to mitigate the di=
saster.

Topology
=5Bclient=5D <--> =5Bhaproxy=5D <---> =5Bpgbouncer=5D <---> =5Bpostgres=5D=

=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=7C=
-----------> =5Bpatroni=5D <-------->/

How can I improve the architecture, to avoid single point of failure =3F =
I could of course add watchdog and similar processes, but I would really =
prefer not too overcomplicate the stack.

P.S. Deploying pgBouncer before client app is not an option, as a lot of =
different API and clients are talking to the cluster, as well as they are=
 deployed in range of environments, i.e. kubernetes, metal, vms etc and s=
o on.

Best regards, JP.

--5d462680_749abb43_bb71
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hello,
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>I have a working HAProxy, Patroni, PG stack and wou=
ld like to add pgbouncer in the mix.</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22><b>Current topology descr.</b></div>
<div dir=3D=22auto=22>Haproxy is checking Patroni API, if the node it is =
responsible for is primary, if so, the requests are routed to it's postgr=
esql server. When a failure occurs Patroni would do its magic and a diffe=
rent node in the cluster is promoted, HAProxy sees it via Patroni API che=
cks and requests are rerouted to this new node. It works=21</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22><b>The problem</b></div>
<div dir=3D=22auto=22><span style=3D=22caret-color: rgb(213, 218, 222);=22=
>if I'd deploy pgBouncer between HAProxy and PostgreSQL, it would be a si=
ngle point of failure. =46or example, if pgBouncer fails on primary, Patr=
oni would not mind and no other node would take primary to mitigate the d=
isaster.</span></div>
<div dir=3D=22auto=22><span style=3D=22caret-color: rgb(213, 218, 222);=22=
><br /></span></div>
<div dir=3D=22auto=22><b>Topology</b></div>
<div dir=3D=22auto=22>=5Bclient=5D &lt;--&gt; =5Bhaproxy=5D &lt;---&gt; =5B=
pgbouncer=5D &lt;---&gt; =5Bpostgres=5D<br />
=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=7C=
-----------&gt; =5Bpatroni=5D &lt;--------&gt;/<br /></div>
<div dir=3D=22auto=22><span style=3D=22caret-color: rgb(213, 218, 222);=22=
><br /></span></div>
<div dir=3D=22auto=22>How can I improve the architecture, to avoid single=
 point of failure =3F I could of course add watchdog and similar processe=
s, but I would really prefer not too overcomplicate the stack.</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>P.S. Deploying pgBouncer before client app is not a=
n option, as a lot of different API and clients are talking to the cluste=
r, as well as they are deployed in range of environments, i.e. kubernetes=
, metal, vms etc and so on.</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>Best regards, JP.</div>
</div>
</div>
</body>
</html>

--5d462680_749abb43_bb71--