I spent some time recently looking at replicating a live, busy Postgres database to another box as part of a disaster recovery plan. From the outset I thought it was either going to be pretty simple or pretty complicated. It ended up being neither.

A bit of background. I’ve spent nigh on 11 years working with MySQL and have never really *needed* to work with another database system (except for SQL Server but that is another story entirely). I basically grew up (professionally) using MySQL for practically everything on every platform.

A couple of years ago I was forced to look at Postgres as part of a billing system I was working on and the experience wasn’t… very agreeable. It’s not a reflection on any attribute of Postgres mind you, just that is was different (read: possibly more complicated to work with) from MySQL.

Recently, the projects I have been working on use Postgres and I have been forced to have another very good look at it for which I am grateful. This last bout has changed my mind about Postgres completely. Once you get over the differences and get used to the tools, Postgres is very, very cool. But I digress, that train of thought I will finish in a fan-boy post some other time.

Replicating a live, big, busy Postgres database. Where to next?

There are a bunch of options actually and you can check them out here:
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Researching the options basically came down to this:

1. If I were setting up both database servers from scratch, I would probably give DRDB a try (http://www.drbd.org/). It’s basically mirrors an entire block device via a network. I didn’t have the stomach to set this up on two existing servers with different disks and OS versions.

2. For an existing system, I decided to give Londiste which is part of Skytools a try. Why? ‘Cos it looked pretty simple. And it was made by the dudes at Skype and if it’s good enough for Skype it’s got to be good enough for me, right? Right. And also, it’s in Python and I now like Python, a lot.

I would write a ‘how to set up Postgres replication on Ubuntu with Londiste and Skytools’ but that would be pointless. There is already a pretty good one right here:
http://wiki.postgresql.org/wiki/Londiste_Tutorial

With some extra stuff, like monitoring with Munin here:
http://wiki.postgresql.org/wiki/Skytools

The abridged version:
– Back your shit up.
– Download Skytools from: http://pgfoundry.org/projects/skytools
– Configure, make, install (will need Postgres development libraries) on both the master/provider and slave/subscriber.
– Create ticker.ini configuration file on the master/provider
– Install and run the ticker daemon on the master/provider
– Create the replication daemon configuration file on the slave/subscriber
– Install Londiste on the provider (from the subscriber)
– Install Londiste on the subscriber (from the subscriber)
– At some point, dump the master/prover database to the subscriber/slave (dead simple example: http://stackoverflow.com/questions/1237725/how-to-copy-postgres-database-to-another-server)
– Have you backed your shit up?
– Launch the replication daemon
– Add the tables you want to replicate to the provider queue
– Add the sequences you want to replicate to the provider queue
– Add all the provider tables to the subscriber
– Add the sequences to the subscriber
– Watch the log file and see all the replication goodness
– Check that the two databases have synced properly

Simple no?

Obviously there are some other things to consider like altering tables, monitoring performance and the like, but so far, it’s going exceedingly well.

A camel is cool, an elephant is cooler, but a Llama is the shit.