Backup of domoticz.db by means of dzVents-script

[Toulon7559]

Elsewhere describing effort to make a translation-program.

To be safe not to harm the database, that effort needs to make a backup of domoticz.db
Waaren's proposal applies an approach in which all setup-elements can be manipulated.
Have 'squeezed' that to the following basic scriptline

Code: Select all

 sudo /usr/bin/sqlite3 /home/pi/domoticz/domoticz.db ".backup ./tmp"

On Putty's CLI that scriptline-version runs without errors, but no backup-file appearing in the tmp-directory.
With slight variation, it throws an error

Code: Select all

sudo /usr/bin/sqlite3 /home/pi/domoticz/domoticz.db ".backup /tmp"
Error: cannot open "/tmp"

Also other variations tested, but with equally or worse bad luck.
Scriptline seems correct, but apparently some subtle error in the scriptline.
Assistance appreciated to correct, or to find alternative with same functional result!

Side-remark
Studying this approach, for starters it is OK to make in this way a copy of the database before manipulation, but for future continuous use it seems prudent to direct the periodic backup to RAMFilingSystem, to reduce wear&tear on RPI's SD-card.

[mgugu]

You have a php file, backupdatabase.php, included in domoticz distribution, which do the job.
Personally I use a bash file with

Code: Select all

/usr/bin/curl -s http://$DOMO_IP:$DOMO_PORT/backupdatabase.php > /tmp/$BACKUPFILE

It is run as root via crontab but that shoud work also with dzvents (with sudo)

[Toulon7559]

@mgugu
Translating your idea to find working setup for scriptline.
Trying with the following scriptline on Putty's CLI (with/without sudo)

Code: Select all

/usr/bin/curl -s http://192.168.0.166:80/backupdatabase.php > /tmp/domoticz_12345.db

get a file domoticz_12345.db at the desired location, but empty

Devil is in details .....

[mgugu]

If you have not changed the domoticz port, it should be 8080, not 80

[Toulon7559]

Indeed!

Mixing your commandline with waaren's script the applicable scriptline might be

Code: Select all

sudo /usr/bin/curl -s http://$DOMO_IP:$DOMO_PORT/backupdatabase.php > $workingDatabase

=> no backup of domoticz.db, apparently due to wrong communication-setup
.
With

Code: Select all

sudo /usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > $workingDatabase

=> generation of backup.db
On purpose here the ip-address set to local_host to avoid any 'external' complications.
Apparently this php-version of database-backup is not able to make a numbered backup

Code: Select all

workingDatabase=$targetLocation/domoticz_$$.db

as required for the further script.
Also demonstrated to be sensitive for selection of http or https: only works with http [in my configuration]

The further script is not able to perform extraction from backup.db .......

That's the risk of mixing functions from various scripts/sources.
Supports preference to stay with sqlite3.
.
Search continues ...

[mgugu]

Do not forget that the script must have root ownership since /tmp is a root folder

[mgugu]

Code: Select all

sudo /usr/bin/curl -s http://$DOMO_IP:$DOMO_PORT/backupdatabase.php > $workingDatabase

=> no backup of domoticz.db
Apparently the php-version of database-backup is not able to make

?
First you can check in a browser

Code: Select all

http://192.168.0.166:8080/backupdatabase.php

You should be proposed to download something

[Toulon7559]

Correct, get request to download x-numbered domoticzx.db with x referring to subject Domoticz.

Assuming that backup.db is equivalent/identical to desired domoticz_xxxx.db,
then the following script-segment should be able to perform conversion to json-file compatible with the subsequent section of waaren's script performing extraction of data.

Code: Select all

#
# convert CSV formatted data to json
#
miller()
{
    echo "$@" | mlr --c2j --jlistwrap --ofs ':' cat >> $result
}

collect()
{
#    res=$($sqlite --csv --header  $workingDatabase "SELECT $@"); miller "$res"
    res=$($sqlite --csv --header  /tmp/backup.db "SELECT $@"); miller "$res"
}

However, just error reports, regardless whether sudo or not in shown bottom scriptline.
Including sudo, the error report is a repeating collection of

Code: Select all

 sudo ./buildCumulusData.sh
./buildCumulusData.sh: 48: ./buildCumulusData.sh: --csv: not found
usage: sudo -h | -K | -k | -V
usage: sudo -v [-AknS] [-g group] [-h host] [-p prompt] [-u user]
usage: sudo -l [-AknS] [-g group] [-h host] [-p prompt] [-U user] [-u user]
            [command]
usage: sudo [-AbEHknPS] [-r role] [-t type] [-C num] [-g group] [-h host] [-p
            prompt] [-T timeout] [-u user] [VAR=value] [-i|-s] [<command>]
usage: sudo -e [-AknS] [-r role] [-t type] [-C num] [-g group] [-h host] [-p
            prompt] [-T timeout] [-u user] file ...
