Re: Postgresql HA cluster - Mailing list pgsql-general

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,