2026-03-31 Backups with SQLite databases running
================================================

If your system has SQLite databases running, and you're making
backups, chances are that you cannot just copy the files. If a change
arrived while you made the copy, the file can be in an inconsistent
state and you won't be able to open the copy.

Check if you can open it using sqlite3.

There are two solutions:

* shut down the application that's using the SQLite database;

* using sqlite3_rsync (which is not in Debian Trixie).

I used to shut applications down but these days that means shutting
down GoToSocial for half an hour or more since the database is about
10 GiB (I know!! 😭). So I did the thing and downloaded the
sqlite3_rsync binary from the SQLite site and installed it on the
server and on my laptop. And it works!

Anyway, in my /etc/fstab, I use a line to make sure that the UUID
b25166fa-7c47-43d0-af4c-e3cfd1ec780a is mounted on /mnt/server-backup
, a directory I created. Thus, when I plug in my SD Card with 128 GiB,
it gets mounted in the right place. The size of these cards still
amazes me.

This is for the new server, paraelectrobombus.

    #!/bin/bash
    
    set -ueo pipefail
    
    if lsblk --noheadings --list --output uuid,mountpoints \
     | grep "^b25166fa-7c47-43d0-af4c-e3cfd1ec780a  */mnt/server-backup$"; then
        echo /mnt/server-backup is mounted
    else
        echo /mnt/server-backup is not mounted
        echo or it is not b25166fa-7c47-43d0-af4c-e3cfd1ec780a
        echo check fsdisk and lsblk
        exit 1
    fi
    
    # paraelectrobombus is defined in /root/.ssh/config (since we're using
    # sudo!) and the IP numbers are in /etc/hosts.
    
    # Note how we're skipping (but not deleting!) the SQLite databases.
    # There's going to be some broken media references as the media files
    # keep coming while the database is replicating.
    
    echo Backing up Paraelectrobombus
    sudo rsync --archive --acls --xattrs --compress \
        --delete --delete-excluded \
        --verbose --itemize-changes \
        --exclude '/root/.cache/' \
        --exclude '/srv/planet/alex/' \
        --exclude '/srv/planet/indie/' \
        --exclude '/srv/planet/jdr/' \
        --exclude '/srv/planet/osr/' \
        --exclude '/srv/planet/other/' \
        --exclude '/srv/planet/podcast/' \
        --exclude '/srv/planet/podcast-de/' \
        --exclude '/srv/planet/podcast-fr/' \
        --exclude '/root/.local/share/Trash' \
        --exclude '/var/cache/*' \
        --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/fail2ban.sqlite3' \
        --exclude '/var/lib/fail2ban/fail2ban.sqlite3' \
        --filter 'protect /etc/thelounge/logs/*.sqlite3' \
        --exclude '/etc/thelounge/logs/*.sqlite3' \
        --filter 'protect /srv/gotosocial/sqlite.db' \
        --exclude '/srv/gotosocial/sqlite.db' \
        --filter 'protect /srv/xobaque/*/index.db' \
        --exclude '/srv/xobaque/*/index.db*' \
        paraelectrobombus:/ \
        /mnt/server-backup/
    
    echo /var/lib/fail2ban/fail2ban.sqlite3
    sudo sqlite3_rsync paraelectrobombus:/var/lib/fail2ban/fail2ban.sqlite3 \
        /mnt/server-backup/var/lib/fail2ban/fail2ban.sqlite3
    
    for db in $(ssh paraelectrobombus ls /srv/thelounge/logs); do
        echo /srv/thelounge/logs/$db
        sudo sqlite3_rsync paraelectrobombus:/srv/thelounge/logs/$db \
            /mnt/server-backup/srv/thelounge/logs/$db
    done
    
    for site in $(ssh paraelectrobombus ls /srv/xobaque); do
        echo /srv/xobaque/$site/index.db
        sudo sqlite3_rsync paraelectrobombus:/srv/xobaque/$site/index.db \
            /mnt/server-backup/srv/xobaque/$site/index.db
    done
    
    echo /srv/gotosocial/sqlite.db
    sudo sqlite3_rsync paraelectrobombus:/srv/gotosocial/sqlite.db \
        /mnt/server-backup/srv/gotosocial/sqlite.db
    
    echo DONE

#Backup