Thanks to the company I work for OmniTI I was working on pretty cool project. Name of the project is OmniPITR, and here is what it is, why, how, and where to get it.
We are using WAL replication quite extensively. We also do make hot backups a lot. What bugged me (and possibly other, but it could be that I just whined and whined, and they simply say: OK, if you don't like it – fix it) was that it was always complicated setup.
We had some program to archive wal segments. Then there was cronjob to move them to 2 places (backup server, and slave server), then we used pg_restore, sometimes with %r and sometimes without (%r removed obsolete segments).
On top of it there are backups which get tricky – we need to make them on master, which means we have to have wal segments on master, and we need something to cleanup in wal archive on master afterwards. All in all – it worked, but I always felt it could be done in a more self-contained way.
Now, this way has materialized.
What OmniPITR is? It's one simple set of tools, where each tool has its own set of tasks, and number of tools is as small as possible, which let me handle all parts of WAL-replication and backup:
- archiving wal segments
- making hot backups
- restoration of wal segments on slave
These are done already. And work. And are (at least to the extent of our possibilities) tested.
We also work on:
- making hot backups on slave
- single tool to monitor state of replication/backups
Now. What's cool about it? Let me list some features:
- It has documentation. Actually – documentation is written before code.
- It has full step by step howto about setting replication
- It works transparently with archived wal segments – i.e. uses less disk space. Supports gzip, bzip2 and lzma compressions.
- Has the ability to apply wal segments on slave with delay – for example to protect against propagating ‘TRUNCATE TABLE users' executed on master to slave(s)
- PostgreSQL license – basically – do what you want
- Is fully configurable using command line options
- Did I mention that there are docs?
To use it you need:
- Some will. It will not work on itself. You have to set it up (although there is detailed howto provided).
- Some unix machine(s) – I didn't test it on Windows and I just don't have any. It might work, but currently – it just hasn't been run
- Perl. I know. perl sucks, is unreadable, there are better languages, and so on. Right. So you need Perl
- Perldoc program. It should come in with perl, if not – it is in some separate package, like perl-doc. This is not strictly requires, but it helps with reading docs.
- PostgreSQL. We tested it from 8.2 on.
Missing features (backup on slave, monitoring, and whatever is in todo doc) will be added in short future. Generally I expect to have backup on slave by the end of next week.
Now for some short Q&A:
- Why docs are in perldoc? Why not .html?
- Because I like perldoc. If you want .html – there is pod2html program bundled with Perl
- Really, why didn't you write it in some sensible language? Python? Ruby? Go? Java?
- Well. Perl is sensible for me. It's by default on most unices, it behaves the same (or close to the same) everywhere, and it is powerful (or: powerful enough) to let me do anything I want
- Isn't it obsolete by the fact that in 9.0 there is streaming replication?
- Not really. You still need restore_command on slave to start the procedure, and handle cases where you'd have to “catch-up" for whatever reason ( network issues, slave downtime). Also – OmniPITR also handles hot backup, which are not solved by streaming replication.
- Why make it public/free?
- First of all – I'm very selfish. I want my piece of software to be as good as possible. This means – tests. I can perform only so many tests. With new users I get broader test base. I get to fix occasional bugs, but generally the product gets better. And second of all – I think it is nice way of having wal replication, so why not share it if I can? I got my Pg for free after all.
That's about it. If you'd want to play with it – fine, go for it. If you have problems – please let me know. If you think docs are wrong in any way – please let me know. And have some fun.