We are running many Linux VM's in a vmware/shared storage environment, each running its own instance of postgreSQL (a mix of 9.0 and 9.3). Currently, the entire VM sits on a single root partition/volume, and we've had great success (~8 years) using storage-based snapshots of the underlying VMFS volumes for backup/restore process (and replication to our DR site).
Due to the architecture of our storage, it would be advantageous to separate postgres WAL files to a non-cached, mostly-write volume to give us less cache churn on the storage side. With our storage (Nimble Storage), we can assign both volumes to a single protection/snapshot group, but I haven't been able to elicit from our vendor that the snapshots will happen at EXACTLY the same time across all volumes in the protection group - it likely will, but there's always that chance that its milliseconds apart.
To that end, we ran some experiments, all while writing data to the DB as fast as possible using pg_bench. After the experiments, we restored our snapshot'ed volumes and started the VM+postgres
- Snapshot both data and log volumes close to simultaneously - result: DB recovered
- Snapshot data volume first, log volume ~1 minute later - result: DB recovered
- Snapshot log volume first, data volume ~1 minute later - result: DB recovered
- Snapshot log volume first, data volume ~3 minutes later, after a WAL checkpoint wrote new data to datafiles: result: DB recovered
So testing seem to tell us as long as both snapshots are consistent at the volume level, and relatively close together, you get a consistent copy of the DB, based on the time of the WAL/Log volume snapshot.
My question: Is this safe? What are the corner cases we are missing in our testing, and what could go wrong?
Postgres' doc indicates this is not safe, but testing seems to indicate its pretty robust: http://www.postgresql.org/docs/9.1/static/backup-file.html
If your database is spread across multiple file systems, there might not be any way to obtain exactly-simultaneous frozen snapshots of all the volumes. For example, if your data files and WAL log are on different disks, or if tablespaces are on different file systems, it might not be possible to use snapshot backup because the snapshots must be simultaneous. Read your file system documentation very carefully before trusting the consistent-snapshot technique in such situations.
NOTE: Yes, we know about other options to make sure they are consistent, like putting PostgreSQL into hot backup mode or using our storage's VMware integration to quiesce the VM's themselves, but we are looking for a storage-only solution for speed, convenience, and zero impact to our clients.