Advice on copying data to and from a remote pi

Good Morning everyone.

I am in the planning stage and need some advice.

I have some remote raspberry's and am looking to back up one sqlite database from them every night, additionally I have two other databases I will need to up loaded when they change.

Does anyone have a setup like this in node-red or recommend a way to do this?

I have toyed with Telegram, it will send messages, but does not seem to send files, at least for me, even though I have selected the file option. When a file is sent, both the file and message do not arrive, but if just a message is sent, it arrives every time.

rsync, works with ssh, can handle large files and can be run from node-red via the exec command.

Problem with backing up sqlite database with a "normal" backup script like rsync is that the files are normally opened by Node-red (or other) application, copying the file when it's open will give a faulty backup file. If you ever have to restore the file sqlite will say that the file is corrupted and can't be used. Or with some effort you can repair the file.

The only way to backup a sqlite file correct is with the backup optie from sqlite command line.
I'm using it myself in a exec node with this arguments for example: /home/pi/dbs/toon.sqlite ".backup /home/pi/dbs/toon.sqlite.backup" to backup the toon database.
After making the backup file it will be zipped with the dsm node and uploaded to dropbox with the dropbox node.

2 Likes

Can you use the backup option while the database is still running/live, or do you have to shut it down, do the backup then bring it back up, seems crazy if it has to be taken down.

According to the top rated answer of stack overflow question:

you can do that as it says:

It's different from regularly file copying, because it takes care of any users currently working on the database. There are proper locks set on the database, so the backup is done exclusively.

1 Like