From: | Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com> |
---|---|
To: | Jason Grammenos <jason(dot)grammenos(at)agilitypr(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Postgresql HA cluster |
Date: | 2023-10-13 13:10:06 |
Message-ID: | 20231013151006.1348c722@karst |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, 13 Oct 2023 12:02:53 +0000
Jason Grammenos <jason(dot)grammenos(at)agilitypr(dot)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,
From | Date | Subject | |
---|---|---|---|
Next Message | Luca Ferrari | 2023-10-13 13:51:53 | Re: psql \du no more showing "member of" column |
Previous Message | Ian Lawrence Barwick | 2023-10-13 13:06:09 | Re: psql \du no more showing "member of" column |