SQLite .backup and VACUUM INTO While the App Runs

Stopping the app to copy app.db is not an operations plan. SQLite can produce a consistent second file while connections stay open. Two primitives do that work: the online backup API, exposed in the CLI as .backup, and VACUUM INTO, which writes a compacted database to a new path. Neither is a byte-for-byte cp of a file that is still being mutated.

This is about using those primitives from the running application: a pre-deploy snapshot, an admin export, a restore rehearsal that does not take the site down. A separate backups note covers scheduled pipelines. Here the unit is one live process and one extra file.

Pick the primitive from the goal

.backup copies pages from a source connection to a destination connection. It retries pages that change mid-copy. The destination is a usable SQLite file. It is not compacted. Freelist pages and bloat come along. Use it when you want a faithful copy quickly and you will VACUUM later, or never.

VACUUM INTO writes a new database that contains only live content. It is the compact snapshot. Use it when the export should be small, when you will ship the file off the host, or when you want to clone a dataset for staging. It needs room for the new file plus the original. On a 4 GB database, plan for another 4 GB free on the same filesystem.

Neither command should target a path the app might open by accident. Write to /var/lib/myapp/snapshots/ with a timestamp in the name. Never overwrite app.db in place.

VACUUM INTO '/var/lib/myapp/snapshots/app-2026-06-18.db';

sqlite3 /var/lib/myapp/data/app.db ".backup '/var/lib/myapp/snapshots/app-live.db'"
sqlite3 /var/lib/myapp/snapshots/app-live.db "PRAGMA integrity_check;"

If integrity_check does not print ok, discard the file. Do not upload it. Do not restore it.

From the app process, not from a second personality

The cleanest snapshot is one the app takes on a connection it already owns. A second CLI process works, but it must use a busy timeout and the same WAL files. An in-process snapshot avoids a surprise lock from a cron overlapping a migration.

import os
import sqlite3
from datetime import datetime, timezone

SNAP = '/var/lib/myapp/snapshots'

def snapshot(live_path):
    os.makedirs(SNAP, exist_ok=True)
    stamp = datetime.now(timezone.utc).strftime('%Y%m%dT%H%M%SZ')
    dest = os.path.join(SNAP, 'app-' + stamp + '.db')
    conn = sqlite3.connect(live_path, timeout=5.0)
    conn.execute('PRAGMA busy_timeout = 5000')
    conn.execute("VACUUM INTO '" + dest.replace("'", "''") + "'")
    conn.close()
    check = sqlite3.connect('file:' + dest + '?mode=ro', uri=True)
    row = check.execute('PRAGMA integrity_check').fetchone()
    check.close()
    if row[0] != 'ok':
        os.remove(dest)
        raise RuntimeError('snapshot failed integrity_check')
    return dest

VACUUM INTO from the live connection will not block readers in WAL mode the way a full exclusive VACUUM of the original file would. It still does real I/O. Run it on a maintenance path (an admin button, ExecStartPre of a deploy, a systemd timer) rather than on every HTTP request.

Watch free space. High-Speed SSD on a Netbay VPS in Lucknow is finite. A snapshot that fills the disk will take the live database with it. Keep one or two local snapshots and push the rest off-box. Size the snapshot directory with a hard cap in the timer so a stuck job cannot fill the volume overnight.

Restore beside the original

Restoring does not mean replacing the inode the app has open. SQLite connections pin a file. Overwriting app.db while the process runs is how you get SQLITE_CORRUPT. Restore to a new path, point a second process at it, confirm, then restart the app with APP_DB set to the restored file, or stop the app and atomically rename.

install -o myapp -g myapp -m 640 /var/lib/myapp/snapshots/app-good.db /var/lib/myapp/data/app.restored.db
APP_DB=/var/lib/myapp/data/app.restored.db /usr/local/bin/myapp

When the rehearsal looks right, stop the unit, swap the files, start the unit. Checkpoint first if you need the live WAL folded in: PRAGMA wal_checkpoint(TRUNCATE) on a quiet connection, then snapshot. A snapshot taken while a huge WAL exists is still consistent; it is just larger work.

Do not cp app.db, app.db-wal, and app.db-shm as three separate copies at three different times. That is the torn-page problem .backup and VACUUM INTO exist to avoid.

Snapshot to a new file, verify it, restore beside the original, then swap. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.