Node-red & SQLite on a Pi Zero Two, read-only filesystem & automated db backups to another Pi

Share Your Projects
,
1

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.

image
image1183Ă—512 66.6 KB

[{"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.

image
image1153Ă—67 10.2 KB

"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
4 Likes
2

Thank you, I tried this and it works, even for me :grin:

A couple of points;

  1. It is probable that the password is required for the 3 commands accessing the remote device (it is with a Pi)
  2. sshfs will probably have to be installed (sudo apt install sshfs)

I had an SSD attached to the remote Pi that I used so I just created a backup directory on that so could skip the Create Directory & Create Mountpoint commands.

Another renown participant on the forum created a backup Node-RED flows etc. to Dropbox flow so I am going to have a look at adapting your flow to also run this backup to a local device.

3

Yes, until you have the public key safely lodged on the remote device, the ssh commands will prompt for the remote pi login password.

I couldn't recall if I did that or not!

Yes you can run some of the commands on the remote device rather than through ssh.

Backup to a local disk is much simpler, but since my SD is read-only I took the complicated route.