mlr: unacceptable empty CSV key at file "(stdin)" line 1.

in fact telling that sudo is wrongly applied and valid csv-key is missing.
.
Looks like dead end .....

[mgugu]

I thought you required to simply backup the db, apparently you need to include it in another script for further processings.
Coming back to your first post with this command line.

Code: Select all

 sudo /usr/bin/sqlite3 /home/pi/domoticz/domoticz.db ".backup ./tmp"

I am not familiar with sqlite3 command line but maybe you can check this post https://stackoverflow.com/questions/616 ... 3-database

[Toulon7559]

Reliable & controlled backup of domoticz.db certainly has high merits (and your scriptline is a nice gift!),
but my aim is application of a backup as working-copy for subsequent data-extractions.
Controlled timing of a backup provides a more stable environment for extraction.

[waltervl]

Reliable & controlled backup of domoticz.db certainly has high merits (and your scriptline is a nice gift!),
but my aim is application of a backup as working-copy for subsequent data-extractions.
Controlled timing of a backup provides a more stable environment for extraction.

Why not use one of the hourly, daily or monthly database backups when automatic backup is switched on (in menu setup - settings)?

[mgugu]

Reliable & controlled backup of domoticz.db certainly has high merits (and your scriptline is a nice gift!),
but my aim is application of a backup as working-copy for subsequent data-extractions.
Controlled timing of a backup provides a more stable environment for extraction.

Understood, to make a db copy to any directory with any name this works:

Code: Select all

sudo /usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > <workingDatabaseDirectoryName>/<WorkingDatabaseCopyName>

[Melotron]

Im using an external bash file to backup the database and another for the whole domoticz folder.

Daily backup.

Code: Select all

#!/bin/bash
sudo cp /home/domoticz/domoticz/domoticz.db /mnt/Backup/Esprino/Domoticz/domoticz_"$(date '+%Y-%m-%d').db"
sudo /usr/bin/find /mnt/Backup/Esprino/Domoticz/ -name '*.db*' -mtime +31 -delete

Im also using find to keep the files to a maximum 31 days.

Monthly backup.

Code: Select all

#!/bin/bash
sudo rsync -av /home/domoticz/domoticz /mnt/Backup/Esprino/Domoticz/domoticz_"$(date '+%Y-%m-%d')"
sudo /usr/bin/find /mnt/Backup/Esprino/Domoticz/ -type d -name domoticz_20\* -mtime +95 -exec sudo rm -r "{}" \;

Also find to keep it to 3 Monthly backups.

They used to start from a dzVent script, but they are now under crontab.
It feels more stable to get a backup of the databas each night that way.

[Toulon7559]

Thanks for the many backup-tips this afternoon!
.
This project of waaren & me as output will be feeding meteodata to a remote common database, with meteodata comprising both current and historic data. Considering the amount & type of historic data a generation within the script is not a viable option, even more because most of required historic data is available from the database.
It is desired that the combined dataoutput may be refreshed every 5 minutes, and therefore the 'internal' copying must be compatibly quick.
Have the impression that the builtin backup-functions of Domoticz are not suitable to meet this requirement,
Therefore responsive to the suggestion by waaren to make a separate backup-stream as working data flow.
Persistence pays off:
after much try&error this afternoon finally succeeded in end-to-end running of the bash-file which extracts&collects&recompiles the required data from the database.
IMHO the layout of the bashfile has proven to be very sensitive to errorfree settings, formatting, paths & ownership.
Reminder to try to make it more sturdy before general release.
.
Now upcoming hurdle is tuning&clean-up of the main script:
will be further dealt with in the other thread mentioned in the start of 1st message of this thread.

[Melotron]

Now upcoming hurdle is tuning&clean-up of the main script

For this so would I turn my eyes to a python script.
I have started to look little at it.
I have a few minor script in python, but a friend are fluent in it and its an amazing.
You can do whatever you want with a database and filter alot out to a new one.

Automating the boring stuff are a good course to look in to for it.

[Toulon7559]

Many roads lead to Rome .....
See the other thread for our intended goal.
Considering that Domoticz already has interfaces for DavisVantage's USB, MeteoStick, Netatmo and TE923-at-USB, such a translation-script might have appeal for quite some meteo-buds.
The inward route already realized (= Cumulus to Domoticz), but outward route (= Domoticz to Cumulus-like) still 'under construction', approaching 'ready-to-go'-status.

In fact much appreciated if an 'alternative' is produced from another programming environment,
because then the 'users' can operate in their favourite environment (making them feel safer), and the product may appeal to other public:
Python probably better known than dzVents.
PHP-programming is another approach which might be attractive.

Sure it would be optimal if just one script could do the whole job, from data-extration, via compilation, upto/incl. file-upload .......