Re: Multi Master Replication

On Wed, Dec 18, 2013 at 1:31 AM, itishree sukla <itishree(dot)sukla(at)gmail(dot)com>wrote:

> Hi all,
>
> I need suggestion about setting up multi master replication between two
> postgresql server place two different geographical area. As i know using
> some third party tool like Bucardo,RubyRep it can be achievable, not sue
> which is the good one to use. If any one can provide me some online
> documentation links, it will help me as well.
>

First suggestion: don't. Multi-master replication almost never is a win.
I tell customers this and they don't like to hear it but it is true.

Second suggestion: If you absolutely must, go with Bucardo. Rubyrep might
work but it is db-agnostic and this raises additional complexity concerns.

The major reasons why I argue against multi-master replication are:

1. With async replication you have the problem of conflict resolution. In
some (rare) cases this is avoidable, but in most cases it is not. You have
to deal with the fact that two different people in two different locations
may update the same row, and you have to decide how to handle this. Once
you have a conflict, every option you have to resolve the conflict results
in data loss. There are rare cases where this is the right solution.

2. With sync replication, you have coordination problems and therefore it
is never (at least IME) a win compared to master-slave replication since
all writes must occur in the same order in the set, or you need global
sequences, or such. So I would say that something like PGPool for
multi-master replication is just to be avoided generally (however for load
balancing in master/slave it can be a win). You will never get better read
or write throughput, or a more robust system than you will with
master/slave replication in a synchronous environment. Keep it simple.

As others have mentioned your best bet here is pl/proxy. I would go a
little further however and suggest that you can separate storage dbs from
proxy db's and thus create the appearance of multi-master over a
master-slave setup. If you are going to go this route however, I don't
know whether Postgres-XC would be worth looking into.

The key here though is that design effort is important. If you carefully
design your federated storage, then you should be good. However this leads
to lots of problems and you need to think them through.

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml