Am 22.05.26 um 15:18 schrieb Michael Grimm:
I have never run a postgres server before, but I do run a mariadb
galera cluster [1] for storing emails by an IMAP server [2].
Distribution and failover is dealt by haproxy [3]. This mail server
setup has to deal with less than 250 mails a day, a database size of
under 6 GB, and a handful of users. You see, that this system is
bored to death ;-)
I have read a lot about replication in order to find something
comparable to a galera cluster with its multi master capabilities.
Now, I have learned that multi master can only be achieved by using
third party plugins. Not all of those are available for my FreeBSD
systems, though.
As a newbie I do currently tend to use logical replication with
mutual publish and subscribe instead, either by pglogical2 or "self-
made". I am in the -probably naive- impression that this could work.
As before, failover will be handled by haproxy by simply directing
read/write access to another postgres node available. When a failed
node will become online again, logical replication should enable
this node to recover, right?
Here are my questions:
#) Is this feasible or nonsense?
#) What would an alternatives for FreeBSD be (pgpool-II, repmgr, …)?
For such a system I don't see the need for a multi-master setup (neither
with Postgres nor with MariaDB).
That's typically used if you need to spread the write load across nodes,
quite frequently because access is geographically distributed and the
latency is not acceptable.
For high availability you don't need multi-master. A standard
streaming replication with a failover mechanism is completely enough
in my opinion.
