SQLite backup

As I mentioned earlier, it’s straightforward to use SQLite as the relational database in Go applications. Your data is stored in one single file with a well-defined format and there is no hassle with installing and configuring a separate database.

However, for any serious application you most likely want backup of your data. If your dataset is not very large, and it is OK to lose updates since the last backup, there is a very simple option. Use the sqlite3 command line tool:

rm -f backup_of_your_database.sqlite 
sqlite3 your_database.sqlite "VACUUM INTO 'backup_of_your_database.sqlite'"

This command will fail if the backup file already exists, so don’t forget to remove or move it first. This will make a consistent snapshot of the database and is safe to run while your application is using the database. Then you can set up a cron job to run this every day (or on whatever cadence you like). You can save storage space by compressing the backup file with gzip or similar tool.

If your database is lost or corrupted, stop the application, copy the latest backup file in its place and restart the application.

More information here.

If your dataset is huge, and you want incremental backups, and/or you don’t want to risk losing recent updates, then this solution might be inadequate. There are other more sophisticated options like Litestream (although I have not tried that).