Hacker News Reader

Comment by lilyball | Hacker News Reader

This article says you can back up your database by just copying it, as it’s a single file (and apparently copying it while it’s in use is fine?). It also suggests enabling WAL mode. But WAL mode means you now have two files. Does the use of WAL introduce any concerns while backing up?

View on HN

This is untrue and a sure way to corrupt your database[1].

From sqlite.org on how to corrupt your database:

> 1.2. Backup or restore while a transaction is active Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt.

> The best approach to make reliable backup copies of an SQLite database is to make use of the backup API that is part of the SQLite library. Failing that, it is safe to make a copy of an SQLite database file as long as there are no transactions in progress by any process. If the previous transaction failed, then it is important that any rollback journal (the -journal file) or write-ahead log (the -wal file) be copied together with the database file itself.

What you need is to follow the guide[2] and use the Backup API or the VACUUM INTO[3] statement to create a new database on the side.

[1] https://www.sqlite.org/howtocorrupt.html

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

[3] https://www.sqlite.org/lang_vacuum.html#vacuuminto

If WAL is enabled it's easy to backup your sqlite db by using VACUUM INTO <file>.

More detailed docs: https://www.sqlite.org/lang_vacuum.html#vacuuminto

The .wal file comes and goes, so occasionally things like rsync will fail if the file no longer exists by the time it tries to transfer.

The .wal file shouldn't be backed up though.

Why? An atomic filesystem snapshot is very similar to power loss. Since WAL is required to handle that scenario, it's just as important when backing up through snapshots.

If the .wal file is not backed up, then any information in it will not be backed up. Depending on the settings for the WAL, this can be quite a lot. You would need to do something like the VACUUM INTO trick described above if you want to safely backup the sqlite database from a live filesystem, because there is a risk of the WAL and database being out of sync. I’m not sure that SQLite, good though it is, can get around the WAL being taken at a different time to the rest of the database.

The problem here isn’t the .wal file, but doing backups from a moving filesystem. The solution is to use snapshots. ZFS supports snapshots natively, and Linux’s device mapper supports snapshots of any block device. [0]

[0] https://www.kernel.org/doc/html/latest/admin-guide/device-ma...

SQLITE_FCNTL_PERSIST_WAL can be used to avoid the .wal file being completely deleted. Also useful for when you need clients without write access to the database directory to always be able to open it read-only.

Not just WAL, there are other files like SHM too.

https://stackoverflow.com/a/55424784 it's saying you have to copy 3 files to backup everything

Reply on news.ycombinator.com