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 1ojlkV-0004Dl-53 for pgsql-admin@arkaria.postgresql.org; Sat, 15 Oct 2022 18:19:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ojlkT-000821-W6 for pgsql-admin@arkaria.postgresql.org; Sat, 15 Oct 2022 18:19:01 +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 1ojlkT-00081r-LO for pgsql-admin@lists.postgresql.org; Sat, 15 Oct 2022 18:19:01 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ojlkQ-0005Wh-O3 for pgsql-admin@lists.postgresql.org; Sat, 15 Oct 2022 18:19:00 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 6DF9F381017; Sat, 15 Oct 2022 20:18:56 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id GybAaLbUgtE4; Sat, 15 Oct 2022 20:18:56 +0200 (CEST) Received: from [192.168.0.178] (ip-109-42-179-40.web.vodafone.de [109.42.179.40]) by rs.plausibolo.de (Postfix) with ESMTPSA id 05738380B05; Sat, 15 Oct 2022 20:18:55 +0200 (CEST) Message-ID: Date: Sat, 15 Oct 2022 20:18:55 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.3.0 Subject: Re: Open source HA setup for postgres To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------NlZke9whXLlFaqgyhhwNhb51" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------NlZke9whXLlFaqgyhhwNhb51 Content-Type: multipart/mixed; boundary="------------bSNma5BvYrLDWM076jDoalL0"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: Open source HA setup for postgres References: In-Reply-To: --------------bSNma5BvYrLDWM076jDoalL0 Content-Type: multipart/alternative; boundary="------------PilrYiGyEf0Q03mNCtbaeynn" --------------PilrYiGyEf0Q03mNCtbaeynn Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 SGkgU2FtLA0KDQpUaGVyZSBpcyBhIHdob2xlIGNoYXB0ZXIgYWJvdXQgdGhpcyB0b3BpYyBp biB0aGUgb2ZmaWNpYWwgZG9jdW1lbnRhdGlvbg0KDQpodHRwczovL3d3dy5wb3N0Z3Jlc3Fs Lm9yZy9kb2NzL2N1cnJlbnQvaGlnaC1hdmFpbGFiaWxpdHkuaHRtbA0KDQpQbHVzLCB0aGVy ZSBhcmUgcHJvZHVjdHMgbGlrZSBwZ19hdXRvX2ZhaWxvdmVyIGJ5IENpdHVzIChNaWNyb3Nv ZnQpIGFuZCANCnRoZSB0ZW1wbGF0ZSBQYXRyb25pLg0KDQpSZWdhcmRzLA0KDQpIb2xnZXIN Cg0KQW0gMTQuMTAuMjIgdW0gMTg6MzMgc2NocmllYiBUaGFybWFyYWphaCwgU2FtOg0KPg0K PiBIaSwNCj4NCj4gV2UgYXJlIHdvbmRlcmluZyB3aGF0IGlzIG9wZW4tc291cmNlIHRvb2xz IGJlaW5nIHVzZWQgdG8gc2V0IHVwIHRoZSANCj4gSEEuIFdlIGFyZSBhbHNvIGxvb2tpbmcg Zm9yIGEgd2F5IHRvIHBhc3MgdGhlIHJlYWRzIHRvIGFsbCB0aGUgSEEgDQo+IG5vZGVzIGFu ZCB3cml0ZXMgdG8gdGhlIHByaW1hcnkuIMKgT25lIG9mIHRoZSBwcmVzc2luZyBjcml0ZXJp YSBpcyB0aGUgDQo+IEhBIGFsc28gc2hvdWxkIGJlIGFibGUgdG8gaGFuZGxlIHRoZSBub2Rl IGJlaW5nIHRha2VuIGRvd24gZHVyaW5nIA0KPiBwYXRjaGluZyBhbmQgc2hvdWxkIGJlIGFi bGUgdG8gZmFpbCBiYWNrIG9yIHJlY292ZXIgc2VhbWxlc3NseSB3aXRob3V0IA0KPiBhbnkg bWFudWFsIGludGVydmVudGlvbi4NCj4NCj4gVGhpcyBpcyB0aGUgZmlyc3QgdGltZSB3ZSBh cmUgZW1iYXJraW5nIG9uIEhBLCBzbyB3b3VsZCBsaWtlIHNvbWUgDQo+IGFkdmljZSBhbmQg Y2F2ZWF0cyB0byBhdm9pZC4NCj4NCj4gVGhhbngNCj4NCj4gU2FtDQo+DQotLSANCkhvbGdl ciBKYWtvYnMsIEJlcmdpc2NoIEdsYWRiYWNoLCBUZWwuICs0OS0xNzgtOTc1OTAxMg0KDQo= --------------PilrYiGyEf0Q03mNCtbaeynn Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi Sam,

There is a whole chapter about this topic in the official documentation

https://www.postgresql.org/docs/= current/high-availability.html

Plus, there are products like pg_auto_failover by Citus (Microsoft) and the template Patroni.

Regards,

Holger

Am 14.10.22 um 18:33 schrieb Tharmarajah, Sam:

Hi,

=C2=A0

We are wondering what is open-source tools= being used to set up the HA. We are also looking for a way to pass the reads to all the HA nodes and writes to the primary. =C2=A0One of the pressing criteria is the HA also should be abl= e to handle the node being taken down during patching and should be able to fail back or recover seamlessly without any manual intervention.

=C2=A0

This is the first time we are embarking on= HA, so would like some advice and caveats to avoid.<= /p>

=C2=A0

Thanx

Sam

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------PilrYiGyEf0Q03mNCtbaeynn-- --------------bSNma5BvYrLDWM076jDoalL0-- --------------NlZke9whXLlFaqgyhhwNhb51 Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY0r5jwUDAAAAAAAKCRCKV5yTsxlhupUM AKDCSw/fFyFi1I1R01TDOmAgRO1K1wCeP+9CQSIKWGyQMDFEqPexvWL1LJk= =ZhbA -----END PGP SIGNATURE----- --------------NlZke9whXLlFaqgyhhwNhb51--