Page 1 of 1
Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 10:54
by 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.
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 11:20
by 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)
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 12:00
by 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 .....
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 12:04
by mgugu
If you have not changed the domoticz port, it should be 8080, not 80
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 12:11
by 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 ...
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 12:17
by mgugu
Do not forget that the script must have root ownership since /tmp is a root folder
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 12:43
by mgugu
Toulon7559 wrote: ↑Sunday 13 February 2022 12:11
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
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 13:26
by 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 .....
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 15:37
by 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
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 16:04
by 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.
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 16:07
by waltervl
Toulon7559 wrote: ↑Sunday 13 February 2022 16:04
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)?
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 16:32
by mgugu
Toulon7559 wrote: ↑Sunday 13 February 2022 16:04
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>
Re: Backup of domoticz.db by means of dzVents-script
Posted: Sunday 13 February 2022 19:15
by 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.
Re: Backup of domoticz.db by means of dzVents-script [SOLVED]
Posted: Sunday 13 February 2022 21:08
by 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.
Re: Backup of domoticz.db by means of dzVents-script
Posted: Monday 14 February 2022 17:06
by 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.
Re: Backup of domoticz.db by means of dzVents-script
Posted: Monday 14 February 2022 18:20
by 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 .......