My customer has quite a large (the total "data" folder size is 200G) PostgreSQL database and we are working on a disaster recovery plan. We have identified three different types of disasters so far: hardware outage, too much load and unintentional data loss due to erroneously executed bad migration (like DELETE or ALTER TABLE DROP COLUMN).

First two types seem to be easy to mitigate but we can't elaborate a good mitigation plan for the third type. I proposed to use ZFS and frequent (hourly) snapshots but "ZFS" means "OpenIndiana" these days and our Ops engineers do not have much expertise in it, so using OpenIndiana imposes another risk. Colleagues try to convince me that restoring from PostgreSQL PITR backup can be as fast as restoring from a ZFS snapshot but I highly doubt that replaying, say, 50G of archived WALs can be considered "fast".

What other options are we missing? Is ZFS an only viable alternative? Can we get a fast Pg DB restore time in the Linux environment?


Why is FreeBSD not a viable option to run ZFS and PostgreSQL on? The FreeBSD ZFS developers works very close with the Illumos team and just recently Pawel Jakub Dawidek (The man who first ported ZFS to FreeBSD) added SSD TRIM support for ZFS. This will most likely also be added to Illumos ZFS code soon.

Another advantage with FreeBSD and ZFS is the GEOM framework. On Solaris, when entire disks are added to a ZFS pool, ZFS automatically enables their write cache. This is not done when ZFS only manages discrete slices of the disk, since it does not know if other slices are managed by non-write-cache safe filesystems, like UFS. The FreeBSD implementation can handle disk flushes for partitions thanks to its GEOM framework, and therefore does not suffer from this limitation.


I suggest you take a look at Barman, Backup and Recovery Manager for PostgreSQL, which has been written by us and is available as open-source under GNU GPL 3 terms. To give you an idea, we use it in production on databases larger than yours (7 Terabytes). Version 1.0 has been released latest July. There is an RPM version already, Debian package is on the way (Barman will be included in Ubuntu 12.10). For more information: www.pgbarman.org.

  • Worth noting that, on researching it, it does have a few notable drawbacks. To wit: It requires both passwordless SSH access from the backup server to the postgres server and, bizarrely, passwordless SSH access from the postgres server back to the backup server. It also requires password-ful network login from the SSH account on the postgres machine to the postgres server. From what I could tell it does not support local UNIX socket login. – Parthian Shot Aug 3 '15 at 21:46
  • Additionally, as of this comment, the version of barman in the Debian Jessie repository is 1.3.3, and the most recent version of barman is 1.4.1. The documentation, however, is not versioned; so it will use features up through the most recent version, even if those will not be available / may behave differently on your system. – Parthian Shot Aug 3 '15 at 21:50
  • Also worth noting is that this answer was written by an employee at 2ndQuadrant; the company that develops Barman. Which isn't to cast aspersions on the accuracy of his endorsement, merely to point out that it is necessarily going to be biased. And it would be frankly quite bizarre if his company didn't use their own product. – Parthian Shot Aug 3 '15 at 21:55
  • Finally, last comment on this... The code is hosted on Sourceforge, and primarily targets RHEL-based distros. – Parthian Shot Aug 3 '15 at 22:44

Replaying archived WALs is the best option here and most likely the fastest.

It's the best because you get whole timeline. No data loss at all. With all types of snapshots you will loose data. Hourly snapshots mean that worse case scenario you loose 1 hour worth of DB changes (disaster happens just before next snapshot).

Also if you do physical (not logical - requires db snapshot too, best for restoring dropped table etc) recovery it's done on block level and is VERY fast.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.