Re: Multi Master Replication - Mailing list pgsql-general

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.