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 all programs using database files before beginning the backup and restarting them after the backup. It's also important to stop these programs using the monitoring software.
I use Monit to monitor the server. If I don't stop the programs using Monit, Monit will discover that they are not running and restart them while rsync is still copying the database file. The GoToSocial database is 10G so copying it takes a long time. Long enough for Monit to discover that GoToSocial isn't running to restart it.
The programs using database files:
Watch the log files for messages saying that files changed.
This is my script:
#!/bin/bash
# sibirocobombus.root is defined in ~/.ssh/config
echo Stopping GoToSocial
ssh sibirocobombus.root monit stop gotosocial
echo Stopping fail2ban
ssh sibirocobombus.root monit stop fail2ban
echo Stopping The Lounge
ssh sibirocobombus.root monit stop thelounge
echo Backing up Sibirocobombus
sudo rsync --archive --acls --xattrs --compress \
--delete --delete-excluded \
--verbose --itemize-changes \
--exclude '/home/alex/Downloads/' \
--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/.local/share/Trash' \
--exclude '/dev/*' \
--exclude '/lost+found/*' \
--exclude '/media/*' \
--exclude '/mnt/*' \
--exclude '/proc/*' \
--exclude '/run/*' \
--exclude '/sys/*' \
--exclude '/tmp/*' \
--exclude '/var/log/*' \
sibirocobombus.root:/ \
/home/alex/Documents/Sibirocobombus
echo Starting GoToSocial
ssh sibirocobombus.root monit start gotosocial
echo Starting GoToSocial
ssh sibirocobombus.root monit start fail2ban
echo Starting The Lounge
ssh sibirocobombus.root monit start thelounge
#Backup #Administration