SQLite

SafeGrd backs up a SQLite file on the host, WAL mode included, with nothing else to install. This page covers a one-off backup, a scheduled surface, and restoring the file.

That is one backup, now. For backups on a schedule, with Fire Drills, name the file as a surface in ~/.safegrd/config.yaml and run the daemon. Pick one of the two for a database: a surface starts its own history, so backups taken with --database-url before it show in the console as a separate surface.

A file copied off the disk while the app writes can be torn, and in WAL mode the newest transactions are still in the -wal file, which a copy of the database file misses. SafeGrd copies the database through SQLite itself, inside one read transaction: every committed transaction is in the copy, the WAL's included, and writers carry on. SQLite WAL mode and backups shows the difference on a test database.

• Each backup is a run of the surface's repository, like a PostgreSQL backup. The copy keeps every page where the database has it, so a run uploads and locks only the 64 KiB chunks whose pages changed. A 228 MB database with 400 rows updated since the last run uploaded 6.8 MB. Inserts into an index on random keys, such as UUIDs or email addresses, touch pages all over that index and upload more.
• --format tar, or format: tar on a surface, takes one archive per backup instead, compacted with VACUUM INTO. Once decrypted and unpacked it is a SQLite file any client opens.
• In WAL mode a backup does not block writers. In rollback-journal mode writers wait while the copy is taken. safegrd doctor says which mode the database is in, and PRAGMA journal_mode=WAL; switches it.
• The copy goes through PRAGMA integrity_check before a byte of it is uploaded. A damaged database is not stored, and the backup says why. Earlier snapshots are not touched.
• The copy is written to an owner-only directory under the system temporary directory and deleted after the upload. It needs free space about the size of the database; set TMPDIR to use another disk.
• A restore writes a new file and never overwrites one: safegrd restore --snapshot <id> --target sqlite:///var/lib/app/restored.db. The file and its -wal must not exist. The copy is checked with PRAGMA integrity_check and then renamed into place. A run restores whole: --table and --to-sql are for PostgreSQL.
• To put a restored file in place of the live one: stop the app, move the old app.db and any app.db-wal and app.db-shm aside, move restored.db to app.db, and start the app. The restored file is in rollback-journal mode; run PRAGMA journal_mode=WAL; on it first if the app expects WAL.
• The SQLite engine is built into safegrd, so the host needs no sqlite3.