Like many people in the web industry I’ve been using MySql for quite some time as part of LAMP projects). When I started my current project using PostgreSQL I looked around for easy replication technique. I found a couple including PGCluster and Slony, but they seemed much more complicated to set up than replication in MySql.
The solution was actually right there in the PostgreSQL manual, I was just googling with the wrong terms. I should have been looking for WAL PITR (seems so obvious now!).
Anyway, these acronyms, which combined and expanded stand for Write Ahead Log Point in Time Recovery, provide a built in solution to disaster recovery. This technique is also very well documented in the online manual.
The gist of the solution is that you take a snapshot of the database and then archive incremental changes by copying the write ahead logs. Your backup DB hangs out and consumes these logs as they’re produced which keeps it quite close to the production DB. When your primary DB server fails, you let the backup DB process any remaining logs and then point your applications at it.
This has the advantage of being easy to set up and official supported, but it only provides a warm backup and required manual intervention in the case of a hardware failure. Also, since the default WAL file size is 16mb, you will lose up to 16mb of data in the standard configuration which may be a big deal for low activity databases.
In spite of these flaws, I opted for this solution at least to get started. Later when I have more load I’ll revisit proper replication.
