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 1qrHvi-003XKZ-1z for pgsql-general@arkaria.postgresql.org; Fri, 13 Oct 2023 13:10:14 +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 1qrHvf-000SVG-4f for pgsql-general@arkaria.postgresql.org; Fri, 13 Oct 2023 13:10:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrHve-000SUL-Oq for pgsql-general@lists.postgresql.org; Fri, 13 Oct 2023 13:10:11 +0000 Received: from mail1.dalibo.net ([51.159.93.128] helo=mail.dalibo.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrHvc-000IKU-QQ for pgsql-general@lists.postgresql.org; Fri, 13 Oct 2023 13:10:10 +0000 Received: from karst (82-65-23-130.subs.proxad.net [82.65.23.130]) by mail.dalibo.com (Postfix) with ESMTPSA id C46421F93F; Fri, 13 Oct 2023 15:10:06 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1697202606; bh=KRnkJa9bVxByyz2bPBBa3XGEQ6o7theFZ2LTzWEtCpc=; h=Date:From:To:Cc:Subject:In-Reply-To:References:From; b=fDoO9amx12CI6hHCYlSWAyvGgNP+IMn3hBtgqCGjZJhuWMO66WQmuhpYJZ9bvFRZk 9xpxoOIGp9GccwEfrprT8gS2hoirpORh44dYUSQojvbCKMi73zd4pwb73H/i5KMxGl 4pHjNPjhsDNIRrDGb0Os4nOtFCw+proyvXW4x+QA= Date: Fri, 13 Oct 2023 15:10:06 +0200 From: Jehan-Guillaume de Rorthais To: Jason Grammenos Cc: "pgsql-general@lists.postgresql.org" Subject: Re: Postgresql HA cluster Message-ID: <20231013151006.1348c722@karst> In-Reply-To: References: <12601231697122729@mail.yandex.ru> Organization: Dalibo MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 13 Oct 2023 12:02:53 +0000 Jason Grammenos wrote: > Thank you for the feedback, >=20 > I have used pacemaker for other purposes previously so am a little famili= ar > with it.=20 So you might be familiar with shared-storage cluster, that are the simpler = one you could deploy (baring you have a reliable HA storage available). But it's not a multi-primary cluster. > It appears that in this scenario pacemaker is being used to manage a > floating ip as well as deal with split brain scenarios. There's also two different resource agents dealing with PostgreSQL itself: pgsql and PAF. Both handle multi-status differently from the administration point of view. > What isn=E2=80=99t clear is how effective master-> master replication is = being accomplished. There's no master-master in PostgreSQL core. There's few external solutions= out there though, but double check you real needs, the real primary-standby capacity to answer you needs, and the various constraints M-M imply before rushing there. > Postgresql streaming replication to the best of my limited knowledge only > replicates in one direction, from the active to the standby servers. The > issue this presents to me is that once you failover from the active to the > standby (or one of the standby=E2=80=99s depending on how many you have) = none of the > data written on the standby is replicated back to the formerly active ser= ver. It depend if this is a "controlled failover" (aka. "switchover") or a real failover triggered by some failure. If this is a controlled failover, you c= an hook back your old primary as a standby with no trouble. PAF even handle th= is for you. Moreover, even with a failure scenario, there's some solutions around to qu= ickly fix your old primary data and get it back in production quickly as a standby (pg_rewind, PITR/pgbackrest, etc). You just have to plan for failure and write you procedures accordingly to g= et the cluster back on feet quickly after a failover. > Let us say that I have only 2 postgresql servers (absolute minimum number) > and I want to patch server A. Ideally, I would use a load balancer (or ot= her > failover mechanism like pacemaker) and repoint the floating ip to server = B. > Now traffic would =E2=80=9Cdrain=E2=80=9D down off server A, and slowly (= or rapidly) move to > B. During the move some clients would still be writing to A and some clie= nts > would be writing to B. This doesn't exist as PostgreSQL has no multi-primary solution in core. You= can do rolling upgrade, but you'll have to pause the production during the switchover between the primary and the standby. > In the above scenario, I do not understand how streaming replication would > handle the part of the scenario when there are clients writing to A and B. It will not. > It would seem that something like `pgpool-ii` or `pgEdge` would be requir= ed, but > with my limited knowledge it is unclear if or which would be appropriate. External multi-primary solution exists, pgpool-II, Bucardo, BDR, etc. But you'll have to ask and evaluate these thrid party solutions yourself.=20 But really, double check first why a simple primary-standby architecture do= esn't meet your needs. The simpler the architecture is, the better. Even from the application point of view. Regards,