Posts by dan_r
18 public posts under this name. There are no accounts here: names are free text and anyone can type any name, so these may not all be the same person.
| Post | Posted |
|---|---|
Backups. It's always backups. kettle, do the line count check. | by dan_r |
After the "is sqlite enough" thread I went and looked at my own readings db. Found 212 rows where temp_c was the text "nan". SQLite was happy to store that in a REAL column, because type affinity is m… | by dan_r |
Rowid is still insertion order for me, so that works. Did the conversion with a script that walks in rowid order, and I've kept the old column around in case. | by dan_r |
Export has two sets of readings for 01:00-02:00 on the night the clocks went back. The script writes datetime.now() into the db as text. Pretty sure I know the answer, but what's the right fix for the… | by dan_r |
A Map is fine for a single process. Sweep entries whose reset time has passed on a timer so it doesn't grow forever. If you ever run two processes you'll need something shared, but don't build that un… | by dan_r |
cp on a live WAL database can give you a file with the WAL half-applied. Use sqlite3 readings.db ".backup /mnt/backup/readings.db" or the VACUUM INTO command. Both produce a consistent snapshot. | by dan_r |
For whoever picks this up (probably me in a month): - export.py reads readings.db and writes readings.csv, runs from systemd nightly - garage sensor reports every 15 min, porch every 5, the CSV has bo… | by dan_r |
Query on 1.4M readings went from 2.8s to 11ms. The index has to be (sensor, ts) not (ts, sensor) for WHERE sensor = ? AND ts > ?. EXPLAIN QUERY PLAN tells you whether it says SCAN or SEARCH, which is … | by dan_r |
That was it. Cron started in my home directory, so ./readings.db was a brand new empty database. Absolute paths and a cd at the top of the script. Marking resolved. | by dan_r |
Trying to dump a small sqlite database from cron. Running the script in a terminal works, but the nightly output is a zero-byte file. The crontab line is: 15 2 * * * /home/me/bin/export-readings.sh Th… | by dan_r |
Box is a Pi Zero in a plastic junction box, on the shelf above the door. - BME280 on i2c, address 0x76, cable is 30cm which is about the limit before it gets flaky - writes to local sqlite, porch box … | by dan_r |
It's what I do. Test a restore or you don't have a backup. | by dan_r |
Not a web app, but my sensor box writes every few seconds and has for years. WAL mode, busy_timeout set, no problems. Use .backup or VACUUM INTO for backups and not cp. | by dan_r |
On points perhaps. My timers are still running. | by dan_r |
OnFailure= pointing to a unit that sends a mail. You have to write that unit. I'll give you that one, cron wins on this. | by dan_r |
And you remember it every time? Honest question. | by dan_r |
I had the same view until a sensor box was off for a night and missed its backup. Persistent=true runs the missed job at next boot. With cron you need anacron for that. | by dan_r |
For what it's worth I gave up and used a bare WROOM-32 on a small carrier with an MCP1700. It's fine for a garage sensor. | by dan_r |