Consistent SQLite online backups on dedicated servers

A backup job reports success, yet the latest batch of records is missing after a restore. This kind of failure is particularly easy to misdiagnose in file services that use SQLite in WAL mode. A successful file copy proves only that the bytes were transferred—not that the database is consistent. On a dedicated server that continuously records file indexes, task status, or backup manifests, creating a copy and proving that it is usable should be separate steps.

1. Understand the boundary between WAL and backups

In WAL mode, committed changes may remain in the -wal file until a checkpoint writes them back to the main database file. Running cp service.db backup.db while the application is active can miss those changes. Copying the main file, WAL file, and shared-memory file in sequence does not produce a snapshot of the same point in time either.

The online backup API reads consistent contents through a database connection instead of blindly copying files that are changing. It suits workloads that cannot pause writes, but still adds read and destination-write overhead. An offline backup is simpler when all connections can be closed. However, the write pause must cover background jobs, not just incoming user requests.

WAL is not an off-site backup, and mirroring is not a backup. A reliable copy must sit outside the source database’s failure domain and pass an independent read test.

OnuCloud dedicated servers come with two 1TB NVMe drives, each directly attached, with no hardware RAID. Writing the copy to the other drive can separate some read and write load, but both drives remain inside the same machine. This does not replace off-site storage.

2. Build a repeatable continuous-write test fixture

The following experiment works on Debian 12 and other environments with Python 3 and the standard-library sqlite3 module. First, check the module and create a separate temporary directory. All operations are confined to the test files: they neither touch a production database nor require changes to system services.

python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
umask 077
export LAB_DIR="$(mktemp -d)"
printf '%s\n' "$LAB_DIR"

The fixture uses two tables: events stores individual records, and totals stores a running total. Each transaction adds a record and increments the total together, so the copy must satisfy the rule that the sum of record amounts equals the running total. This is more meaningful than checking whether a file exists, and it demonstrates that structural integrity and business correctness are separate acceptance criteria.

The script below includes both a writer thread and the backup process. It uses the directory passed in from the previous step. Create a new directory for each repeat test to avoid mixing in an older fixture. Production databases are usually more complex: define their cross-table relationships and application-version requirements separately rather than applying this counting model unchanged.

3. Create and check a copy with the online backup API

The script waits for an initial commit, then runs the online backup while the writer thread continues. The destination starts as a temporary file. Only after the integrity check, foreign-key check, and business invariants all pass is it renamed to the final test backup. An exception at any step causes the script to fail without publishing a new copy.

python3 - <<'PY'
import os
import sqlite3
import threading
import time
from pathlib import Path

base = Path(os.environ["LAB_DIR"])
source = base / "source.db"
pending = base / "backup.pending.db"
published = base / "backup.db"

db = sqlite3.connect(source)
mode = db.execute("PRAGMA journal_mode=WAL").fetchone()[0]
if mode.lower() != "wal":
    raise RuntimeError("WAL mode unavailable")
db.executescript("""
CREATE TABLE events (
    n INTEGER PRIMARY KEY,
    amount INTEGER NOT NULL
);
CREATE TABLE totals (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    balance INTEGER NOT NULL
);
INSERT INTO totals VALUES (1, 0);
""")
db.close()

stop = threading.Event()
ready = threading.Event()
errors = []

def writer():
    conn = None
    try:
        conn = sqlite3.connect(source, timeout=5)
        while not stop.is_set():
            with conn:
                conn.execute("INSERT INTO events(amount) VALUES (1)")
                conn.execute(
                    "UPDATE totals SET balance = balance + 1 WHERE id = 1"
                )
            ready.set()
            time.sleep(0.01)
    except Exception as exc:
        errors.append(exc)
        ready.set()
    finally:
        if conn is not None:
            conn.close()

worker = threading.Thread(target=writer)
worker.start()
try:
    if not ready.wait(5):
        raise RuntimeError("Writer did not become ready")
    if errors:
        raise RuntimeError("Writer failed") from errors[0]
    src = sqlite3.connect(source, timeout=5)
    dst = sqlite3.connect(pending, timeout=5)
    try:
        src.backup(dst, pages=128, sleep=0.01)
    finally:
        dst.close()
        src.close()
