Re: master/master replication with load balancer in front

El 24/08/15 a las 15:41, Florin Andrei escribió:
> On 2015-08-23 06:56, Martín Marqués wrote:
>> El 21/08/15 a las 20:45, Florin Andrei escribió:
>>>
>>> The single instance scheme is not very reliable. I need to build a new
>>> DB backend. I'll set up Postgres 9.4. Ideally, I'd like to setup 2
>>> instances, each instance placed in a different availability zone.
>>> Master/master replication. I'll put a load balancer (ELB) in front of
>>> both instances.
>>
>> It's not clear if the main goal is reliability (or availability), or to
>> balance writes.
>>
>> If you are looking for HA, single master with multiple standbys is your
>> best bet (you can put standbys on different zones).
>
> This is for reliability / availability.
>
> The thing is, if I have a single master and an AZ fails, I still have to
> make manual changes to switch to the healthy AZ - and, until then,
> updates would fail. Master/master, in theory, should absorb single-AZ
> failures without needing any manual intervention.

When updates fail the application should have the code to retry
executing those statements or return the appropriate errors to the user
so further actions can be carried on.

And in any case, you will need to adjust you application or pooling
system to send statements to a node that's up. Multi-master solutions
won't magically do that.

Maybe you should take a look at repmgr and particularly the automatic
failover feature:

https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.rst

>> You can also look at BDR and have masters geographically distributed,
>> but I'd strongly suggest you look at the link
>> http://bdr-project.org/docs/stable/weak-coupled-multimaster.html, and
>> keep in mind that with multi-master systems you will be more prone to
>> data modification conflicts.
>
> I get the sense that BDR is not 100% ready for prime time. Is that
> accurate?

BDR is ready for prime time, but if it will work for you depends on your
needs. BDR aims at *eventually* consistent multi-masters system with
geographically distributed nodes, so DML statements on one node will not
be slowed down because the data has to be replicated to other distant nodes.

There's still lots of ground where to improve, but isn't that also true
for any other system?

The important thing is if it provides what you need.

I suggest you run some tests and see if it fits your requirements.

Regards,

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services