Migrating Off RRDtool to SQLite Without Losing History

For two years my home sensors lived in RRDtool — one .rrd file per sensor, fixed-size round-robin archives, rrdtool fetch for everything. It worked, but it fought me on the one thing I actually wanted to do: ask questions of the data. You can’t JOIN RRD files. You can’t run a window function. You can’t easily say “show me every February where the mudroom dropped below 15°C.” So I migrated to SQLite in WAL mode (the same database the WAL post is about). This is how I did it without throwing away a year of history.

The trap with RRDtool: history is the product

RRDtool’s genius is also its cage. A round-robin archive has a fixed size — old data falls off the end. That’s fine for a dashboard that only ever shows “last 24 hours.” It’s fatal if you later decide you wanted last year. By the time I wanted to query history, my RRDs had already been silently discarding it on a rolling window. (And a single .rrd often holds several metrics — basement.rrd carries both temp and hum — so “one file per sensor” understates the count.)

The migration’s real job wasn’t “move the data.” It was “rescue what’s left before it ages out, and never let that happen again.” SQLite has no fixed-size archive — I keep the hourly rollup forever by choice, not by constraint.

The original plan (and why it changed)

My first MIGRATION_PLAN.json specified DuckDB as the unified store, with a split architecture: an accumulator_unified.py ingestor and a dashboard_backend.py query layer. Clean separation of concerns.

I didn’t build that. Two reasons, both measured:

1. DuckDB is overkill for one writer and one reader on a Pi. It shines for analytical workloads across many files; my workload is a trickle of inserts and some chart queries. SQLite WAL does that with zero extra processes.
2. The schema was overengineered. The plan’s unified table had temperature_c, humidity_pct, pressure_pa, energy_cost_usd as separate typed columns. I went with a simpler EAV-ish (ts, source, metric, value) row instead — adding a sensor became an INSERT, not a migration.

So the plan was DuckDB + two services. The reality is SQLite + the existing FastAPI layer I already had. The plan was right to force me to think about schema; the reality is what actually runs. I’m documenting both because the pivot is the lesson, not an embarrassment.

The migration script: read-only, idempotent, parallel-safe

migrate_legacy.py is a one-time importer. Its three guarantees:

• Read-only on legacy data. It only ever calls rrdtool fetch — never writes to an .rrd. If the migration explodes, the old system is untouched.
• Idempotent. Every insert is INSERT OR IGNORE. Run it twice, get the same result. This matters because I ran it while both systems were live.
• Resolution-aware. RRD files store multiple RRA resolutions (e.g. 5-minute and 1-hour). The script fetches at each resolution and writes to the matching rollup table (measurements_5m / measurements_1h).

The source mapping is explicit — 19 legacy RRD data-source mappings across 16 .rrd files ported (the heat loop alone contributes eleven tempN.rrd files):

# (rrd_path, ds_name, new_source, new_metric)
RRDS = [
    ("/home/searay/rrdtool/basement/basement.rrd", "temp", "basement", "temp"),
    ("/home/searay/rrdtool/basement/basement.rrd", "hum",  "basement", "hum"),
    ("/home/searay/rrdtool/jakob/esp32.rrd",       "temp", "jakob",    "temp"),
    ("/home/searay/rrdtool/jakob/esp32.rrd",       "hum",  "jakob",    "hum"),
    ("/home/searay/rrdtool/jakob/esp32.rrd",       "press","jakob",    "press"),
    ("/home/searay/rrdtool/power/pwr1.rrd",        "curpwr","power",   "watts"),
    ("/home/searay/rrdtool/heat/energy_summary.rrd","energy","heat",   "load_delta"),
    ("/home/searay/rrdtool/heat/energy_summary.rrd","cost",  "heat",    "cost_rate"),
]
for n in range(1, 12):   # temp1..temp11 (9-11 kept per user decision)
    RRDS.append((f"/home/searay/rrdtool/heat/temp{n}.rrd", "temp", "heat", f"temp{n}"))

Plus the shed node’s shed_v2.rrd — eleven data sources (humidity, outside temp, lux, battery, solar, soil, pressure, relay state…) mapped into the shed source. The full list is 19 RRDs; the busiest is the heat loop with eleven DS18B20 probes.

Parallel-run: the cutover you can trust

The key to not losing sleep: don’t cut over. Run both. My collector config still has the legacy mirror mode:

[shed]
# parallel-run: mirror rows from the legacy RRDtool system written by old shed.service.
# At cutover set mode = "serial" (and stop old shed.service).
enabled = true
mode = "mirror"          # mirror | serial
interval = 60
serial_port = "/dev/ttyS0"

While mode = "mirror", the new collector also writes what the old one writes. Both systems accumulate. I compared them row-for-row until I trusted the new one, then flipped to serial and stopped the old shed.service. No gap, no reconciliation panic, no “did I lose Tuesday?”

The old RRDTool processes are still on the box as of this writing — rrdtool/shed/shed.py and rrdtool/power/curpwrai.py are running. Not because they have to be, but because the parallel-run doctrine says: keep the old system until you’ve personally verified the new one, then archive — never delete. The RRD files are the historical reference; they stay read-only.

What the migration actually bought

Measured from the live database after cutover:

The headline: history that RRDtool was about to discard is now permanent. The 145,742 pre-cutover hourly rows reach back to May 2024 — two years I’d otherwise have watched age off the end of a round-robin archive.

What I’d tell past-me

• Migrate sooner. The longer RRDtool runs, the more history is already gone. The rescue window is the round-robin length, not “whenever.”
• Keep the importer idempotent and read-only. I re-ran migrate_legacy.py three times during testing. INSERT OR IGNORE meant I never had to wonder if a re-run doubled anything.
• Don’t over-architect the target. My DuckDB two-service plan was correct on paper and wrong in practice. SQLite + the API I already had shipped faster and runs lighter. Plan to think, then build the simpler thing that satisfies the plan’s intent.
• Parallel-run beats cutover every time. The mirror mode cost me a few duplicate writes for a week. That’s cheap insurance against a silent data gap.

All figures in this post were read from the running logger’s SQLite database and the live legacy RRDTool tree during writing. The migration script and config quoted here are the actual files in use.