Backup Production Database

The production deployment stores SQLite data on the Raspberry Pi host in the bind-mounted data/ directory.

For example, on the current Pi setup:

~/apps/dicechess-trainer/data/dicechess.db

Because the app runs SQLite in WAL mode, the safest online backup method is to use SQLite’s built-in .backup command. This creates a consistent snapshot even while the app is running.

Tip

Prefer the online .backup workflow below whenever possible. Copying only dicechess.db directly while the app is live can miss uncheckpointed WAL data.

Recommended: Online backup with sqlite3 .backup

1. SSH into the Raspberry Pi

   ssh jegors@192.168.10.9

2. Go to the deployment directory

   cd ~/apps/dicechess-trainer

3. Create a timestamped backup directory

   mkdir -p backups
   BACKUP_TS="$(date +%Y%m%d-%H%M%S)"
   BACKUP_PATH="backups/dicechess-${BACKUP_TS}.db"

4. Create a consistent SQLite snapshot

   sqlite3 data/dicechess.db ".backup '${BACKUP_PATH}'"

5. Verify the backup exists

   ls -lh "$BACKUP_PATH"
   sqlite3 "$BACKUP_PATH" "PRAGMA integrity_check;"

   Expected integrity-check result:

   ok

Copy the backup to your local machine

Run these commands on your local machine, not on the Raspberry Pi.

scp

mkdir -p ~/backups/dicechess
scp jegors@192.168.10.9:~/apps/dicechess-trainer/backups/dicechess-YYYYMMDD-HHMMSS.db ~/backups/dicechess/

rsync

mkdir -p ~/backups/dicechess
rsync -avz --progress jegors@192.168.10.9:~/apps/dicechess-trainer/backups/dicechess-YYYYMMDD-HHMMSS.db ~/backups/dicechess/

After copying, you can validate the local file:

sqlite3 ~/backups/dicechess/dicechess-YYYYMMDD-HHMMSS.db "PRAGMA integrity_check;"

One-liner workflow

If you want a compact flow on the Raspberry Pi:

cd ~/apps/dicechess-trainer
mkdir -p backups
BACKUP_TS="$(date +%Y%m%d-%H%M%S)"
BACKUP_PATH="backups/dicechess-${BACKUP_TS}.db"
sqlite3 data/dicechess.db ".backup '${BACKUP_PATH}'"
sqlite3 "$BACKUP_PATH" "PRAGMA integrity_check;"
echo "$BACKUP_PATH"

Then copy the printed file path from your local machine with scp or rsync.

Manual backup + staging refresh via GitHub Actions

Use the workflow .github/workflows/ops-backup-stage-refresh.yaml when you want a single-button run from GitHub Actions.

Current mode is manual only (workflow_dispatch). You can trigger it via the GitHub UI or straight from your local terminal.

Option A: Trigger from local machine (via mise)

If you have configured mise, the easiest way is to run our pre-configured task:

mise run db:backup-remote

This wraps the GitHub CLI and transparently launches the ops-backup-stage-refresh.yaml workflow for you.

Option B: Trigger via GitHub Web UI

1. Open Actions → Ops: Backup and Stage Refresh.

2. Click Run workflow and choose inputs:

   • retention_days (default 30)
   • run_stage_refresh (true to restore latest backup to staging)
   • notify_telegram (true to send Telegram alerts)

3. Wait for backup-production on rpi4:

   • runs sqlite3 .backup
   • validates with PRAGMA integrity_check;
   • compresses to .db.gz
   • applies retention cleanup

4. If enabled, refresh-staging on rpi3 downloads the backup artifact and restores it to staging.

Telegram configuration

The workflow uses two repository secrets:

• TELEGRAM_BOT_TOKEN
• TELEGRAM_CHAT_ID

Create and configure them as follows.

1. Create a bot with @BotFather (/newbot) and copy the token.

2. Send at least one message to your bot (for example /start).

3. Fetch your chat id:

   curl -s "https://api.telegram.org/bot<TELEGRAM_BOT_TOKEN>/getUpdates"

   Find message.chat.id in the response and copy that value.

4. In GitHub, open Settings → Secrets and variables → Actions → New repository secret and add:

   • TELEGRAM_BOT_TOKEN=<token from BotFather>
   • TELEGRAM_CHAT_ID=<chat id from getUpdates>

If notify_telegram=true and secrets are configured, the workflow sends success/failure messages for:

• production backup result
• staging refresh result (when enabled)

Fallback: stopped-app file copy

If sqlite3 is unavailable on the Pi, stop the app first and copy the database files only while the service is down.

cd ~/apps/dicechess-trainer
docker compose stop web db-admin
cp data/dicechess.db backups/dicechess-stopped-copy.db
docker compose start web db-admin

Caution

Do not rely on copying a live WAL-mode database file without .backup or without stopping the app. The main dicechess.db file alone may not include the latest committed data still present in dicechess.db-wal.

Optional: copy the raw live files for forensic inspection

If you specifically want the raw live SQLite files, copy all three files together:

scp jegors@192.168.10.9:~/apps/dicechess-trainer/data/dicechess.db* ~/backups/dicechess/raw/

This is useful for low-level inspection, but it is not the preferred backup format for restore workflows.

Restore notes

To inspect a backup locally:

sqlite3 ~/backups/dicechess/dicechess-YYYYMMDD-HHMMSS.db ".tables"

To replace the production database from a backup, stop the app first and keep a copy of the current data/ directory before overwriting anything.