Hacker News new | ask | show | jobs
by cm2187 487 days ago
How do you backup a file like that?
4 comments

Using the SQLite backup API, which pretty much corresponds to the .backup CLI command. It doesn't block any reads or writes, so the performance impact is minimal, even if you do it directly to slow-ish storage.
> It doesn't block any reads or writes.

That's neat! I bet it keeps growing a WAL file while the backup is ongoing right?

Hard to imagine doing it any other way, which is probably fine up until you hit some larger files sizes.
That copies the entire file each time (not just deltas).

You may find sqlite_rsync better.

I use zfs snapshots, they work in diffs so they're very cheap to store, create, and replicate.
sqlite_rsync is new tool created by sqlite team. It might be useful.
Ctrl-c, Ctrl-v
That's not great advice for a large database (and I wouldn't recommend it for small databases either). That incidentally works when the db is small enough that the copy is nearly atomic, but with a big copy, you can end up with a corrupt database. SQLite is designed such that a crashed system at any time does not corrupt a database. It's not designed such that a database can be copied linearly from beginning to end while it's being written to without corruption. Simply copying the database is only good enough if you can ensure that there are no write transactions opened during the entire copy.

A reliable backup will need to use the .backup command, the .dump command, the backup API[0], or filesystem or volume snapshots to get an atomic snapshot.

[0]: https://www.sqlite.org/backup.html