5 ms·

Is it safe to backup a database by just straight copying its files? Wouldn't it be better to use a specific backup tool, e.g. pg_dump for Postgres or mongodump

Is it safe to backup a database by just straight copying its files? Wouldn't it be better to use a specific backup tool, e.g. pg_dump for Postgres or mongodump for Mongo? (Hopefully you've tested recovery procedures, because as the saying goes, "your backups are only as good as your last restore")

pmlnr 8y ago
ZFS is copy-on-write, so in most cases, it is safe.
lmz 8y ago
If you use a consistent snapshot of the volume it should be ok. The database would perform crash recovery on startup but data should still be consistent.
chousuke 8y ago
With Postgres, it's perfectly safe and supported if you just do it according to the documentation using pg_start/stop_backup and also archive your WALs properly. Most databases have something similar.
- lettergram 8y ago
  Note, you do need to track the version of Postgres. So a backup from a PG 8 may need to be reloaded into PG 8 before upgrading to PG 9, 10, 11, etc
mbell 8y ago
Depends on the database / disk setup. For Mongo you should probably do an fsync/lock prior to taking the snapshot then release it, this will ensure the data on disk is in a consistent state. I don't think this is technically required with a single disk setup (at least with LVM, I've not used ZFS with mongo) but it's probably a good idea anyway and only causes a few ms hiccup.
wang_li 8y ago
> Is it safe to backup a database by just straight copying its files? ZFS snapshots are atomic. Recovering from a snapshot is the same as recovering from a loss of power. All serious databases don't acknowledge commits until either the transaction log or the container is synced to disk and can handle an unexpected loss of power/reboot/system crash without significant data loss. However, it is absolutely better to put the database into some kind of backup/write-suspend mode when taking the snapshot.
arghwhat 8y ago
Yes, if the files cannot mutate while it is being done. Shut down the DB, or use a CoW filesystem which can do atomic snapshots (zfs, btrfs, bcachefs, apfs, ...)

pmlnr 8y ago

ZFS is copy-on-write, so in most cases, it is safe.

lmz 8y ago

If you use a consistent snapshot of the volume it should be ok. The database would perform crash recovery on startup but data should still be consistent.

chousuke 8y ago

With Postgres, it's perfectly safe and supported if you just do it according to the documentation using pg_start/stop_backup and also archive your WALs properly. Most databases have something similar.

lettergram 8y ago
Note, you do need to track the version of Postgres. So a backup from a PG 8 may need to be reloaded into PG 8 before upgrading to PG 9, 10, 11, etc

mbell 8y ago

Depends on the database / disk setup. For Mongo you should probably do an fsync/lock prior to taking the snapshot then release it, this will ensure the data on disk is in a consistent state. I don't think this is technically required with a single disk setup (at least with LVM, I've not used ZFS with mongo) but it's probably a good idea anyway and only causes a few ms hiccup.

wang_li 8y ago

> Is it safe to backup a database by just straight copying its files? ZFS snapshots are atomic. Recovering from a snapshot is the same as recovering from a loss of power. All serious databases don't acknowledge commits until either the transaction log or the container is synced to disk and can handle an unexpected loss of power/reboot/system crash without significant data loss. However, it is absolutely better to put the database into some kind of backup/write-suspend mode when taking the snapshot.

arghwhat 8y ago

Yes, if the files cannot mutate while it is being done. Shut down the DB, or use a CoW filesystem which can do atomic snapshots (zfs, btrfs, bcachefs, apfs, ...)