Postgresql replication blows

Yup, that’s right. I said it. Postgresql replication options blow. Hardcore. To summarize the options:

pgreplication – Too old, based off PostgreSQL-6.4.2, did see a rumor on a mailing list about reviving it for 8.x

pgpool – Synchronous replication (good for data integrity, bad for performance), load balances select queries, very limited (and insecure) auth options

Slony-I – Asynchronous replication (good performance, slight data integrity issue), no load balancing, setup with existing db a royal PITA.

I’ve been ‘tasked’ with getting failover replication setup at work for a few critical (such as bugzilla) postgres databases. The _only_ option I have at this point is Slony-I which, as I mentioned above, is a royal PITA to integrate into an established database.

For one thing, Slony introduces it’s own syntax for control. Not so bad, but there are some conflicting docs out there which cost me some config time. Slony does also require all tables you create to either have a primary key, or an index marked as ‘unique, not null’. Again, this seems like it should be pretty common place but, at least in our version of bz, there were several tables without. According to the docs, you can have Slony create a primary key if neither of the other two options are available. What that doesn’t mention is that you then have to specify ‘key = SERIAL’ in your table definition.

Slony has also had some stability issues in the past with their replication processes, but most of those have been worked out with the current stable version (1.1.5) and the next version (1.2.0) has gotten even more love.

I will say this for it, my initial tests are showing it being pretty damn fast with intial master -> slave sync.

In contrast, setting up MySQL replication is dead simple and requires no third-party scripts. *sigh*

2 thoughts on “Postgresql replication blows

  1. Hi, can you explani, or write tutorial how to setup a slony-I replication system on existing database. for example Two pc 1-Ubuntu, 2-Win XP or 1-Ubuntu -> 2-Ubuntu. and to do that from “clean” instal of database.

  2. I couldn’t possibly agree with you more. I’d love to migrate more of our databases from MySQL to PostgreSQL, but replication IS THE ONLY BARRIER.

    The community really needs to embrace just how important this is to success and hit this one out of the park.

    It needs to be:

    1. Easy to use
    2. Stable
    3. Easy to use
    4. Support the creation of hot slaves without much work. (think old load data from master) I KNOW this is hard, but not half as hard as 5m ppl who use the database manually setting up replication over and over and over and over and over. It seems like I spent half my career babysitting a new replicant while my SQL Server admin colleage is at lunch while hers finishes.
    5. Easy to use
    6. Flexible, but not overly so. Perhaps just slighly smarter than MySQL replication would do. Were not looking to solve world hunger. Just give us options to replicate whole databases, select tables, etc in sets or individually.
    7. And if it’s not easy to use then start over.

    I LOVE PostgreSQL and the wonderful community. It’s just a shame I can’t use it more.

Leave a Reply