From: Matthias Leisi <matthias@leisi.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_EEDDBEDE-6C7E-457F-9C33-3ACF9BA50715"
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: <18DEA0D4-1D33-48CA-A037-FF01AFE37471@leisi.net>
Date: Thu, 7 Nov 2024 22:06:29 +0100
To: pgsql-general@lists.postgresql.org
Archived-At: <https://www.postgresql.org/message-id/18DEA0D4-1D33-48CA-A037-FF01AFE37471%40leisi.net>
Precedence: bulk


--Apple-Mail=_EEDDBEDE-6C7E-457F-9C33-3ACF9BA50715
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, but a failover process of up to a =
minute in the worst case could be acceptable); throughput is not a =
concern (it=E2=80=99s relatively low transaction volume except for some =
often-written statistics data, which is still moderate). Latency (due to =
the distance between datacenters for georedundancy) is a fact we are =
willing to accept.


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). We could use a load balancer, but I guess this would strongly =
deviate from the second use case:


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 database cluster serving a number of =
application machines.


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

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

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 <mailto:matthias@leisi.net>=

--Apple-Mail=_EEDDBEDE-6C7E-457F-9C33-3ACF9BA50715
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, but a failover process of up to a minute in the =
worst case could be acceptable); throughput is not a concern (it=E2=80=99s=
 relatively low transaction volume except for some often-written =
statistics data, which is still moderate). Latency (due to the distance =
between datacenters for georedundancy) is a fact we are willing to =
accept.</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). We could use =
a load balancer, but I guess this would strongly deviate from the second =
use case:</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 database cluster serving a number of application =
machines.</div><div><br></div><div><br></div><div>In both use cases, we =
have full control over the application and the database code and =
environment.</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.</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><div style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><div =
style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;"><div style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: =
after-white-space;">--&nbsp;</div><div style=3D"overflow-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><a =
href=3D"mailto:matthias@leisi.net">matthias@leisi.net</a></div></div></div=
></div></div></body></html>=

--Apple-Mail=_EEDDBEDE-6C7E-457F-9C33-3ACF9BA50715--