From: Matthias Leisi <matthias@leisi.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_F449998B-15C3-4047-8B89-06C3172F5D97"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Advice on cluster architecture for two related, but distinct, use
 cases
Message-Id: <897B047D-B99D-4637-ACBD-E98EC3D38DD5@leisi.net>
Date: Thu, 7 Nov 2024 17:43:46 +0100
To: pgsql-general@postgresql.org
Archived-At: <https://www.postgresql.org/message-id/897B047D-B99D-4637-ACBD-E98EC3D38DD5%40leisi.net>
Precedence: bulk


--Apple-Mail=_F449998B-15C3-4047-8B89-06C3172F5D97
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Dear all,

(This is a follow-up to a question I asked almost exactly a year ago, =
https://postgrespro.com/list/thread-id/2670756#726F3765-858C-4AC0-A7B0-5CB=
6720E4B37@leisi.net - the requirements have changed since then, and the =
platform has changed from OpenBSD to Linux, which may make some things =
easier.)


I=E2=80=99m looking for advice on Postgres cluster architecture(s) for =
two related but distinct use cases. Ideally, the approaches for the two =
use cases would not differ too widely.

The goal of clustering is low RPO (I guess we need sync clustering) and =
RTO (ideally almost-instant failover); throughput is not a concern =
(it=E2=80=99s relatively low transaction volume). Latency (due to the =
distance between datacenters which provide georedundancy) is a fact we =
are willing to accept (eg for syncs to happen on all cluster members).


The first use case is in an environment under our own control (and where =
eg a DBA could intervene). We can theoretically run any number of =
cluster instances, but assume we would use an even number (split over =
the two datacenters), or potentially an odd number of nodes (eg with an =
arbiter).


In the second use case, the environment is not under our control, so we =
can only assume basic network connectivity from the application to the =
DB, and between the DBs (the latter potentially through an SSH tunnel if =
needed). In this use case, we can not assume a person to intervene if a =
node goes down, and would prefer some automated failover to the other =
node (this automation would also be welcome for the first use case, eg =
if something happens while nobody is watching). We can not assume eg a =
load balancer.

There could be various ways how the environment in the second use case =
is set up, ranging from =E2=80=9Eapplication and database running on the =
same box=E2=80=9C (well, no clustering for you then=E2=80=A6), to =
dedicated two- or three node cluster.


In both use cases, we have full control over the application and the =
database itself.

=46rom reading various docs, it seems we would need something like =
Patroni (/Percona), at least for the first use case. However it seems =
relatively complex to set up and operate to me.

I would appreciate your experience and input into which approach would =
best fit the two use cases. We are also willing to engage in paid =
consulting.

Thanks,
=E2=80=94 Matthias

--=20
Matthias Leisi
Katzenr=C3=BCtistrasse 68, 8153 R=C3=BCmlang
Mobile +41 79 377 04 43
matthias@leisi.net


--Apple-Mail=_F449998B-15C3-4047-8B89-06C3172F5D97
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">Dear =
all,<div><br></div><div>(This is a follow-up to a question I asked =
almost exactly a year ago,&nbsp;<a =
href=3D"https://postgrespro.com/list/thread-id/2670756#726F3765-858C-4AC0-=
A7B0-5CB6720E4B37@leisi.net">https://postgrespro.com/list/thread-id/267075=
6#726F3765-858C-4AC0-A7B0-5CB6720E4B37@leisi.net</a>&nbsp;- the =
requirements have changed since then, and the platform has changed from =
OpenBSD to Linux, which may make some things =
easier.)</div><div><br></div><div><br></div><div>I=E2=80=99m looking for =
advice on Postgres cluster architecture(s) for two related but distinct =
use cases. Ideally, the approaches for the two use cases would not =
differ too widely.</div><div><br></div><div>The goal of clustering is =
low RPO (I guess we need sync clustering) and RTO (ideally =
almost-instant failover); throughput is not a concern (it=E2=80=99s =
relatively low transaction volume). Latency (due to the distance between =
datacenters which provide georedundancy) is a fact we are willing to =
accept (eg for syncs to happen on all cluster =
members).</div><div><br></div><div><br></div><div>The first use case is =
in an environment under our own control (and where eg a DBA could =
intervene). We can theoretically run any number of cluster instances, =
but assume we would use an even number (split over the two datacenters), =
or potentially an odd number of nodes (eg with an =
arbiter).</div><div><br></div><div><br></div><div>In the second use =
case, the environment is not under our control, so we can only assume =
basic network connectivity from the application to the DB, and between =
the DBs (the latter potentially through an SSH tunnel if needed). In =
this use case, we can not assume a person to intervene if a node goes =
down, and would prefer some automated failover to the other node (this =
automation would also be welcome for the first use case, eg if something =
happens while nobody is watching). We can not assume eg a load =
balancer.</div><div><br></div><div>There could be various ways how the =
environment in the second use case is set up, ranging from =
=E2=80=9Eapplication and database running on the same box=E2=80=9C =
(well, no clustering for you then=E2=80=A6), to dedicated two- or three =
node cluster.</div><div><br></div><div><br></div><div>In both use cases, =
we have full control over the application and the database =
itself.</div><div><br></div><div>=46rom reading various docs, it seems =
we would need something like Patroni (/Percona), at least for the first =
use case. However it seems relatively complex to set up and operate to =
me.</div><div><br></div><div>I would appreciate your experience and =
input into which approach would best fit the two use cases. We are also =
willing to engage in paid =
consulting.</div><div><br></div><div>Thanks,</div><div>=E2=80=94 =
Matthias</div><div><br><div>
<meta charset=3D"UTF-8"><div style=3D"color: rgb(0, 0, 0); =
letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><div =
style=3D"color: rgb(0, 0, 0); letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div style=3D"color: rgb(0, 0, 0); letter-spacing: =
normal; text-align: start; text-indent: 0px; text-transform: none; =
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
word-wrap: break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;">--&nbsp;</div><div style=3D"color: rgb(0, 0, 0); =
letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">Matthias =
Leisi<br>Katzenr=C3=BCtistrasse 68, 8153 R=C3=BCmlang<br>Mobile +41 79 =
377 04 43<br>matthias@leisi.net<br><br><br></div></div></div>
</div>
<br></div></body></html>=

--Apple-Mail=_F449998B-15C3-4047-8B89-06C3172F5D97--