sqlite

SQLite backup for WAL database

mkdir -p /opt/app/backups

cat > /usr/local/bin/app-backup.sh <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

APP_DIR="/opt/app"
DB="$APP_DIR/data/dbname.sqlite"
BACKUP_DIR="$APP_DIR/backups"
RETENTION_DAYS=14
STAMP="$(date +%F_%H%M%S)"

mkdir -p "$BACKUP_DIR"

# Consistent hot backup (WAL-safe). Requires sqlite3 on the host.
if [ -f "$DB" ]; then
  sqlite3 "$DB" ".backup '$BACKUP_DIR/db-$STAMP.sqlite'"
  gzip -f "$BACKUP_DIR/db-$STAMP.sqlite"
else
  echo "WARNING: database not found at $DB" >&2
fi

# Prune anything older than retention window.
find "$BACKUP_DIR" -type f -name 'db-*.sqlite.gz' -mtime +$RETENTION_DAYS -delete

echo "Backup complete: db-$STAMP.sqlite.gz"
EOF

chmod 700 /usr/local/bin/app-backup.sh
chmod 700 /opt/app/backups

cat > /etc/systemd/system/app-backup.service <<'EOF'
[Unit]
Description=App SQLite
After=docker.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/app-backup.sh
EOF

cat > /etc/systemd/system/app-backup.timer <<'EOF'
[Unit]
Description=Run App backup daily

[Timer]
OnCalendar=*-*-* 03:30:00
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target
EOF

systemctl daemon-reload
systemctl enable --now app-backup.timer