On Jun 9, 2016, at 1:11 PM, John R Pierce pierce@hogranch.com wrote:
filesystem level snapshots such as provided by ZFS are also very workable for such databases, as they are point-in-time views of the filesystem. even if transactions are in process when the snapshot is made, if its later restored and the database server is restarted, the results are exactly the same as if the reset button had been pulled at that instant, postgres does a transaction recovery/cleanup, and resumes normal operation, with any committed transactions intact, and any transactions that were in progress rolled back.
This.
If your DBMS does not recover a filesystem-level snapshot, you should stop using that DBMS because it will fail you under other real-world error scenarios, too.
This is not specific to ZFS. The same can be said for UFS2, btrfs, NTFS, ReFS…
(The latter two via Volume Shadow Copy Service, a feature of Windows often used specifically for its ability to snapshot a running DBMS store.)