Re: Using PostgreSQL for service discovery and health-check - Mailing list pgsql-general

From Dominique Devienne
Subject Re: Using PostgreSQL for service discovery and health-check
Date
Msg-id CAFCRh--59jPEa2Fh_KLf1PnMi6iO33xMqVKi5-M5u_yJRTXDdQ@mail.gmail.com
Whole thread Raw
In response to Re: Using PostgreSQL for service discovery and health-check  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
On Thu, Feb 9, 2023 at 4:46 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Feb 9, 2023 at 8:30 AM Dominique Devienne <ddevienne@gmail.com> wrote:
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?


You can probably get good mileage from CREATE UNLOGGED TABLE.

Thanks. Although I guess the fact it's not replicated to standby servers could be a problem?
That's not something we test now, and also something the on-prem DBA (i.e. a client of ours)
might want to setup on his/her own, on top of our client/server arch I guess.

I have no experience with stand-bys (replication) and HA in PostgreSQL.
Would having the main service table be a regular one, and the service_heartbeat be an unlogged one be replication friendly?
I.e. if fail over to the stand-by happens, the service table is still there and populated, but the service_heartbeat is empty, but
then the services would start populating it "transparently" no? I.e. would using 2 tables instead of 1 be a better design?

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: WHERE col = ANY($1) extended to 2 or more columns?
Next
From: Dominique Devienne
Date:
Subject: Re: WHERE col = ANY($1) extended to 2 or more columns?