May 3, 2013

PostgreSQL | "PostgreSQL Backup and Restore How-to" review

PostgreSQL Backup and Restore How-to

Having worked with PostgreSQL continuously since 2001, I'd like to think there's nothing I don't know about backing up and restoring. However, experience shows that a) it's all too easy to develop "muscle memory" for a certain way of doing things, and b) PostgreSQL has a pesky habit of developing useful new features which fly under the radar if you're not paying sufficient attention to the release notes, so any opportunity to review things from a fresh perspective is a welcome one.

I ordered the paper version of "PostgreSQL Backup and Restore How-to" by Shaun Thomas from Amazon Japan for a tad under 2,000 yen, which is a little on the expensive side for what was described as a "booklet" (and four times the price of the eBook version), but I have a (meagre) book budget to burn and I have this old-fashioned habit of scribbling notes on margins and making little bookmarks from Post-It notes etc., also it's nice to spend some time not staring at a screen. To my surprise it arrived less than 48 hours after ordering - upon closer examination it turned out the book was actually printed by Amazon in Japan, which is kind of nifty.

First impression: it's a very thin volume - 42 actual content pages - so is genuinely a booklet. On the other hand, bearing in mind I've got a bookshelf full of largely unread weighty tomes, less can be more.

The booklet's table of contents, as lifted directly from the publisher's site, is:

  • Getting a basic export (Simple)
  • Partial database exports (Simple)
  • Restoring a database export (Simple)
  • Obtaining a binary backup (Simple)
  • Stepping into TAR backups (Intermediate)
  • Taking snapshots (Advanced)
  • Synchronizing backup servers (Intermediate)
  • Restoring a binary backup (Simple)
  • Point in time recovery (Intermediate)
  • Warm and hot standby restore (Intermediate)
  • Streaming replication (Advanced)

Each section is presented in the same recipe-style format divided into four sections ("Getting ready", "How to do it...", "How it works..." and "There's more..."). It's written in a style which should make it easy for non-PostgreSQL specialists to get up to speed. I like the booklet format, which addresses a deficiency of the main PostgreSQL documentation, namely that it's very detailed but  doesn't always make it easy to get an overview of the options available for a particular subject such as backups or replication. "PostgreSQL Backup and Restore How-to" doesn't cover every conceivable backup-related subject, but then that would go beyond the scope of this format.

My main criticism is that this book should have been published at least 10 years ago, when I certainly could have used it. Personally, over the years I've covered pretty much all of the points explained, but there were certainly a few things which were new to me (for example, parallel pg_restore had somehow slipped my attention). However it's still handy to have around, and no you can't borrow my copy. For anyone beginning PostgreSQL, or working with it on a part-time basis, or needing to get colleagues up-to-speed, I'd say it's a must-have.

A minor point: although not a backup/restore method per-se, it might be useful to have at least a mention of pg_upgrade, which is an alternative to the traditional "dump'n'restore" required when upgrading between major PostgreSQL versions, to provide some context for the book's target audience. Also, it might be useful to have links to the official documentation for reference (maybe as an appendix?), and possibly (as another appendix) a list of more advanced backup solutions for further reading ("Barman" has been pointed out as one subject not mentioned at all).

Finally, I'd just like to say it would be a fine thing if there was a similar booklet covering replication.

Other blog posts covering this book:

See also:

Posted at 12:01 PM

Yeah, I agree about the backup tools. Their Instant format was pretty strict, but I could have slipped in a reference to Barman, as I did with OmniPITR. Sad to say, I've been using custom scripts for so long, I didn't even know about Barman.

Just a few more things to add to a second edition, should they commission me to update the docs. :)
Posted by: Shaun Thomas | 2013-05-03 15:28