From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Jason Grammenos <jason.grammenos@agilitypr.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Postgresql HA cluster
Date: Fri, 13 Oct 2023 15:10:06 +0200
Message-ID: <20231013151006.1348c722@karst> (raw)
In-Reply-To: <YQBPR0101MB628268D625DC924D614E0C03E4D2A@YQBPR0101MB6282.CANPRD01.PROD.OUTLOOK.COM>
References: <YQBPR0101MB6282FB50CD4ED470B98DEF20E4CDA@YQBPR0101MB6282.CANPRD01.PROD.OUTLOOK.COM>
<YQXPR01MB62865246E684BD30645B68B3E4CCA@YQXPR01MB6286.CANPRD01.PROD.OUTLOOK.COM>
<12601231697122729@mail.yandex.ru>
<YQBPR0101MB628268D625DC924D614E0C03E4D2A@YQBPR0101MB6282.CANPRD01.PROD.OUTLOOK.COM>
On Fri, 13 Oct 2023 12:02:53 +0000
Jason Grammenos <jason.grammenos@agilitypr.com> wrote:
> Thank you for the feedback,
>
> I have used pacemaker for other purposes previously so am a little familiar
> with it.
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’t 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’s depending on how many you have) none of the
> data written on the standby is replicated back to the formerly active server.
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 can
hook back your old primary as a standby with no trouble. PAF even handle this
for you.
Moreover, even with a failure scenario, there's some solutions around to quickly
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 get
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 other
> failover mechanism like pacemaker) and repoint the floating ip to server B.
> Now traffic would “drain” down off server A, and slowly (or rapidly) move to
> B. During the move some clients would still be writing to A and some clients
> 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 required, 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.
But really, double check first why a simple primary-standby architecture doesn't
meet your needs. The simpler the architecture is, the better. Even from the
application point of view.
Regards,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: jgdr@dalibo.com, jason.grammenos@agilitypr.com
Subject: Re: Postgresql HA cluster
In-Reply-To: <20231013151006.1348c722@karst>
References: <YQBPR0101MB6282FB50CD4ED470B98DEF20E4CDA@YQBPR0101MB6282.CANPRD01.PROD.OUTLOOK.COM> <YQXPR01MB62865246E684BD30645B68B3E4CCA@YQXPR01MB6286.CANPRD01.PROD.OUTLOOK.COM> <12601231697122729@mail.yandex.ru> <YQBPR0101MB628268D625DC924D614E0C03E4D2A@YQBPR0101MB6282.CANPRD01.PROD.OUTLOOK.COM> <20231013151006.1348c722@karst>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox