Backup Strategies — Hot, Cold, Streaming

Three approaches, three tradeoffs

Production SQLite needs a backup story before it has real data. Three viable approaches:

Cold backup — stop writers, copy the file. Simplest, requires downtime.
Online backup — sqlite3 source.db ".backup target.db" or the C-level sqlite3_backup API. Works while writers are active; produces a consistent snapshot.
Streaming backup — Litestream / LiteFS / Turso replicate the WAL continuously to S3 / a peer / a managed service. Recovery to any point in the recent past.

For a personal product (Pippa, a side project), online backup to a NAS or to S3 once an hour covers 99% of disasters. For a multi-user product, Litestream gives you point-in-time recovery with minutes of data loss instead of hours.

Warning: Backups that aren't restored aren't backups. Before you trust any backup setup, do a full restore drill onto a different machine. The number of products that discover their backups were corrupt or incomplete only during a real incident is enormous.

Code

Online backup — daily cron·bash

#!/bin/bash
# Run daily; survives writers being active
set -euo pipefail

DB=/var/lib/myapp/myapp.db
DEST=/backups/myapp/$(date +%Y%m%d).db

sqlite3 "$DB" ".backup $DEST"
sqlite3 "$DEST" 'PRAGMA integrity_check'  # verify the copy
gzip "$DEST"

Litestream — continuous replication to S3·yaml

# /etc/litestream.yml — runs as a sidecar (launchd / systemd)
dbs:
  - path: /var/lib/myapp/myapp.db
    replicas:
      - type: s3
        bucket: myapp-backups
        path:   prod/myapp
        region: us-east-1
        retention: 168h        # 7 days of point-in-time recovery

Backup Strategies — Hot, Cold, Streaming

Three approaches, three tradeoffs

Code

External links

Exercise

Progress

Comments 0