Automating integrity_check in Python

You want a periodic job that answers one question — is this database still consistent? — and reports the answer to your monitoring stack as a metric and an exit code, without ever stalling the process that writes to the file. The naive version, a cron job that opens the live database and runs PRAGMA integrity_check, does exactly the wrong thing on a busy target: the full check reads every page and can hold a read lock for minutes, and on a write-heavy edge node that is enough to back up the writer and trip SQLITE_BUSY across the fleet. This page covers wiring the check into an automated health probe that stays off the write path — by running against a snapshot or inside a low-traffic window, bounding the check with a hard deadline, and translating the result into a signal a monitor can act on. It is the automation companion to Integrity Checking & Corruption Recovery, part of the Backup, Recovery & Data Integrity guide.

Figure — The probe path: open read-only, arm a deadline via the progress handler, run the check, and branch on the result into a healthy metric or an alert with a non-zero exit code.

A bounded SQLite integrity probe and its two monitoring outcomes A left-to-right probe flow. The job opens the database read-only, arms a wall-clock deadline through the progress handler so a long check can be aborted, and runs integrity_check. The result branches: a single ok row emits a healthy gauge and exits zero, while error rows or a deadline abort emit an alert and exit non-zero for the monitor. Open read-only mode=ro + deadline integrity_check bounded, abortable run Healthy gauge=1, exit 0 Alert gauge=0, exit != 0 ok errors / timeout

Diagnosis

Confirm two things before scheduling anything: that a full check actually stalls your writers, and how long it runs. Time the check on a representative copy so you can size a deadline and decide whether the live file can tolerate it. From the shell:

# time a full check on a copy so timing it never touches the live writer
time sqlite3 /var/backups/sensors.db 'PRAGMA integrity_check;'

If that wall-clock time is longer than the gap between your writes, running it on the live file will serialize writers behind the checker’s read lock. The second signal comes from the writers themselves: if a PRAGMA integrity_check on the production file coincides with a spike in SQLITE_BUSY returns, the check is the cause — a read lock during a full scan blocks the checkpoint the writer needs. The fast screen, PRAGMA quick_check, is the cheap alternative for a frequent probe, but it does not cross-verify index content, so it cannot be your only signal; the coverage difference is detailed in the parent guide.

Solution

Run the check read-only, arm a wall-clock deadline through the progress handler so a runaway check on a large file aborts cleanly, and reduce the result rows to a single verdict the monitor consumes. The progress handler fires every N virtual-machine instructions; returning a non-zero value from it aborts the current statement with an OperationalError, which is how you bound a check that has no native timeout:

import sqlite3
import sys
import time
import logging

logger = logging.getLogger("integrity_probe")


def probe(db_path: str, deadline_s: float = 20.0, full: bool = True) -> int:
    """Emit a health verdict. Returns 0 = healthy, 1 = corrupt, 2 = error/timeout."""
    conn = None
    try:
        # mode=ro: never create or write the file; a probe must be side-effect free.
        conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True, timeout=5.0)

        # arm a deadline: the handler runs every 10_000 VM ops and aborts the
        # statement (raising OperationalError) once the wall clock passes it.
        end = time.monotonic() + deadline_s
        conn.set_progress_handler(lambda: 1 if time.monotonic() > end else 0, 10_000)

        pragma = "integrity_check" if full else "quick_check"
        rows = [r[0] for r in conn.execute(f"PRAGMA {pragma};").fetchall()]

        if rows == ["ok"]:                       # success is exactly one 'ok' row
            emit_gauge("sqlite_integrity_ok", 1)
            logger.info("%s clean: %s", pragma, db_path)
            return 0
        emit_gauge("sqlite_integrity_ok", 0)
        logger.error("%s reported %d problem(s): %s", pragma, len(rows), rows[:5])
        return 1
    except sqlite3.OperationalError as e:
        # progress-handler abort lands here: check exceeded the deadline.
        emit_gauge("sqlite_integrity_ok", 0)
        logger.error("check aborted or locked on %s: %s", db_path, e)
        return 2
    except sqlite3.DatabaseError as e:
        # SQLITE_CORRUPT / SQLITE_NOTADB while merely opening: header is broken.
        emit_gauge("sqlite_integrity_ok", 0)
        logger.critical("cannot open %s for checking: %s", db_path, e)
        return 1
    finally:
        if conn:
            conn.close()


def emit_gauge(name: str, value: int) -> None:
    # replace with your metrics client (statsd/prometheus textfile/etc.)
    print(f"{name} {value}", file=sys.stderr)


if __name__ == "__main__":
    sys.exit(probe(sys.argv[1]))

The exit code is the integration surface: 0 healthy, 1 corrupt (or unopenable), 2 inconclusive because the check timed out or the file was locked. A monitor that treats only 0 as success will alert on both real corruption and a check that could not finish — which is what you want, because an inconclusive check is not a passing one. Point db_path at a backup snapshot on write-heavy targets; producing that snapshot consistently is covered in Online Backup API & Hot Copies.

Verification

Prove the probe reports correctly for both a clean and a damaged file before trusting it in production. Assert the healthy path first:

rc = probe("/var/backups/sensors.db")
assert rc == 0, f"probe flagged a database you believe is clean (rc={rc})"

Then confirm it actually catches corruption rather than silently passing. Fabricate a broken file by truncating a copy mid-page, and assert the probe rejects it with a non-zero code:

import shutil, os

shutil.copy("/var/backups/sensors.db", "/tmp/broken.db")
with open("/tmp/broken.db", "r+b") as f:
    f.truncate(os.path.getsize("/tmp/broken.db") - 2048)   # lop off a partial page
assert probe("/tmp/broken.db") != 0, "probe passed a deliberately corrupted file"

If the healthy file returns 0 and the truncated one returns non-zero, the exit-code contract holds and the monitor will see the difference. Confirm the deadline works too: set deadline_s very low against a large database and check the probe returns 2 rather than hanging.

Failure Modes & Gotchas

A full check on a huge live database stalls every writer. integrity_check reads the entire file under a read lock; on a multi-gigabyte database that lock outlives the writer’s patience and the checkpoint it depends on, so writes back up behind it and return SQLITE_BUSY. Never point the probe at a hot production file when the check runs longer than your inter-write gap. Run it against a backup copy, or confine it to a genuine low-traffic window — and keep quick_check as the frequent tripwire, reserving the full check for the snapshot.

Checking the live file and checking a snapshot answer different questions. A check against the live database validates the exact bytes writers are touching, including uncheckpointed WAL frames; a check against a snapshot validates the moment the copy was taken and nothing since. A snapshot keeps you off the write path but means the probe lags reality by one backup interval — acceptable for a health signal, misleading if you treat a clean snapshot as proof the live file is clean now. Be explicit about which one the metric represents.

Timeouts and locks masquerade as passes if you only check for raising. The connection timeout only bounds how long SQLite waits for a lock — it does nothing to cap a check that is running. Without the progress-handler deadline, a runaway full check on a growing file simply runs until something else kills the job, and a monitor keyed on “the script exited” reads that as fine. Bound the check explicitly and map “did not finish” to a non-zero exit, distinct from both healthy and corrupt.

The SQLite PRAGMA reference documents the check semantics, and the sqlite3 module documentation covers the progress handler used to bound the check.