Re:Correct Backups Require Filesystem Snapshots
by tatersolid on Tuesday May 17, 2022 @07:44AM
(#31409028)
In many if not most production databases, that write-ahead log will be on a different volume or file system from the database files. This is often recommended in documentation for performance reasons.
So now it is quite possible you back up an older WAL coupled with a newer DB file that has partial writes inside. Boom.
This “multi-volume, multi-writer coordination” is the sort of problem VSS solves on windows. I’m not sure there’s anything equivalent on Linux or MacOS that can do multi-volume snapshots consistently.
Most databases I use keep a write-ahead log, which guards against power failure without requiring an external service. Multiple writes are handled without an issue. If power is lost, the database recovers automatically using whatever is on disk, no matter when it was interrupted.
So a file system snapshot behaves the same and the database can start up from the snapshot even if it was taken at an arbitrary point mid-write.