Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
NexentaStor? –  Michael Hampton Oct 10 '12 at 23:53

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
Thank you, we are already evaluating it! –  Alex Oct 11 '12 at 10:19

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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