Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ9Qw-0007TI-35 for pgsql-general@arkaria.postgresql.org; Thu, 09 Feb 2023 16:06:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQ9Qu-0008Pb-W2 for pgsql-general@arkaria.postgresql.org; Thu, 09 Feb 2023 16:06:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ9Qs-0008PR-Uv for pgsql-general@lists.postgresql.org; Thu, 09 Feb 2023 16:06:00 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ9Qq-0003np-0P for pgsql-general@lists.postgresql.org; Thu, 09 Feb 2023 16:05:57 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 934645C0308; Thu, 9 Feb 2023 11:05:53 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Thu, 09 Feb 2023 11:05:53 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1675958753; x= 1676045153; bh=DmF2fPcbzYp5VVL+9JbVstM2Nn2hYQE2KZyOQdvd0oc=; b=k S/muasJFOSnmEsbCRd+LNKDWlyxj16JFUPL9TveZpj3kxWYahuLSizbyG2EZ5LaR hMT1ka0ydp1nfg0pAv9yCVO3aKaBwvlqBmI1eWaM7p+xld5NnRCXi63fRVaMoUAk cQIxzTUC/Xje8LVDlrhNXeOx+T1YAzSzYbMtCkD79tQqhvEe+tO42Ram/6sD0USr Kaa5v0Fi8m7hIi98zE1TGDUdCh9CXM5MglaeOP4a7PN34vFP5xKC661Fswsqr1tk sA0znN4nd2jIalXUSXVcHY58Vy2A1BHqod+S242/AgktX/Mt6OqQxwFan+Cg49ad vkhGRD4wYlTAnG1Eetp1A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:date:feedback-id:feedback-id:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1675958753; x=1676045153; bh=D mF2fPcbzYp5VVL+9JbVstM2Nn2hYQE2KZyOQdvd0oc=; b=diinO7k3y9Y02wWcV a30RJaUuYYUWn5Wosr3xD5ndnvcF4xHmJijOq9iQ+cQcc0kmpWHPDskzz0LcjurN Ji4MC8zTthdGmpIbOY6jvvNbsL//QNsOR9mdS4QOf3vDUB/NdZyGxYUiHCoS74Wq I4X9r8IgBzOars7aOm0hGgcc/6v2UeurWyTFcNXmec1+0z+Rl5F1KARXZAYzk18N DHXMBcWW3mV11BMJE4p3I0p8bbQpY7GTw5baT+4OrSHKqTlS3Q0txCIlzVM9VSk3 ly57u9Yospbm8+be3FdxKpdMaStjb+sakWhc1W4p1piM8raLBZkX6pyINdT0Rnz3 qnKEA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudehfedgkeefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthejredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeevvdekiefhudfhhfdtkeelgeffudeigfetjeegfffg hfetfeelledugfelleegveenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 9 Feb 2023 11:05:52 -0500 (EST) Message-ID: Date: Thu, 9 Feb 2023 08:05:52 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 Subject: Re: Using PostgreSQL for service discovery and health-check To: Dominique Devienne , pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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