pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Dominique Devienne <ddevienne@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Using PostgreSQL for service discovery and health-check
Date: Thu, 9 Feb 2023 08:05:52 -0800
Message-ID: <a7bc901a-a191-9504-c5be-41de7128c5b9@aklaver.com> (raw)
In-Reply-To: <CAFCRh-9okm64gEM-Oy27Lh-2k=PKEBbK1C5cG1vgwEjgN-EB+w@mail.gmail.com>
References: <CAFCRh-9okm64gEM-Oy27Lh-2k=PKEBbK1C5cG1vgwEjgN-EB+w@mail.gmail.com>

On 2/9/23 07:30, Dominique Devienne wrote:
> Hi. I'm requesting advice on something I'm about to start on.
> 
> In a normally 2-tier system, where "desktop" native apps connect 
> directly to PostgreSQL to access data, some things must be mediated via 
> a middle-tier service (i.e. 3-tier architecture). That service is HTTP 
> based thus clients (the "desktop" native apps) must know the URL 
> (host+port+path) of a server providing the requested service.

Is there more then one server providing the same service?

> 
> Since clients must already have access to PostgreSQL to operate, I 
> wanted to add a table in PostgreSQL for services, and server(s) on 
> startup (probably using a random port) would register in that table, and 
> deregister on shutdown. Also, since crashes are a fact of life, the 
> server would regularly update the DB with a "heartbeat" on some 
> frequency (e.g. 1s?), so clients would select (or see) only registered 
> services with a "fresh enough" heartbeat timestamp.

Would it no be easier to not have random ports and just attempt 
connections to the servers either:

1) In the client with reattempt to different port on failure.

2) From Postgres server and update table to have current up servers.

> 
> That's basically my plan. Now come the questions:
> 1) will updating a row every second (for example) create issues?
> 2) if yes to #1 above, what would be good mitigation tactics? Use 
> different table for service vs heartbeat? Special kind of table? or 
> configured in a particular way?
> 3) if a service crashes, it won't remove its row(s), obviously. What 
> kind of mechanism exists to "reap" "zombie" services?
> 4) Related to #3 above, I think built-in "cron"-like services are only 
> available via extensions, not in PostgreSQL proper. Why? Seems like such 
> an essential service.
> 5) Which cron-like extension to use? Especially since we run both 
> on-prem but also in managed-PostgreSQL on the cloud?
> 
> I'd appreciate community input. Thanks, --DD
> 
> PS: Note that there could be more than 1 server registered, for the same 
> service, possibly on the same machine, for redundancy. But I think 
> that's mostly orthogonal to my questions above.

-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (9+ messages)  latest in thread

Message-ID: <a7bc901a-a191-9504-c5be-41de7128c5b9@aklaver.com>
Permalink:  ../a7bc901a-a191-9504-c5be-41de7128c5b9@aklaver.com/
Also on:    postgresql.org/message-id/a7bc901a-a191-9504-c5be-41de7128c5b9@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, ddevienne@gmail.com
  Subject: Re: Using PostgreSQL for service discovery and health-check
  In-Reply-To: <a7bc901a-a191-9504-c5be-41de7128c5b9@aklaver.com>
  References: <CAFCRh-9okm64gEM-Oy27Lh-2k=PKEBbK1C5cG1vgwEjgN-EB+w@mail.gmail.com> <a7bc901a-a191-9504-c5be-41de7128c5b9@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox