Replication with PostgreSQL

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
FROM pg_tables
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.

Advertisements

4 thoughts on “Replication with PostgreSQL

  1. Regarding that Exception caught: PG::ConnectionBad error, removing all the other versions of the gem ‘activerecord’ solved the issue for me. I tried this because the stack trace was pointing that gem, and it was not actually the one asked by rubyrep (it asks for version 3.0.5). So what I did is,

    baris@host:~/Workspace/redmine> gem list | grep active
    activemodel (4.1.6, 3.2.19, 3.0.5)
    activerecord (4.1.6, 3.2.19, 3.0.5)
    activeresource (3.2.19)

    baris@host:~/Workspace/redmine> sudo gem uninstall activerecord -v 4.1.6
    baris@host:~/Workspace/redmine> gem uninstall activerecord -v 3.2.19

    and I ignored the dependency problem with ‘rails’ gem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s