2025-01-27 Using rsync with Sqlite databases

I use Debian stable so my copy of Sqlite is not the latest and greatest. At the same time, I don't use Borg Backup for my server. I use rsync.

I copy the content of the important server files to my local laptop and then I make a backup of my laptop to external disks using Borg Backup.

When making copies using rsync, the database must not be in use. Otherwise, a busy database is probably going to be corrupt when restoring it from backup.

The key is to stop the program using the database before beginning the backup and starting it after the backup. It's also important to stop the program using the monitoring software. I use Monit to monitor the server. If I don't stop GoToSocial using Monit, Monit will discover that GoToSocial isn't running and start it while rsync is still copying the database file. The database file is 10G so copying it takes a long time. Long enough for Monit to discover that GoToSocial isn't running.

I should stop and start other programs, too: The Lounge, Norn, etc.

This is my script:

#!/bin/bash

# sibirocobombus.root is defined in ~/.ssh/config

# Using sudo rsync --archive to preserve ownership.
# Using --fake-super to avoid changes to groups and owners

echo Backing up Sibirocobombus
echo Stopping GoToSocial
ssh sibirocobombus.root monit stop gotosocial
rsync --archive --fake-super --verbose --compress --delete --delete-excluded \
      --itemize-changes \
      sibirocobombus.root:/home \
      --exclude '/home/alex/alexschroeder/' \
      --exclude '/home/alex/helmut/' \
      --exclude '/home/alex/alexschroeder.ch/share' \
      --exclude '/home/alex/planet/alex/' \
      --exclude '/home/alex/planet/indie/' \
      --exclude '/home/alex/planet/jdr/' \
      --exclude '/home/alex/planet/osr/' \
      --exclude '/home/alex/planet/other/' \
      --exclude '/home/alex/planet/podcast/' \
      --exclude '/home/alex/planet/podcast-de/' \
      --exclude '/home/alex/planet/podcast-fr/' \
      --exclude '/home/alex/.cpan/build' \
      --exclude '/home/alex/.cpan/sources' \
      --exclude '/home/alex/.cpanm' \
      --exclude '/home/alex/.cache' \
      --exclude '/home/alex/.Trash' \
      --exclude '/home/alex/.local/share/Trash' \
      sibirocobombus.root:/etc \
      sibirocobombus.root:/var/lib/news \
      sibirocobombus.root:/var/lib/dpkg \
      sibirocobombus.root:/var/lib/apt/extended_states \
      sibirocobombus.root:/var/lib/radicale \
      sibirocobombus.root:/usr/lib/cgit/filters \
      /home/alex/Documents/Sibirocobombus
echo Starting GoToSocial
ssh sibirocobombus.root monit start gotosocial

​#Backup