field noteSQLite · Backups

Backing up SQLite in WAL mode without losing writes

Copying the database file of a busy SQLite database quietly drops recent changes. Use the database's own backup instead.

Several tools I run keep their state in SQLite with write-ahead logging (WAL) turned on, Headscale among them. WAL makes concurrent reads and writes fast, and it also changes what a backup has to copy.

Why copying the file is not enough

In WAL mode, new writes go to a separate -wal file first and are folded into the main database file later, at a checkpoint. Copy only db.sqlite and you get the database as of the last checkpoint: recent changes are missing, and nothing tells you so. Copy the files one by one while the database is in use and you can capture them at different moments.

What to do instead

Ask SQLite for the backup. Its online backup reads a consistent snapshot, including what is still in the WAL, while the application keeps running.

sqlite3 /var/lib/headscale/db.sqlite ".backup '/backups/headscale-$(date +%F).sqlite'"

# or, from SQLite 3.27:
sqlite3 /var/lib/headscale/db.sqlite "VACUUM INTO '/backups/headscale.sqlite'"
Either gives a single, consistent file with no downtime.

What I took from it

  • Check the journal mode (PRAGMA journal_mode;) of anything you back up.
  • Restore a backup into a scratch copy now and then, and check it opens and has the newest rows.