I have used this flow to explore several ideas:
- SQLite and Node-red on a read-only filesystem.
- SSH connection using public/private keys.
- SSHFS to mount a directory on a remote device.
- A delay node as a simple SQL queue rather than db transactions.
- Template nodes to build short Bash scripts and SQL queries within Node-red.
- Catching and reporting errors.
Background
I have a Raspberry Pi Zero with Node-red and SQLite database which retrieves and stores data from an external API for display on a dashboard.
There are about 2 SQL INSERTs per second, raising concern about SD card life.
For long term resilience, this Pi has the "overlay filesystem" enabled, meaning that Node-red and SQLite are memory resident; the sd card is read-only.
It performs surprisingly well for a computer with 512MB memory.
The flow below performs automated backups to another Pi on the same LAN via an SSHFS mount.
[{"id":"077a14765e9a876a","type":"group","z":"0c0663cd2954f132","name":"Mount sshfs, queue SQL to create backup file, unmount","style":{"label":true,"fill":"#e3f3d3","fill-opacity":"0.5","stroke":"#addb7b"},"nodes":["9b76891ef122f983","4bda68f348d313fa","eeb07e2caf90c0a9","cf31a81f2ac5879c","6fb766a2d7cfef94","95d3ee14cbda4eb9","ad4323f30a879e3e","e81fe38a0701ac84","71a32a35ccd6d8a9","39b55f9c55d4bec1","08a59785214a2c09","a4f7fadc357f33c0","8012edce77603e63","d7d6b52188c25b93","d2435e0dc656cc93","f890adff242ba9ca","53ce68cf380bb245","7e29dcc44d12ccaf","2c078f51a39dd1e0"],"x":14,"y":19,"w":852,"h":362},{"id":"9b76891ef122f983","type":"inject","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Init","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":60,"wires":[["53ce68cf380bb245"]]},{"id":"4bda68f348d313fa","type":"link in","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Single point of db access","links":[],"x":150,"y":120,"wires":[["f890adff242ba9ca"]],"l":true},{"id":"eeb07e2caf90c0a9","type":"link out","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"dboutput","mode":"return","links":[],"x":755,"y":120,"wires":[]},{"id":"cf31a81f2ac5879c","type":"sqlite","z":"0c0663cd2954f132","g":"077a14765e9a876a","mydb":"060b0a6827e9c5e8","sqlquery":"msg.topic","sql":"","name":"","x":610,"y":120,"wires":[["eeb07e2caf90c0a9"]]},{"id":"6fb766a2d7cfef94","type":"inject","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Backup","props":[{"p":"payload"}],"repeat":"21600","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"go","payloadType":"str","x":120,"y":180,"wires":[["71a32a35ccd6d8a9"]]},{"id":"95d3ee14cbda4eb9","type":"exec","z":"0c0663cd2954f132","g":"077a14765e9a876a","command":"","addpay":"payload","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"","x":430,"y":180,"wires":[[],["a4f7fadc357f33c0"],["e81fe38a0701ac84"]]},{"id":"ad4323f30a879e3e","type":"template","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"umount sshfs","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"fusermount -u {{{flow.backupMountPoint}}} || exit 1 ","output":"str","x":290,"y":300,"wires":[["39b55f9c55d4bec1"]]},{"id":"e81fe38a0701ac84","type":"switch","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"OK?","property":"payload.code","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"}],"checkall":"true","repair":false,"outputs":1,"x":110,"y":240,"wires":[["7e29dcc44d12ccaf"]]},{"id":"71a32a35ccd6d8a9","type":"template","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"mount sshfs","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"fusermount -u {{{flow.backupMountPoint}}} 2>/dev/null\nsshfs pi@{{{flow.remoteServerIP}}}:{{{flow.remoteServerDirectory}}} {{{flow.backupMountPoint}}} || exit 1 ","output":"str","x":290,"y":180,"wires":[["95d3ee14cbda4eb9"]]},{"id":"39b55f9c55d4bec1","type":"exec","z":"0c0663cd2954f132","g":"077a14765e9a876a","command":"","addpay":"payload","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"","x":430,"y":300,"wires":[[],["d7d6b52188c25b93"],[]]},{"id":"08a59785214a2c09","type":"change","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Notify SQLite error","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"SQLite backup: \" & error.message","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":340,"wires":[["2c078f51a39dd1e0"]]},{"id":"a4f7fadc357f33c0","type":"change","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Notify mount error","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"SQLite backup: \" & payload","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":180,"wires":[["2c078f51a39dd1e0"]]},{"id":"8012edce77603e63","type":"catch","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"DB errors","scope":["cf31a81f2ac5879c"],"uncaught":false,"x":100,"y":340,"wires":[["ad4323f30a879e3e","08a59785214a2c09"]]},{"id":"d7d6b52188c25b93","type":"change","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Notify umount error","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"SQLite backup: \" & payload","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":300,"wires":[["2c078f51a39dd1e0"]]},{"id":"d2435e0dc656cc93","type":"link call","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"To database","links":["4bda68f348d313fa"],"linkType":"static","timeout":"30","x":490,"y":240,"wires":[["ad4323f30a879e3e"]]},{"id":"f890adff242ba9ca","type":"delay","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Crude SQL queue","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"4","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":390,"y":120,"wires":[["cf31a81f2ac5879c"]]},{"id":"53ce68cf380bb245","type":"change","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Setup FILENAMES, DIRECTORIES","rules":[{"t":"set","p":"backupFileName","pt":"flow","to":"backup.db","tot":"str"},{"t":"set","p":"backupMountPoint","pt":"flow","to":"/media/dbbackups","tot":"str"},{"t":"set","p":"remoteServerIP","pt":"flow","to":"192.168.1.26","tot":"str"},{"t":"set","p":"remoteServerDirectory","pt":"flow","to":"/home/pi/dbbackups","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":60,"wires":[[]]},{"id":"7e29dcc44d12ccaf","type":"template","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"SQL to msg.topic","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"VACUUM INTO '{{{flow.backupMountPoint}}}/{{{flow.backupFileName}}}'","output":"str","x":310,"y":240,"wires":[["d2435e0dc656cc93"]]},{"id":"2c078f51a39dd1e0","type":"debug","z":"0c0663cd2954f132","g":"077a14765e9a876a","name":"Errors","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":240,"wires":[]},{"id":"060b0a6827e9c5e8","type":"sqlitedb","db":"/home/pi/demo.db","mode":"RWC"}]
There is a parallel flow to retrieve the latest backup at system startup.
The flow uses VACUUM INTO 'backup.db' to create the backup. This will fail if backup.db already exists, so I made a simple mechanism on the remote Pi to move it to another directory.
"VACUUM" implies some trash removal so the backup file might be slightly smaller than the original. It is a valid SQLite database so you can connect sqlite3 to it without any import process.
Thanks to @Buckskin for suggesting VACUUM INTO for this application.
Prerequisites
ssh has to be enabled on both (?) Pies, sshfs has to be installed with sudo apt install.
So that Node-red can mount a remote directory using sshfs, I had to setup key based ssh connection:
(Once only commands, all run on the Node-red & SQLite device)
REMOTEIP=192.168.1.26
#Generate public/private key pair
ssh-keygen -t rsa (Accept the defaults)
#Create a directory on remote pi for the key
ssh pi@$REMOTEIP "mkdir -p .ssh"
#Upload Generated Public Keys to the Remote Server
cat ~/.ssh/id_rsa.pub | ssh pi@$REMOTEIP "cat >> ~/.ssh/authorized_keys"
#Set Permissions on Remote server
ssh pi@$REMOTEIP "chmod 700 ~/.ssh; chmod 640 ~/.ssh/authorized_keys"
# Create a directory to backup to
ssh pi@$REMOTEIP "mkdir -p dbbackups"
ssh pi@$REMOTEIP "mkdir -p dbbackups/latestbackup"
# Create a mountpoint on this device
sudo mkdir /media/dbbackups
sudo chmod 777 /media/dbbackups
To get the best use of the 512MB memory I installed zram to move swap space into memory and compress it.
I followed the guide at https://pimylifeup.com/raspberry-pi-zram/
I also have /tmp and /var/log set as temporary file systems in /etc/fstab. Not sure this makes any difference with the entire filesystem memory resident.
tmpfs /tmp tmpfs defaults,noatime 0 0
tmpfs /var/log tmpfs defaults,noatime,size=16m 0 0