finally:
    stop.set()
    worker.join()

if errors:
    raise RuntimeError("Writer failed") from errors[0]

check = sqlite3.connect(pending)
try:
    result = check.execute("PRAGMA integrity_check").fetchall()
    if result != [("ok",)]:
        raise RuntimeError("Integrity check failed")
    if check.execute("PRAGMA foreign_key_check").fetchall():
        raise RuntimeError("Foreign key check failed")
    count, amount = check.execute(
        "SELECT COUNT(*), COALESCE(SUM(amount), 0) FROM events"
    ).fetchone()
    balance = check.execute(
        "SELECT balance FROM totals WHERE id = 1"
    ).fetchone()[0]
    if not (count > 0 and count == amount == balance):
        raise RuntimeError("Business invariant failed")
finally:
    check.close()

os.replace(pending, published)
print(f"Verified rows: {count}")
print(published)
PY

pages=128 specifies the number of database pages processed per step, not a byte count. sleep is primarily used for busy retries; it is not a fixed throughput limiter. In production, record elapsed time and failure reasons, and impose a job-level deadline. Sustained heavy writes can prolong a backup. A timeout must count as a failure, not turn an incomplete file into a successful backup.

The rename here distinguishes a candidate from an accepted copy; it does not guarantee durability through a power loss. A production backup workflow must also define its durability boundary around file synchronization, directory synchronization, and the destination storage’s semantics.

4. Validate restores in isolation, not against the source database

The source database may have received more writes by the time the backup finishes, so the copy’s row count need not match a later query against the source. Check the copy’s own consistency, then assess its freshness against the backup window. An online backup is not continuous replication and does not automatically provide recovery to any arbitrary point in time.

Make a restore working copy from the accepted backup

The test backup is already closed, so it can be copied offline into another directory. The following code opens the restore working copy read-only to check its table structure and key relationships. Do not point the restore-test connection at source.db, or you will be validating the original database instead.

mkdir "$LAB_DIR/restore"
cp "$LAB_DIR/backup.db" "$LAB_DIR/restore/service.db"
python3 - <<'PY'
import os
import sqlite3
from pathlib import Path

path = Path(os.environ["LAB_DIR"]) / "restore" / "service.db"
conn = sqlite3.connect(path.as_uri() + "?mode=ro", uri=True)
try:
    print(conn.execute("PRAGMA integrity_check").fetchall())
    print(conn.execute("PRAGMA foreign_key_check").fetchall())
    print(conn.execute("""
        SELECT
            (SELECT COALESCE(SUM(amount), 0) FROM events),
            (SELECT balance FROM totals WHERE id = 1)
    """).fetchone())
finally:
    conn.close()
PY

The pass conditions are an integrity result of ok, no rows returned by the foreign-key check, and equal totals. This fixture has no foreign keys, so an empty foreign-key result proves only that the check can run. Real workloads should retain their actual constraints for validation.

Production acceptance also requires opening the working copy with a matching application version and reading the file index or task list, with email, callbacks, and automatic task execution disabled. Being able to open the database does not mean its external files are all present.

5. Turn a one-off experiment into backup acceptance criteria

Before putting the workflow into production, retain at least four kinds of evidence: the source database path and application version, backup start and end times, check results, and isolated-read results. Timestamps help assess data freshness; they should not be treated as an exact transaction cutoff. Workloads with a defined recovery-point requirement need a separate design for commit sequence numbers and recovery objectives.

Restrict access to the backup directory so that tokens, user details, or task parameters in the database are not exposed through the copy. After transferring a copy off-site, compare file checksums and repeat the restore test. Matching checksums prove only that the transfer was intact; they do not replace database acceptance checks.

On failure, keep the last verified copy and handle the candidate file separately. Do not overwrite a successful backup with an empty file. If the database references attachments, index shards, or other databases, an online backup of one database does not provide atomicity across those resources. That requires pausing application writes or coordinating them through a version manifest.

The final acceptance criterion is not “the copy command succeeded.” It is “the copy exists independently, structural checks pass, business relationships hold, and the application can read it in isolation.” Only when all four conditions are met does the backup have verifiable recovery value.

Frequently asked questions