MIME-Version: 1.0
References: <897B047D-B99D-4637-ACBD-E98EC3D38DD5@leisi.net>
In-Reply-To: <897B047D-B99D-4637-ACBD-E98EC3D38DD5@leisi.net>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 11 Nov 2024 09:27:33 -0500
Message-ID: <CAKAnmm+VQXYJAFczyo01JZoQcx4CVoM26xyUuVmjbbujR0u=5w@mail.gmail.com>
Subject: Re: Advice on cluster architecture for two related, but distinct, use cases
To: Matthias Leisi <matthias@leisi.net>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d609d00626a3e881"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BVQXYJAFczyo01JZoQcx4CVoM26xyUuVmjbbujR0u%3D5w%40mail.gmail.com>
Precedence: bulk

--000000000000d609d00626a3e881
Content-Type: text/plain; charset="UTF-8"

Some of those requirements are vague, but yes, Patroni should probably be
the first approach you look at. If the second datacenter is just for
redundancy, then a simple setup would be:

DCA (data center A):
Postgres server 1
Postgres server 2

DCB:
Postgres server 3 (set no_failover: true)

You will also need a DCS system of some sort (e.g. etcd on all three
nodes), as well as a backup system (e.g. pgBackRest). Will also need to
decide how automated you want things to be (for example, cross datacenter
failover in the above would be manually done). It should definitely be able
to handle your RPO/RTO requirements easily enough.

[Patroni] However it seems relatively complex to set up and operate


Setting things up can be a little complex, yes, but once done it just
works, so very little operation resources are needed.

We can not assume eg a load balancer.


Possible via the application: see
https://www.postgresql.org/docs/current/libpq-connect.html (esp.
target_session_attrs)

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Some of those requirements are vague=
, but yes, Patroni should probably be the first approach you look at. If th=
e second datacenter is just for redundancy, then a simple setup would be:</=
div><div><br></div><div>DCA (data center A):</div><div>Postgres server 1</d=
iv><div>Postgres server 2</div><div><br></div><div>DCB:</div><div>Postgres =
server 3 (set no_failover: true)</div><div><br></div><div>You will also nee=
d a DCS system of some sort (e.g. etcd on all three nodes), as well as a ba=
ckup system (e.g. pgBackRest). Will also need to decide how automated you w=
ant things to be (for example, cross datacenter failover in the above would=
 be manually done). It should definitely be able to handle your RPO/RTO req=
uirements easily enough.</div><div><br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">[Patroni] However it seems relatively complex to set up=
 and operate</blockquote><div><br></div><div>Setting things up can be a lit=
tle complex, yes, but once done it just works, so very little operation res=
ources are needed.=C2=A0</div><div><br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">We can not assume eg a load balancer.</blockquote><div>=
<br></div><div>Possible via the application: see=C2=A0<a href=3D"https://ww=
w.postgresql.org/docs/current/libpq-connect.html">https://www.postgresql.or=
g/docs/current/libpq-connect.html</a> (esp. target_session_attrs)</div><div=
><br></div><div>Cheers,</div><div>Greg</div><div><br></div></div>
</div>

--000000000000d609d00626a3e881--