Re: master/master replication with load balancer in front

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.

> 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?

How about Bucardo?

https://bucardo.org/wiki/Bucardo

>> If I were to drop the master/master requirement and just do
>> master/slave, sending updates to one node, and doing all analytics on
>> the other node, what would be the best replication technique in this
>> case?
>
> Stream replication seems the one which might fit better. Trigger based
> replication would choke on large bulk loads (unless you split them up
> into smaller pieces)

It's all bulk uploads for now. It's a Python script that wakes up once
in a while and dumps more data into the DB. Size varies but it can be
big.

--
Florin Andrei
http://florin.myip.org/