Thread: [GENERAL] Postgres HA

Hi,

On Wed, Feb 22, 2017 at 10:58:10PM +0000, Dylan Luong wrote:
>
> I am looking at options to improve our high availability.

I suspect the central question you have to answer is, "What do you
mean by 'improve'?"

Do you want to increase the ability to retrieve data?  Decrease the
potential for data loss?  Shorten the recovery time to read/write
availability?  And so on.  The answers for these different questions
will determine which trade-off you need to make.

And rest assured, there is abolutely no solution in the world -- not
even a really expensive commercial one -- that requires no trades.
Distributing data reliably with ACID semantics and no data loss or
corruption or loss in write throughput is not possible, at least
today.  You have to pick which poison you want :)

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 2/22/2017 2:58 PM, Dylan Luong wrote:
> For PostgreSQL High Availability, we currently have setup a
> Master/Slave across two datacenters using PostgreSQL (WAL) streaming
> replication.

have you considered the ramifications of network problems between these
two datacenters?    with a master/slave cluster, you need to avoid the
'split  brain' scenario where both servers think they are master because
they can't reach the other.




--
john r pierce, recycling bits in santa cruz

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

Julyanto Sutandang <julyanto@equnix.id> wrote:
> Talking about High Availability, we should understand the basic concept of HA, it is avoiding SPOF (Single Point of
Failure).When we use a Loadbalancer (LTM) and that load balancer is single, then you may get HA only for the PostgreSQL
butthere are another single point of failure, it is the LTM it self. In overall that  topology is not HA.  
>
> The best configuration for HA i know is using Linux-HA to watch between 2 servers and doing failover VIP (Virtual IP)
whenMaster is down or out of service. The best configuration for HA, Servers should be on the same site and uses direct
cableconnection to ensure dedicated private bandwidth and there are no Single Point of Failure.  
> LinuxHA or pacemaker or corosync will do the Virtual IP swing over from master host to slave host and promote the
replicadatabase in slave host become master.  
>
> There is no single point of failure.


I'll agree with most of this, especially that avoiding SPOF is the goal.

However, I will point out that if you put both servers in the same site that you've created a SPOF. If you lose power
atthat site, or construction workers takes out the network cable to the building, or any other thing that can happen to
takethe whole site down, any of that will cause total failure. 

If you really want to avoid that, then the 2 servers need to sit multiple miles/kilometers apart and be served by a
dedicatedconnection or else 2 different network providers (I've heard differing opinions on the best way) that is 100Mb
orhigher to adequately deal with the various replication issues that must be addressed. If the load is high enough, you
mayneed multiple lines to be bonded or else even Gb. 

Of course, the OP may not need that level of HA, but it is something that should be asked and answered by him and his
organization.

It's something we deal with for our larger customers. We currently solve the replication with DRBD (for the DB) and
csync2(for the application code and logs). We are slowly considering the question if we'd be better off abandoning DRBD
andusing Pg's builtin replication, but the jury is still out on that as we haven't had enough time to fully figure it
out.We'd considered ZFS replication for a while, but found ZFS to be too slow for the DB despite it's other useful
featureslike replication (at least for the hardware that we had). 

Personally, I find HA easy to understand but hard to implement well -- especially without a large budget. :)

HTH,
Kevin