Streaming Hot Backups from Edge Devices

A field-deployed edge node — a solar-powered sensor gateway, an agricultural telemetry box on a cellular modem, a rail-side monitor reachable only over a flaky LTE link — cannot afford the naive backup flow of “copy the database into memory, POST it to the cloud.” The device has a few hundred megabytes of RAM shared across the whole platform, a write path that must never miss a sensor sample, and a network link that drops for seconds or minutes at a time. Reading the whole database into a buffer to upload it can OOM the process; holding a long read transaction to copy it can stall the writer and drop samples; and a single failed upload wastes the cell budget re-sending bytes you already sent. This page addresses the exact shape of that problem — getting a consistent copy off the device without disturbing the sensor path or exhausting RAM — as a companion to Online Backup API & Hot Copies in the Backup, Recovery & Data Integrity guide. The technique is to decouple the two hard problems: use the online Backup API to spool a consistent image to a local file with a bounded footprint, then stream that finished file to the collector resumably, so a link drop never touches the live database.

Diagnosis

Confirm you actually have the edge-streaming problem and not a simpler one before adding throttling machinery. Three conditions must all hold: the write path is latency-sensitive, RAM is scarce enough that buffering the database is unsafe, and the uplink is unreliable. Check each concretely.

First, the write path. If a sensor task inserts on a fixed cadence and a stalled insert means a lost sample, the backup must never hold the source lock long enough to block a commit. Confirm the source is in WAL mode (so the backup reads without blocking writers at all) and that a busy_timeout is set so any contention waits rather than fails:

PRAGMA journal_mode = WAL;    -- reader (the backup) never blocks the sensor writer
PRAGMA busy_timeout = 5000;   -- ms; a busy step waits, it does not drop the sample
PRAGMA page_count;            -- total pages; × page_size ≈ the spool file size to plan for

Second, the memory ceiling. On a cgroup-limited or simply small target, reading the database into a Python bytes object to upload doubles its size in RAM. Compare the database size against available memory — if the file is a meaningful fraction of free RAM, in-memory buffering is disqualified and you must stream from a file descriptor in fixed-size chunks.

Third, the link. If uploads fail partway with connection resets, a whole-file re-POST wastes the data budget. The signature is an upload that restarts from byte zero after every drop. The fix is a resumable transfer over a spooled file, which also means the backup copy and the upload are cleanly separated in time — the database is untouched by the time the flaky part begins.

Decoupling a throttled hot copy from a resumable upload on an edge device A left-to-right pipeline. The sensor writer commits into the live WAL-mode database. A throttled Backup API copy, using tiny page batches and a sleep, spools a consistent image to a local file without stalling the writer. That spool file is then uploaded in fixed-size chunks over an intermittent link, resuming from the last offset after a drop, and verified with integrity_check on arrival. Sensor writer WAL, fixed cadence commit Throttled copy pages=16, sleep=.3 yields to the writer spool Spool file consistent image chunks Upload resume by offset link drops → resume from last offset

Solution

Separate the two concerns in code. First, spool a consistent copy locally with an aggressively throttled backup() — tiny pages and a real sleep so the sensor writer always wins the lock. Then stream the finished spool file to the collector in fixed-size chunks, so peak memory is one chunk regardless of database size, resuming from a byte offset the server reports.

import os
import sqlite3
import logging

logger = logging.getLogger("edge_stream_backup")

CHUNK = 64 * 1024        # 64 KiB upload chunk: peak memory is one chunk, not the whole file


def spool_consistent_copy(src_path: str, spool_path: str) -> None:
    """Throttled hot copy to a local file that never stalls the sensor writer."""
    src = sqlite3.connect(src_path, timeout=30.0)   # SECONDS; a busy step waits, no dropped sample
    dst = sqlite3.connect(spool_path, timeout=30.0)
    try:
        def _yield_to_writer(status, remaining, total):
            # remaining rising back toward total means a sensor commit forced a restart;
            # that is acceptable here — correctness first, throughput second.
            logger.debug("spool %d/%d pages", total - remaining, total)

        # pages=16 (~64 KiB per step at a 4 KiB page) + sleep=0.3 s makes the copy a
        # background trickle: each step holds the read lock only briefly, then yields
        # 300 ms so the fixed-cadence writer commits without contention.
        src.backup(dst, pages=16, progress=_yield_to_writer, sleep=0.30)

        result = dst.execute("PRAGMA integrity_check;").fetchone()[0]
        if result != "ok":
            raise RuntimeError(f"spool integrity_check failed: {result!r}")
    except sqlite3.Error:
        logger.exception("spool copy failed for %s", src_path)
        raise
    finally:
        dst.close()
        src.close()


