PostgreSQL does not offer any in built support for asynchronous multi-master replication. According to this official page, some providers implemented this functionality, so I decided to have a go with the only two that advertise lazy multi-master replication: rubyrep and bucardo.
Rubyrep offers two versions: one using ruby and another using ruby and java. It is adverstised as being “really easy to use and database independent”, which are undoubtedly some cool features 🙂 I started with the most simple one (ruby only) and sadly I was unable to run it under linux 😦
I followed the instructions on this page, and in fact the installation is really easy, but when I try to scan the databases for differences, with this command:
rubyrep scan -c myrubyrep.conf
I kept running into this exception (and various warnings):
Exception caught: PG::ConnectionBad: connection is closed: SELECT tablename
WHERE schemaname IN ('"$user"','public')
This happened, even if I reduce the conditions to the simplest: two databases in the same server, and the same user!!
After reading a bit, I realized there are some issues with the versions of Ruby (and other tools) and apparently the project has been inactive for a while (discontinued?). It is a shame because it looks really simple, but I don’t think it is really worth it to put a lot of effort on a software that has been discontinued 😦
Bucardo is perl-based and it is an absolute nightmare to install. I followed the instructions on this page, and as they warn the install script does not work, because it tries to create a “bucardo” user on the fly, but does not ask you to set a password! 😦
After fiddling with it for a while, I found a website that suggests that you edit the script (bucardo_ctl) and edit the default password.
Some things that I would recommend to get bucardo running:
- set “permissive” authentications for the bucardo on pg_hba.conf
- add a system bucardo user, and set the credentials on .pgpass
After that, you can add the databases that you want to syhcnronize with:
bucardo_ctl --dbhost=192.168.1.11 --dbpass=password add database test
Note that I was only able to run bucardo_ctl, passing the entire row of arguments (host, password).
Next step is to add the sync; bucardo supports three types of sync algorithms: fullcopy, pushdelta and swap.
bucardo_ctl add sync sync-delta source=test targetdb=test2 type=pushdelta tables=distributors
After that, you can start the bucardo daemon, and wait for the synchronization:
bucardo_ctl --dbhost=192.168.1.11 --dbpass=password start
The only method that supports circular (multi-master) replication is “swap”, and it has some conflict detection/resolution. Sadly for me, it works exactly with two databases 😦
My conclusion about the add-ons for supporting lazy replication in PosgreSQL is that they are both hard to install/configure and Bucardo (which seems a more mature and lively project) is still limited to two masters. I did not attempt more complicated scenarios, such as running databases at different hosts.