From 13740a0bf8101f49069301fc9b58c87d40e36c34 Mon Sep 17 00:00:00 2001 From: Julien Dessaux Date: Tue, 9 Nov 2021 16:46:42 +0100 Subject: Added SQLite backups blog article --- content/blog/miscellaneous/sqlite-backups.md | 34 ++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 content/blog/miscellaneous/sqlite-backups.md (limited to 'content/blog/miscellaneous') diff --git a/content/blog/miscellaneous/sqlite-backups.md b/content/blog/miscellaneous/sqlite-backups.md new file mode 100644 index 0000000..4cf9bae --- /dev/null +++ b/content/blog/miscellaneous/sqlite-backups.md @@ -0,0 +1,34 @@ +--- +title: How to properly backup your SQLite databases +description: I used to simply .dump those +date: 2021-11-09 +tags: +- backups +- SQLite +--- + +## Introduction + +SQLite is great and I have been using it for years while self-hosting various services. It is easier, lighter and fast enough for most needs which is critical when the time you can spend managing your personal infrastructure is finite. + +Recently while talking with a developer friend I learned about the `user_version` PRAGMA, which could be a simple way to handle migrations in a custom app. For migrations to work you need a way to save the current revision of your schema somewhere : I was using a table for that, with a single column and a single row to store that revision. This PRAGMA would be a nice way to handle this in SQLite. + +## The backup problem + +The problem with this PRAGMA is that it does not appear in a `.dump`! So if you rely on this to backup your databases (like I did) then you would lose the schema revision upon restoring your service, or migrating it to another host. + +When looking into this problem I learned that a better way to handle SQLite backups is with `VACUUM INTO 'file.db';` (added in SQLite 3.27 in early 2019). Not only does this create a perfect copy of your database at the point in time you run this command, the PRAGMA are kept and the indexes will not need to be rebuilt! + +## Conclusion + +I switched all my SQLite backups to `VACUUM INTO` and will not look back! + +If by chance you are using my [borg ansible role]({{< ref "docs/adyxax.org/backups/borg-ansible-role.md" >}}) to manage your backups, here is what an SQLite job entry looks like : +``` +- name: srv-short-db + path: "/tmp/short.db" + pre_command: "echo 'VACUUM INTO \"/tmp/short.db\"'|sqlite3 /srv/short-data/short.db" + post_command: "rm -f /tmp/short.db" +``` + +I did not yet migrate away from my schema version table to replace it with the pragma : I need to think about it more before committing to this kind of change. -- cgit v1.2.3