benbjohnson/litestream.io

Update cron docs to use VACUUM instead

benbjohnson opened this issue · 7 comments

benbjohnson commented

Apparently the backup command is not transactional but VACUUM is.

ignoramous commented

VACUUM INTO backups are transactional, but the .backup mechanism is not: https://news.ycombinator.com/item?id=31387556

Ben I suggest updating that cron backups documentation page to recommend VACUUM INTO instead!

ref.

simonw commented

https://til.simonwillison.net/sqlite/compile-sqlite3-ubuntu is when I first figured this out:

/home/simon/bin/sqlite3 data.db "vacuum into '/tmp/backup.db'"
simonw commented

I had a go at this in #57

It looks like it's not quite as simple as "VACUUM INTO is always better than .backup" - there are some subtleties. https://www.sqlite.org/lang_vacuum.html#vacuuminto says

The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database. The advantage of using VACUUM INTO is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced. Also, all deleted content is purged from the backup, leaving behind no forensic traces. On the other hand, the backup API uses fewer CPU cycles and can be executed incrementally.

simonw commented

Something I don't understand is what "can be executed incrementally" refers to with regards to the backup API - maybe that's something you can do with the C interface that isn't relevant to running .backup using the SQLite CLI tool.

benbjohnson commented

Merged. Thanks everybody!

  • 🎉2
rhcarvalho commented

Merging #57 didn't close this issue automatically, but I understand the matter is already resolved 👍