Hacker News Reader

Comment by mekster | Hacker News Reader

For regular DB like MySQL/PostgreSQL, just snapshot on zfs without thinking.

Databases these days are pretty resilient to restoring from crash consistent backups like that, so yes, you'll likely be fine. It's a good enough approach for many cases. But you can't be sure that it really recovers.

However, ZFS snapshots alone are not a good enough backup if you don't off-site them somewhere else. A server/backplane/storage controller could die or corrupt your entire zpool, or the place could burn down. Lots of ways to fail. You gotta at least zfs send the snapshots somewhere.

How do you mean can’t be sure if it recovers? It’s not hoping for inconsistent states to be recovered by the db but they’re supposed to be in good state with file system snapshotting.

https://serverfault.com/a/806305

https://zrepl.github.io/v0.2.1/configuration/snapshotting.ht...

Ha! I did not expect a reference to `innodb_flush_log_at_trx_commit` here. I wrote a blog post a few years ago about MySQL lossless semi-sync replication [1] and I've had quite enough of innodb_flush_log_at_trx_commit for a lifetime :-)

Depending on the database you're using, and on your configuration, they may NOT recover, or require manual intervention to recover. There is a reason that MSSQL has a VSS writer in Windows, and that PostgreSQL and MySQL have their own "dump programs" that do clean backups. Pulling the plug (= file system snapshotting) without involving the database/app is risky business.

Databases these days are really resilient, so I'm not saying that $yourfavoriteapp will never recover. But unless you involve the application or a VSS writer (which does that for you), you cannot be sure that it'll come back up.

[1] https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r...

Reply on news.ycombinator.com