HOWTO Backup the SQLite database

To back up the SQLite database, the following script can be used. The script clones the database using SQLite tools, which also applies delayed transactions from the write-ahead log (unlike simple file copy), removes temporary data such as the message queue, compresses the database and uploads to Dropbox using rclone. The script runs daily.

#!/bin/sh
set -e

SOURCE=var/database.sqlite
TARGET_FOLDER=backups
TARGET_FILE=$TARGET_FOLDER/treemap-`date +'%Y%m%d'.sqlite`
REMOTE="dropbox:Yerevan Tree Map Database/"

echo "Creating folder $TARGET_FOLDER ..."
mkdir -p "$TARGET_FOLDER"

echo "Copying $SOURCE to $TARGET_FILE ..."
rm -f $TARGET_FILE
echo ".clone $TARGET_FILE" | sqlite3 $SOURCE >/dev/null

echo "Cleaning up the database..."
echo "DROP TABLE IF EXISTS upload_tickets;" | sqlite3 $TARGET_FILE
echo "DROP TABLE IF EXISTS queue_messages;" | sqlite3 $TARGET_FILE
echo "DROP TABLE IF EXISTS users;" | sqlite3 $TARGET_FILE
echo "VACUUM;" | sqlite3 $TARGET_FILE

echo "Compressing..."
xz -9 $TARGET_FILE
ls -lh $TARGET_FOLDER

echo "Copying to Dropbox..."
rclone copy $TARGET_FOLDER "$REMOTE"

Results of this script for the Yerevan Tree Map project can be found on this page.