Infrastructure Migration Guide

This guide describes how to migrate the production deployment from one Raspberry Pi to another without corrupting the SQLite database.

The backend enables SQLite WAL mode in backend-api/src/dicechess_trainer/database.py, so the database may span multiple files:

• dicechess.db
• dicechess.db-wal
• dicechess.db-shm

Caution

Do not copy dicechess.db from a running container. In WAL mode, recent committed writes may still live in -wal, and SQLite coordination metadata lives in -shm. A file-only copy from a live system can produce an incomplete or inconsistent restore.

When to use this guide

Use this procedure when moving production from one device to another, for example:

• Raspberry Pi 3 to Raspberry Pi 4
• SD card replacement with a fresh OS image
• Host replacement after hardware failure

GitHub Container Registry authentication

The Docker image is stored in GHCR (ghcr.io/rabestro/dicechess-lab). When the CD workflow deploys automatically, it authenticates using a short-lived GITHUB_TOKEN issued by GitHub Actions. That token is not available on a host shell, so any manual docker compose command on a fresh device will fail with unauthorized unless you log in first.

The PAT required for GHCR access is saved in Vaultwarden under the entry GitHub – read:packages PAT (dicechess RPi).

Tip

To check whether the package is public (no login needed), visit https://github.com/rabestro?tab=packages and inspect the visibility badge on the dicechess-lab package. If it shows Public, skip the login step below.

One-time login on a new device

Retrieve the PAT from Vaultwarden, then run:

echo "<PAT_FROM_VAULTWARDEN>" | docker login ghcr.io --username rabestro --password-stdin

Docker saves the credential to ~/.docker/config.json; subsequent docker compose pull and docker compose up commands will use it automatically.

Caution

Never pass the PAT as a plain argument (--password <token>); use --password-stdin so the token does not appear in shell history.

Production data location

The deployed stack syncs docker-compose.yaml into ~/apps/dicechess-trainer/ and mounts:

./data:/app/data

That means the production database files live on the host under:

~/apps/dicechess-trainer/data/

Typical files are:

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

Safe migration procedure

1. Prepare the target device

   • Install Docker and Docker Compose.
   • Log in to GHCR using the PAT from Vaultwarden (see GHCR authentication above):

   echo "<PAT_FROM_VAULTWARDEN>" | docker login ghcr.io --username rabestro --password-stdin

   • Register the GitHub Actions runner with the self-hosted and rpi4 labels so that future CD deployments can target the new device automatically.
   • Create the deployment directory:

   mkdir -p ~/apps/dicechess-trainer

2. Stop the application on the source device

   Shut the stack down before copying any SQLite files:

   cd ~/apps/dicechess-trainer
   docker compose down

   This is the critical safety step for WAL databases. The application must not be writing while the copy is taken.

3. Copy the database files as one set

   Copy dicechess.db together with dicechess.db-wal and dicechess.db-shm.

   rsync --archive --verbose ~/apps/dicechess-trainer/data/ pi4:~/apps/dicechess-trainer/data/

   If the source shutdown already checkpointed the WAL and removed the sidecar files, copy the whole data/ directory anyway. The important rule is: never copy only dicechess.db from a live WAL database.

4. Back up any existing target data before overwriting

   On the new device, if a previous deployment already created database files, back them up first:

   mv ~/apps/dicechess-trainer/data ~/apps/dicechess-trainer/data.backup.$(date +%Y%m%d-%H%M%S)
   mkdir -p ~/apps/dicechess-trainer/data

5. Start the stack on the target device

   After the copy completes, start the deployment:

   cd ~/apps/dicechess-trainer
   docker compose up --detach

Optional integrity checks

After startup on the target host, verify that the container is running and the application logs look healthy.

cd ~/apps/dicechess-trainer

docker compose ps
docker compose logs --tail 50 web | cat

If you have SQLite CLI access, you can also inspect the migrated database directly:

sqlite3 ~/apps/dicechess-trainer/data/dicechess.db "PRAGMA journal_mode;"
sqlite3 ~/apps/dicechess-trainer/data/dicechess.db "PRAGMA foreign_keys;"

Expected values:

• journal_mode -> wal
• foreign_keys -> 1

Recommended rollback plan

Before switching traffic or disabling the old runner, keep:

• the old Raspberry Pi powered off but intact,
• a timestamped backup of the target data/ directory,
• the copied docker-compose.yaml and .env files.

If anything looks wrong after migration, stop the new stack, restore the backup directory, and repeat the copy from the original source data.

Practical rules to remember

• Always stop the container before copying the database.
• Treat dicechess.db, dicechess.db-wal, and dicechess.db-shm as one logical unit.
• Copy the whole data/ directory instead of cherry-picking files.
• Start the target container only after the copy has fully completed.