databases might need to do lots of writes. (example, could be modify multiple rows in a db). They can't neccessarily capture it into a single write operation. they will issue multiple.
VSS is a method windows has to allow application to both be informed that a snapshot operation is in progress (and should quiesce their writes) as well as the ability to delay the snapshot operation from occurring (because they are in the middle of a "transaction"). If they delay too much, the snapshot operation will fail and reported back as such to the caller.
edit: also see the former maintainer of VSS at MSFT who responded to me in my other post in this thread
While the former maintainers post was very interesting, as I see it mostly helps with applications that already manage the sudden power loss use case. So a database can make use of that explicit API to somewhat better handle a snapshot, with no lingering transactions.
With only a COW filesystem snapshot, the end result will still be correct in case of programs that use transactions (as mentioned, dbs will either have commited all the transactions and the data is saved, or one was in-flight, those will be reverted and you are still in a consistent state). For programs that don’t support transactions both might be faulty, for those that support transactions COW will work correctly and for those that support this API COW will again work and might save a bit fresher version.
Doesn't sound like that protects against power failure.
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.
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.
Comments
databases might need to do lots of writes. (example, could be modify multiple rows in a db). They can't neccessarily capture it into a single write operation. they will issue multiple.
VSS is a method windows has to allow application to both be informed that a snapshot operation is in progress (and should quiesce their writes) as well as the ability to delay the snapshot operation from occurring (because they are in the middle of a "transaction"). If they delay too much, the snapshot operation will fail and reported back as such to the caller.
edit: also see the former maintainer of VSS at MSFT who responded to me in my other post in this thread
While the former maintainers post was very interesting, as I see it mostly helps with applications that already manage the sudden power loss use case. So a database can make use of that explicit API to somewhat better handle a snapshot, with no lingering transactions.
With only a COW filesystem snapshot, the end result will still be correct in case of programs that use transactions (as mentioned, dbs will either have commited all the transactions and the data is saved, or one was in-flight, those will be reverted and you are still in a consistent state). For programs that don’t support transactions both might be faulty, for those that support transactions COW will work correctly and for those that support this API COW will again work and might save a bit fresher version.
Doesn't sound like that protects against power failure.
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.
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.
In that case you just back up the database first and the WAL second.