Last two weeks i made some hardware en configuration changes for my home assistant and ended up reinstalling and putting back a full-backup from the supervisor menu.
Both times every configuration and setting came back and worked except my energy history.
I don’t have experience with databases so it would be nice that the history data of my energy measurements would be backupt true the normale supervisor backup option.
the main problem I can see here is that this would only work if you are using the internal sqlite database.
Of course, the database should be included when you are creating a backup… if you don’t use any other database as recorder.
BUT:
if you are using a dedicated database such as MariaDB this would become more difficult - because it would depend on your exact setup.
Having a Database within Supervisor (same machine, but another docker container) could somehow create a backup…
As far as I can see, the most reliable solution would be to run a dedicated database on a complete different machine.
This would allow you to keep the DB Recordings and so on… and you could create seperate backups from your database.
Maybe i have to explain it better, before the energy dashboard i used DSMR-Reader. Then came the energy dashboard and the Slimmerlezer P1 reader so i wanted more unity and stopt using DSMR-Reader and start using Energy Dashboard with a clean sheet.
So Home Assistant is storing some where the measurements for the Energy Dashboard, i dont know where and i don’t use some alternative Database. I don’t have experience with databases and don’t need to have it.
Only thing that i want is when i start a backup in the supervisor it also backups my Energy Dashboard readings. So when i reinstall home assistant for any reason i have my past reading back.
I think more people that do not have much experience in databases or recovery will have this problem with the Energy Dashboard
If this is not possible then i have to back to DSMR-Reader but that is not what i want.
I completely understand your point
The only thing I have to mention from my side (but I am not a developer) is, that there are difficulties with such a backup.
by default, Homeassistant is using a file-based database (SQLite) - the Database will be stored in your config folder (*.db) file.
that’s totally fine - and could be included into the backups.
BUT:
such a backup could become way more complicate if you have to consider, that you can change the recorder to whatever you want.
It can be a MariaDB or InnoDB provided as Superviser addon - or it can be a dedicated DB Server.
And since Homeassistant does provide such option, these needs to be considered in any backup implementation as well.
As far as I understand, the implemented backup does have one main purpose:
It should backup your configuration in case an update would break it.
with this, you could restore the configuration - and you would not have any data loss.
Therefore, HA does not really provide a ‘full qualified backup’ for complete restoration of a damaged system.
This needs to be acheived with other methods… depending on your system setup (raspberry, or other system where HA is running on, etc)
Are you aware of restores not restoring or even backing up the energy dashboard data?
Tried this while moving from a VM on ESXi to a vm on a Synology nas and had the exact same issue as @royvi is mentioning here. there seems to be some data wich is restored but not all of it… i really get som strange numbers here.
I think you are wrong on this if you use the build in DB all of the data is in the backup! Except for Energy dashboard data.
I do agree in some way that external databases are not that simple but if you support specific types of DB’s you can also make sure there is documentation on the proper way to backup all of the data. This is in both of the cases not clear to the enduser at this time.
And if home assistant want’s to have a broad adoption of their platform this must be user friendly.
Hi @sdhondt, hm… Since the Energy Dashboard Data is a caluculation of the ‘statistics’ and ‘statistics_short_term’ table within the database file, I don’t see any reason how a particular table should not be included into the backup - if the database file is included into the backup.
Based on the methods I know on how a backup of a sqlite database file would be created, I can’t really see any reason why this data should not be restored.
Maybe, it is somehow related to the dashboard itself, that it does generate a new “entrypoint” for its calculations and therefore ignores past data…
Hi, about my backup…
The very next day after the 12.1 update, there was bar reading in my energy dashboard dat I used 12 megawatt (in one hour) and my Solar system also delivered 12 megawatt. All the previous data in the energy dashboard was still oké. After reboots I decided to install a backup from a day before. After that my energy history was completely gone.
Maybe there is a good reason that this all happens. But I’m not a programmer. So maybe there should written that if you make a backup it’s not a full backup.
The database of your energy dashboard is GONE !!! .
How can I make a complete backup. So that everything is in it. Do I have to take out the hard drive and make an image of it is that the best way? Is there any chance that I can get my data back?
It’s all the same database, so either there is a problem with the database, and all data (history AND statistics for energy dashboard) is gone, or none.
The only explanation for ONLY statistics for energy dashboard missing is, that the entity_ids have changed in between.
You can find some guides here in the forum on how to deal with a corrupted database.
I can also confirm this issue. Just made a backup yesterday, reinstalled Home Assistant and restored the backup, and indeed everything is nicely restored, exept for the energy dashboard history.
I ran on the same issue and I’ve realized that, after restoring from a backup, the DB file was created from scratch while the old DB was renamed to home-assistant_v2.db.corrupt.2021-12-31T13:57:51.093335+00:00.
I also had the same issue after I upgraded my hardware.
Fresh HA installation on new SSD
Restored a full back up
I lost 6 months of data which is a little bit annoying. The only data I retrieved were:
Data I’ve sent to influxdb.
One week of data (because I use purge_keep_days: 7 in recorder configuration)
So like @royvi, I’m a little bit concerned about how my data are stored and how I can make a proper back up.
I really like built-in energy dashboard and I would like to keep it.
but as long as I don’t know how I can back it up I’ll not take the risk to use it anymore.
+1 On this too.
Migrating from a Supervised install to a Virtualbox VM using a backup/restore.
Everything seems to have transferred except for the historical Energy data.
I have to migrate my setup to Hard disk from SD, I have a second Pi with hard disk and HA ready and the only thing keeping me back, is my energy database…
That’s extermely important to me and I think anybody installing these systems.
We are kind of obsessed with our energy History DATA…
I also use influxDB and Graphana in my setup.
Ideally I would like to be able to copy the statistics or database files and go to a fresh installation.
My setup is so big and I have done so many changes that I am sure a fresh setup would be faster and bugs free. I thought to clone my sd to HDD but I prefe a fresh start.
I undestand all entities must have the exact same name for this to work but that’s not a problem.
I have already lost my database once now this one is 6 months old and I am not willing to dump it like that.
It’s bad. At some point the only good “complete” backup would consistenly declare corrupt db some hours after restoring. Maria DB corruped at daylight saving hour last year and I switched back to default sql.
Not one fixed db file using prigma integrity check command. That needed Linux so enabling WSL on Windows stops VM acceleration in vbox working correctly and the new fresh HA with lost statistics got way way slower without me knowing the reason for some time. Influxdb is great but I the ui especially on mobile is buggy and overall it’s not not-a-real-programmer friendly. It’s all free software from some awesome people but long term statistics are not…getting old.
hm…
in fact, we might need to discuss a bit deeper how the energy dashboard - and how the backup of HA itself is working.
What do we know:
By default, HA is using a SQLite Database.
This means, all data will be stored in a single, local database file - including all tables HA needs to run (including the statistics for the Energy Dashboard)
Question:
When you run a backup, does HA create a 1:1 copy of the Database file?
When you run a backup, does HA create an export of the local Database file into a new file?
In this case, it might be, that not all tables are included in the export.
Furthermore:
Depending on how the backup will be done, what Actions are performed by HA when restoring the Backup?
My guess:
When a backup will be restored, HA will create a new “Entry point” for the Statistics calculation.
Why:
Since it could be, that the last backup is a couple of days old - it will cause a wrong calculation in the energy dashboard if you start with the previous stored data.
For example - the backup is from Wed. - and you restore that backup on Sunday - you will have a gap in your statistics for several days.
but the way how the statistics calculation will be done is by getting the difference between the last stored value and the new stored value.
We would probably see similar issues than we have seen after the integration of the energy dashboard, with strange peaks…
Even if it is odd - starting with a complete fresh statistik - seems to be way more reliable to me in this case.
The whole discussion is based on the simple fact, that recorder database backups of the default recorder (SQLite db) were (are?) useless because of the way it is backed up - and that is during being operational and not locked. That usually results in a non-restorable state of a recorder database as part of the backup.
See
Of course, being able to create a separate backup would be great - but where to start and where to end. I would be happy if the backup of the SQLite database would be reliable and safe to restore instead.
