Postgresql database has come a long way since its initial release. One of the feature that can come in very handy in handling database that are located across geographical region is multi-master replication. The idea is to provide localised access to the database for each sites and yet still maintain a single view of the database. There are two solution that can get this done rather painlessly.
Option 1: BDR
This is a new replication solution by second quadrant. Still in beta but the ease of installation is very promising. The quirks:
a. Some of the database administrative functions are rendered inoperative.
b. A global sequence need to be explicitly set during table creation. Otherwise, would have a lot of write failure as the sequences are being replicated asynchronously.
c. Need a patched version of Postgresql 9.4.
Option 2: Bucardo
This solution has been around for quite sometime and is quite stable. The latest version which is 5.4 added support for multi-masters. The quirks:
a. DDL is not replicated. Any update to DDL need to be manually handled across all nodes.
b. Sequences is not replicated. A good strategy is to increment the sequence by the number of nodes installed.
Personally I prefer BDR solution as DDL and sequences replication can make deployment so much easier.
