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 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: * GoToSocial * fail2ban * The Lounge 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 2025-07-08. I finally got around to twiddling with the backup script some more. The goal was to limit downtime of the services that use SQLite databases. This required the following change: --filter 'protect /var/lib/fail2ban/*' \ --filter 'protect /etc/thelounge/*' \ --filter 'protect /home/gotosocial/*' \ --exclude '/var/lib/fail2ban/*' \ --exclude '/etc/thelounge/*' \ --exclude '/home/gotosocial/*' \ This excludes the three directories from the transfer but doesn't delete them even though I use the --delete-excluded option at the top. The three directories are only transferred when their respective service is stopped. The biggest benefit, I think, is that fail2ban keeps running for as long as possible. This is very important. 😅 So this is the current script: #!/bin/bash # sibirocobombus.root is defined in ~/.ssh/config 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/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/*' \ --filter 'protect /var/lib/fail2ban/*' \ --filter 'protect /etc/thelounge/*' \ --filter 'protect /home/gotosocial/*' \ --exclude '/var/lib/fail2ban/*' \ --exclude '/etc/thelounge/*' \ --exclude '/home/gotosocial/*' \ sibirocobombus.root:/ \ /home/alex/Documents/Sibirocobombus # /var/lib/fail2ban echo Stopping fail2ban ssh sibirocobombus.root monit stop fail2ban sudo rsync --archive --acls --xattrs --compress \ --delete --delete-excluded \ --verbose --itemize-changes \ sibirocobombus.root:/var/lib/fail2ban/ \ /home/alex/Documents/Sibirocobombus/var/lib/fail2ban echo Starting fail2ban ssh sibirocobombus.root monit start fail2ban # /etc/thelounge echo Stopping The Lounge ssh sibirocobombus.root monit stop thelounge sudo rsync --archive --acls --xattrs --compress \ --delete --delete-excluded \ --verbose --itemize-changes \ sibirocobombus.root:/etc/thelounge/ \ /home/alex/Documents/Sibirocobombus/etc/thelounge echo Starting The Lounge ssh sibirocobombus.root monit start thelounge # /home/gotosocial echo Stopping GoToSocial ssh sibirocobombus.root monit stop gotosocial sleep 10 sudo rsync --archive --acls --xattrs --compress \ --delete --delete-excluded \ --verbose --itemize-changes \ --exclude '/home/gotosocial/backup/*' \ sibirocobombus.root:/home/gotosocial/ \ /home/alex/Documents/Sibirocobombus/home/gotosocial echo Starting GoToSocial ssh sibirocobombus.root monit start gotosocial