Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0VAF-005yFs-Jc for pgsql-general@arkaria.postgresql.org; Tue, 07 Nov 2023 23:07:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r0VAE-00DCUP-85 for pgsql-general@arkaria.postgresql.org; Tue, 07 Nov 2023 23:07:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0VAD-00DCUH-S0 for pgsql-general@lists.postgresql.org; Tue, 07 Nov 2023 23:07:17 +0000 Received: from mail.netnea.com ([82.220.26.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0VAA-005FdA-S3 for pgsql-general@postgresql.org; Tue, 07 Nov 2023 23:07:17 +0000 X-Virus-Scanned: Debian amavisd-new at Message-ID: <56d29e71-a049-4c42-b0bb-cef25a79a014@netnea.com> Date: Wed, 8 Nov 2023 00:07:10 +0100 MIME-Version: 1.0 Subject: Re: Cluster for an appliance-type deployment Content-Language: en-US To: Matthias Leisi , pgsql-general@postgresql.org References: <726F3765-858C-4AC0-A7B0-5CB6720E4B37@leisi.net> From: Christian Ramseyer Autocrypt: addr=ramseyer@netnea.com; keydata= xsFNBFheUvsBEACvW2YD3PjqvkRphU9ZSstDf+hTrWa9FNYpr848CWh1BAaK9bSwHTybrhjM W0bXJ7GYWgJoV7wmRl8LXVwUL4f9DwQSaz8UBe3g0YiB46FSPI1xVFX91BSbDJfseFP+zuBc b/cORNXNTS88iQj/fuD6PWBHC7k2DmVsq5nHN5ZSnHmCVsRmmm8UcD2BnGL3hbG2XHo1zn4k ostl7VxIn85vWKcCQYPrtz4AgU9jFMkbbAsramqmizzsPABbbWjs8gthok75y6MnpdNPIA5f OqQPqEcFv4vcw4fFz24gtHtUJ8/KT2hFfyp9ieT0Hmuj6kkVSgAhUkA5IWZXRGcEQGAHYeea h29BOxU+GHLdZ6Hlw/SSPPU5RV8LivRA2yrCKBC4pHzk6NMffxqOwHQwBCfU04miRiRl0mBy WpYPKh9doUE17V+eB3lqNCv2MMKXgd7422+J1HPFywQ+TNqYoSNu91OCwI0dMUWB/KNDYvds AhLLoI/Bnfw2+8MzoWdkNoat7zQPcsa0/UqKnyMlmHc52gcdChep6b5cLiITuA49e5y9NWRI 0ECJOS1g2ShG7bnlU02+1z63AKbBApKKZiFGBPdLQqox2S9ThntHtCPiTLQY81W3BZ9BOLpE fKx9csh4Jrv4giG0+hz3hO8BCB6/bdplRvAQDlMp7bbZjHX/nQARAQABzShDaHJpc3RpYW4g UmFtc2V5ZXIgPHJhbXNleWVyQG5ldG5lYS5jb20+wsF4BBMBAgAiBQJYXlL7AhsDBgsJCAcD AgYVCAIJCgsEFgIDAQIeAQIXgAAKCRDx2zw+5xstNuKCEACm5uC8f3uiS8cdkHm+HFwR7eRR yzYf4hU7fwRPPhDkT74SyIPO0VvfsUsKDmk6mxvuh4apmHqpLrwEnN+16xv8YWMKyGvR5NHi rholH7/ls4hvvib5UnskRJ7aWJJXzjKY4GvZ8Eks1yPb3TJK3O7mWRFdxyQCzJb/3RVToVM4 oZRyzu4kwjkvoRAeO/Cf0N3P+3JYHK+gQvEeXbjFgG6LqpoueiwT9zwlyxccjNnH41nAfdeU SOCL3tJmfju2AwRagCCia1qlkB8Y0Ur52KHB1oBlvbpv0+1rGyvPmlfNe6w8K3qIhZ7wPHau JoqSTU9vOQ6R10brPrgxtyHODYyak5qrUoBtWrTxyyPFFh3mKnpymJcYUYovUkNTvzBmEL3t nEyiQ3GJ8mxuRx+KC0e+/oJGLhCwF20t4wLas9sgFRY2sTKtD+6Zn75QyfQe5drB36Tj+fL0 Ya2Gsuy878dxA+LrBuT7wBEe0KM1tGqCMQlYkiDCpkWkXut7AEbFej65vufRue8ljh353f2z s+2WOP/arIRbjjRsqy6UyDaTy20EBUECqFJhDu92I5m225RyVAIvHbQgQfwWdmd0NrTVovQA 08nsMm0S8P9+IFxEvJMr/MpVVdcCn2FEBK4WiHg8E4NVZCgOJMGfBc4YoPm/8GtPSVpL7SP/ sSKOFlCVvs7BTQRYXlL7ARAAsYUnXwercvckwZXckaX0B/12hLaENuWRYvl1O4XJqUy674qA PEcMtE/nr6tZIGn3pFl1TGaswBqWMu27yN96P/U5qlk/WbQ4S0y5IqePrYmduw5nvrYTF5rB Vh0wWt+Y7NH9iraB6mD4P5hlIRoCDrCQ/IWcz0wkxynJXZdcu32flu41h23e4kxrur0GFW0l ebuAtlqu8Wr4u6JFdTDjUYKBb4rmJGqdq1QpIRWAwCDU48r5G3MNxNWdTwY3K4S8HsYFhr13 g/4gv+ka1JgKLADEC767KYmpuvY1GUYgAnD7Zg0E8d0umVmPnl18tonM5a/6TU85bHehe7We IaGQ3mLXLff1riH/ud/Z+krUyOk+88rFn6QaVF2eL0ZwlWfdTavBTOZjJIYYc/jMEBmHEWbq +yqrVIYMwbtv3xc+I9lcDuS+mHcsNl0nHnniXl8Lb97AHEPPbNmgAwycRy2QwQ8Q3Q3QZ2lW rBtCBJl1+6delXYyFtN7t6SjICSa7fMpN2v1OyCUWo0nINPh+iAuVkXmnXakirSzZ+VaUerV Wu7nj4AIMAYd6pSoYxAo3h/fAabrR/W3sSND0aBzTz0qWCLtafo+TyyFbLxyySKqp0JOBP+X OTdEz2ITQ9MuSwW2Zrh9GYkpfyzZuggSFVdMZUfHNzOyTx8LTu7CNgUqspkAEQEAAcLBXwQY AQIACQUCWF5S+wIbDAAKCRDx2zw+5xstNrxID/4t5vIvdaS+e+84jSBp+uoS9pShnFljIvvP DhLRAmBr+8GpE8qrKeALVvHdxe7yKi/TlitCZKbviASndn6pdXJeLE1XVKivqiwY/zDQxG8P yyr1uEni6yHAojfr8K0ALn5gvnF/cwiWhB6BJhBcXVbfFuag9FRdNfDwn3JRTXoOs0CLONJ8 Wy1Yk8GoDTYklb54Asrk/RAnYJRr7EfwuUQO5FsDDkfvBdhgcXxzXcT9KcczFjcpjYPW+xN9 SzSvXUplX5vfzucjKZHntw1Z44Y36MrBxXHE6qKmJY2qoN2A6O+SciJTH5hI3QRnu+ANauKU 2xO/we38A8WX1uC/nOVtPoyigajCP7gXgnO03ReOlIMCHe7J3WhUbMWirocu4hNlLxTZghAr rd71AtHil8KNZuQh2OrrMmsfMNKpKGVpHhQsijJEHNuPZaiOluAcCRjHNbCRB490PyrDo/z4 YUo5j++7eP/pgLg7aEKv2wsaWA26G/nDofl0i/8gFSRzoaPPYUSPJWT6LSGNGPHNEtE8QI1n LBZscSK70I0EvU06LclCRZ40zm7uFP+Y1AV5xQVZL1VyOGC3Is0YG4UJgqNYDC8SJ/55L/Xd ZnNLbA+w0moScLLT/WfrCvsqpLQ8YIlsuxxhr2X3M4xtytgUgmvsC4qVcxxg80MnsM6CGh95 tA== In-Reply-To: <726F3765-858C-4AC0-A7B0-5CB6720E4B37@leisi.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 06.11.23 20:26, Matthias Leisi wrote: > Dear all, > > I’m reasonably experienced with Postgres with simple (single, „rebuild > and restore“) requirements, but would need some hints on what to look > for in a more complex situation - deploying Postgres as the backend for > a (virtual) appliance. > > This appliance can scale horizontally from a single to dozens of VMs > (theoretically more, but most installations are small-ish). It is > feasible to configure VMs for particular purposes (eg „you are [also] a > DB node“), but basically all instances will/should be able to perform > their main tasks besides (also) being a DB node. As the VMs may be > installed in very different environments, network-based solutions are > less feasible and we would prefer a DB-level solution. We assume that > for most cases, primary/stand-by configurations would be sufficient in > terms of availability / latency / throughput. > > We must also assume that there is no person who would be able to touch > things if an error occurs. Data consistency and (as much as possible) > automated recovery from error situations („VM down“, „network lost“, …) > are therefor more important than „n nines". We can assume that the VMs > can talk to each other over TCP (eg using SSH tunnels, direct Postgres > connection, or some other suitable protocol). Scripting „around“ the > database is available to initialize instances and for similar tasks. > > Would Postgres’ own log-shipping (file-based + streaming replication, > possibly with remote_write) be sufficient for such a set of requirements? > > What aspects would you consider important for such a scenario? The replication that ships with Postgres gives you one writeable primary server and a number of standbys, but it has no tools to automatically discover or recover from failure. From https://www.postgresql.org/docs/current/warm-standby-failover.html: --- PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server. Many such tools exist and are well integrated with the operating system facilities required for successful failover, such as IP address migration. Once failover to the standby occurs, there is only a single server in operation. This is known as a degenerate state. The former standby is now the primary, but the former primary is down and might stay down. To return to normal operation, a standby server must be recreated, either on the former primary system when it comes up, or on a third, possibly new, system. The pg_rewind utility can be used to speed up this process on large clusters. Once complete, the primary and standby can be considered to have switched roles. Some people choose to use a third server to provide backup for the new primary until the new standby server is recreated, though clearly this complicates the system configuration and operational processes. --- So you need to add additional parts from the ecosystem to detect failure, handle failover, potentially move an IP address with the Primary etc. Popular tools are repmgr, Patroni, CloudNativePG, BDR, pacemaker+corosync and endless others. They will address many of your requirements, but still some work and understanding is required to make them fully unattended, as well as have them expand dynamically with new replicas if an appliance is added. Postgres is an amazing product and I like to use it for almost everything, but in this scenario we are of course making our live hard with ACID compliance. In NoSQL/"eventual consistency" land, there are products that are a lot friendlier to a setup like this - stuff like Cassandra, etcd, CouchDb comes to mind. I'd compare the pros and cons of such alternatives, the big con of course being a lot less consistency and durability guarantees - the question is whether the application needs it. But enough about non-Postgres topics on this list :) To go with Postgres, on a hunch I'd try Patroni first: it does a lot of the advanced failover stuff, has a great track record, and supposedly runs on BSD: https://openports.pl/path/databases/patroni Cheers Christian -- Christian Ramseyer, netnea ag Network Management. Security. OpenSource. https://www.netnea.com Phone: +41 79 644 77 64