[muxator]

I do not think it would be a good idea to use file system level snapshotting for backing up a database. The database "knows better" about its internals, and can give more guarantees about the consistency of its data. I would trust a filesystem-levdl backup only as a last resort.

[iracic]

It is possible to put database in state that is "ready" for snapshot, pushing changes to disk and sort of freezing I/O during snapshot.

[jstrong]

this is generally not a matter of concern for a copy on write filesystem like zfs, since it's not possible for the file to be in an "in between" state. If a write were in progress, the filesystem would still be pointing to the previous state. Only when the data is written to disk is the pointer moved to the new location.

[tjoff]

It very much is a concern. ZFS has no knowledge about the internals of a database, which parts of a file are related to each other etc.

[RX14]

DBMSes always keep their database in the file system in a consistent state to be able to recover from system crashes. Taking a file system snapshot is equivalent to pulling the power on the database server in terms of data recovery, but databases are designed to support this.

[tjoff]

As do filesystems. Yet I've seen anyone argue that cutting the power is the recommended way of doing backups.

In fact the opposite, make sure to use an UPS just so that you can shutdown cleanly in the unfortunate event.

For example: https://blogs.oracle.com/paulie/backing-up-mysql-using-zfs-s...

[wmf]

Some people came up with the idea of "crash-only software", arguing that it's better to maintain one code path (recovering from a crash) than two (clean start and recovery), but it hasn't caught on that much. https://www.usenix.org/legacy/events/hotos03/tech/full_paper...

[rocqua]

It matters when various writes to files need to be ordered, and the DB processes is keeping some of the consistency part in memory.

[Teknoman117]

It is a matter of concern if said database systems leaves its filesystem contents in an inconsistent state at any point. ZFS, BTRFS, and others can only keep consistent what they have control over.

[mekster]

I also think database specific backup makes more sense.

Some people recommend filesystem snapshotting but wouldn't that make recovery a slow process because you have to load up the entire database even if you just wanted to look up on data of a small table?

Maybe backing up only small tables as SQL dumps while keeping a file system snapshot would be a good compromise.