10 years of PostgreSQL replication

February 6’th 2012 marks the 10 year anniversary of the open source release of the DBMirror replication system. DBMirror was not the first PostgreSQL replication solution to be released but it was the first one I was involved with.

In the summer of 2001 I was working for Navtech System Support, an aviation software company. We were using PostgreSQL 6.x to store data for one of our applications. We needed to have an up to date copy of the database available on servers at a remote site. We also needed a standby database server in case our primary server failed.

PostgreSQL didn’t have any built in replication but it did have triggers. My colleagues at Navtech and myself figured that if we wrote a trigger that captured a record of all changes made to a database table we would then know what changes needed to be made on a replica to keep it up to date with the master.

I wrote the initial version of DBMirror over a few weeks during the summer of 2001 targetting PostgreSQL 7.1. I no longer have a copy of the original version but I think it only stored the primary key of each row and referenced the original table while replicating. In the September/October time-frame I spent a few more weeks reworked DBMirror into the form we released it in. This version stored the entire contents of a modified row. This is was the first version that saw production use.

In February of 2002 the management team at Navetch was kind enough to allow us too open-source DBMirror. DBMirror was included with the PostgreSQL source code as contrib/dbmirror where it remained as a contrib module until 2007. No one tracks how many users any particular PostgreSQL replication solution has (or had) but based on mailing list traffic DBMirror was far more popular than contrib/rserv, or any of the other options available until Slony become more mature almost 4 years later.

DBMirror was not particularly efficient, it stored each column of a replicated table in its own row of the pendingData table, the replication daemon was written in perl and the query to approximate the commit order taxed the database server. The cleanup script also created a lot of work for vacuum.

The biggest strength DBMirror had going for it was that it was simple to setup and it was flexible. People could install DBMirror and replicate their database after reading a three page README file. The replication daemon was written in perl and was easy to understand. Many people customized dbmirror.pl to perform site specific tasks like renaming tables or excluding rows that matched a particular regex.

I haven’t modified the dbmirror code in years and I would be surprised if there were many dbmirror installations still running. I am truly grateful that I was given the opportunity to develop and release DBMirror. I continue to be involved with PostgreSQL replication as a Slony developer. I also assist with built in replication where by skills and time allow.

In the past 10 years PostgreSQL replication has come a long way from the few thousand lines of C and perl code that made up dbmirror. I look forward to the advances in replication we will see in the next 10 years.

It has been years since I have heard from the other people at Navtech who were involved in the development, testing and deployment of DBMirror. If any of them are reading this they should send me a note.