def stream_upload(spool_path: str, put_chunk, resume_offset: int = 0) -> int:
    """Upload the spool file from resume_offset in CHUNK-sized reads. Returns bytes sent.

    put_chunk(offset, data) -> None must raise on failure so we stop and keep the spool
    file for the next attempt; peak memory stays at one CHUNK."""
    size = os.path.getsize(spool_path)
    sent = 0
    with open(spool_path, "rb") as f:
        f.seek(resume_offset)                        # skip bytes the collector already has
        offset = resume_offset
        while offset < size:
            data = f.read(CHUNK)                      # bounded read: never loads the whole DB
            if not data:
                break
            put_chunk(offset, data)                  # raises on a dropped link -> caller retries
            offset += len(data)
            sent += len(data)
    return sent

The division of labour is the whole point. spool_consistent_copy touches the live database and finishes fast because it copies to fast local storage; the moment it returns, the database is out of the picture. stream_upload operates only on the immutable spool file, so a link drop mid-upload costs nothing on the database side — you retry from resume_offset (the byte count the collector acknowledges) with peak memory pinned at one 64 KiB chunk. The throttling values (pages=16, sleep=0.30) come straight from the high-write row of the decision table in the parent Online Backup API & Hot Copies topic; on a device whose sensor cadence is slower you can raise pages to shorten the spool time.

Verification

Prove three things: the writer never stalled, the spool is consistent, and the upload is byte-exact and resumable.

Confirm the spooled copy is a well-formed, queryable database before it leaves the device:

spool_consistent_copy("/var/lib/telemetry/sensors.db", "/var/spool/backup.db")
check = sqlite3.connect("/var/spool/backup.db")
assert check.execute("PRAGMA integrity_check;").fetchone()[0] == "ok"
assert check.execute("PRAGMA page_count;").fetchone()[0] > 0, "empty spool"
check.close()

Confirm the writer kept making progress during the copy by sampling the source row count before and after — a healthy run shows new rows landed while the backup was running, proving the copy did not block commits:

before = src_conn.execute("SELECT count(*) FROM readings;").fetchone()[0]
# ... run spool_consistent_copy in a background thread ...
after = src_conn.execute("SELECT count(*) FROM readings;").fetchone()[0]
assert after >= before, "writer made no progress — the copy stalled the sensor path"

Confirm resumability by simulating a drop: upload half the chunks, record the offset, then resume — the total bytes sent across both passes must equal the file size, with no chunk sent twice.

Failure Modes & Gotchas

A link drop mid-transfer must never restart the copy. The most common design mistake is coupling the copy to the upload — running backup() straight into an HTTP body stream. When the link drops, the whole copy restarts, re-reading the live database and re-contending with the sensor writer for every retry. Spooling to a local file first breaks that coupling: the database is read exactly once, and all the retry pain lives on the immutable file. If local storage genuinely cannot hold a second copy, shrink the database footprint first (checkpoint and VACUUM in a window) rather than streaming live.

The WAL grows during a long backup and eats the storage you need for the spool. On a device that writes continuously, a slow throttled copy means the WAL keeps accumulating frames the whole time — and the spool file is competing for the same small partition. Two safeguards: checkpoint immediately before spooling so the WAL starts small, and pre-flight free space for both the spool file and expected WAL growth. Size the checkpoint cadence per Optimizing wal_autocheckpoint for Continuous Logging so the WAL cannot balloon during the copy window.

SD-card wear turns a frequent backup into a hardware failure. Writing a full spool copy every few minutes to the same cheap flash card multiplies write-amplification and burns through erase cycles. Back up on the longest cadence your recovery objective tolerates, write the spool to a different device or partition from the live database when possible, and prefer incremental stepping over repeated full copies where the recovery window allows — the approach in Incremental Backups with the SQLite Backup API. Match the whole scheme to the sensor-write tuning in Configuring busy_timeout for IoT Sensor Writes.

For the underlying copy contract, see the official SQLite Backup API documentation.