From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Using PostgreSQL for service discovery and health-check |
Date: | 2023-02-09 15:30:06 |
Message-ID: | CAFCRh-9okm64gEM-Oy27Lh-2k=PKEBbK1C5cG1vgwEjgN-EB+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
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.
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dominique Devienne | 2023-02-09 15:41:41 | WHERE col = ANY($1) extended to 2 or more columns? |
Previous Message | Jehan-Guillaume de Rorthais | 2023-02-09 13:49:47 | Re: Question regarding UTF-8 data and "C" collation on definition of field of table |