Re: [GENERAL] Postgres HA

On Wed, 22 Feb 2017 22:58:10 +0000
Dylan Luong <Dylan.Luong@unisa.edu.au> wrote:

> Hi
>
> I am a DBA at the University of South Australia. For PostgreSQL High
> Availability, we currently have setup a Master/Slave across two datacenters
> using PostgreSQL (WAL) streaming replication. We use an LTM (load balancer)
> server that sits between the application servers and the PostgreSQL server
> that directs connections to the Master (and the Slave if failover occurs). We
> also have watchdog processes on the PostgreSQL servers that polls the LTM to
> determine who is Master and perform automatic failover if required.

And how do you deal with split brain ? Fencing? Network partition? What if the
network fail on the master side for 5 minutes? Will the LTM go back to the old
master as soon as the watchdog pool it again?

> I am looking at options to improve our high availability. I would like to
> know how other organizations in different industries (other than education)
> setup High Availability on their PostgreSQL environments. What  tools do you
> use. Are they commercial licensed products? How is the architecture setup and
> how do you do recovery of new slave. Your information is greatly appreciated.

We use Pacemaker with the PAF[1] resource agent. Pacemaker takes great care to
avoid split brain using fencing. It mostly supports local cluster, but it
supports multi-site clusters as well thanks to a layer called "Cluster Ticket
Registry"[2].

HA is a complex subject, it requires some time to get familiar with it. Good
luck :)

[1] http://dalibo.github.io/PAF/
[2] http://clusterlabs.org/doc/en-US/Pacemaker/1.1/html/Pacemaker_Explained/ch15.html