Where has all history data gone? | Home Assistant | Page 1

spring scroll Mar 4, 2025, 11:20 AM

#

Reading the log, I have lost all historical (most important the energy stuff) at 4AM of Feb 24. I think to recall that this was a point in time (weekly 4 AM) where the database would cleanup.
I wonder what are my options to get (at least) the historical energy info back?
Thanks in advance. YaNn

patent jackal Mar 4, 2025, 1:20 PM

#

first check the sqlite database file(s) and siize... In case of problems a new empty DB is created...

spring scroll Mar 4, 2025, 5:50 PM

#

I see a set of 3 files for Zigbee.* and a set of 4 files home_assistant_v2.* .
The latter has a file called home-assistant_v2.db.corrupt.2025-02-25T03:12:00.951709+00:00 and has a size of 356.8 MB. The corresponding home-assistant_v2.db has a file size 224.0 MB.
So it looks that the data still may be there, but how to transfer it to the right .DB file?

spring scroll Mar 5, 2025, 8:11 AM

#

The corrupt file can not be copied, so I must consider it as lost. I do heve older .DB files , but how can I transfer data from the old into the new (or maybe the other way around) ?

patent jackal Mar 5, 2025, 9:21 AM

#

Most probably the file was corrupt ( sd card corrupt, power lost ...), and the daily job which is planned at 4:12 (local time) , detected the corruption, and a new DB was created at that time.
You can try to get the corrupted DB on your PC and search if some tools exists about sqlite recovery...
On which hardware your HA was running ?

spring scroll Mar 5, 2025, 3:57 PM

#

HA runs on a PI4/8GB using a SSD.

#

Is there maybe info on the web that describes the format (fields etc) of the sqlite file.

patent jackal Mar 5, 2025, 5:56 PM

#

looks like it's possible to extract the content of the DB in sql. ( https://sqlite.org/recovery.html ) .
But also like this: https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database
Never tested as i'm not using SQLite.
Always test it on copies. Not the real files.

Stack Overflow

How to recover a corrupt SQLite3 database?

This is a follow up question to a previously answered post: Is there a command line utility for validating SQLite databases in Linux?
If a database is producing the following error:
$ sqlite3 mydat...

rugged widget Mar 5, 2025, 8:05 PM

#

That's a hit and miss...

#

If the data is corrupt then the best thing to do is restoring a backup

spring scroll Mar 6, 2025, 2:54 PM

#

Or I can take the last backup befor corruption and add the data that came after wards.
BTW ,where could I find more info on the purpose of the "home-assistant_v2.db-wal" ?

spring scroll Mar 11, 2025, 12:04 PM

#

In the meanwhile I figured out that the HA-DB has the following tables:
event_data, event_types, state_attributes, states_meta,
statistics_meta, recorder_runs, migration_changes, schema_changes,
statistics_runs, events states, statistics, statistics_short_term and sqlite_stat1

I have no idea what these tables do, bu could I not just insert into the statistics or statistics_short_term from an old energy database to retrieve the history in the current DB ?

rugged widget Mar 11, 2025, 12:33 PM

#

maybe, if you're comfortable working with databases and SQL. But like I said, easiest is to just restore a backup.

patent jackal Mar 11, 2025, 1:12 PM

#

spring scroll In the meanwhile I figured out that the HA-DB has the following tables: event_da...

I'm doing it by hand using Postgres when a sensor is renamed and i want to recover history.
It is not so complicated when you have some background of SQL.
Could be challanging if you start from zero, without any dedicated VM, and if you'r not comfortable with full backup/restore ;-))))
( You need to have a plan / strategy, it must be clear in your mind, and you need backup, go slowly, step by step)

rugged widget Mar 11, 2025, 1:40 PM

#

I wouldn't do that unless you are an expert

#

spook.dev also has some tools for importing historical data

spring scroll Mar 11, 2025, 1:48 PM

#

oke, will have a look there.

spring scroll Mar 11, 2025, 1:54 PM

#

rugged widget maybe, if you're comfortable working with databases and SQL. But like I said, ea...

Unfortunately, there is already >3 weeks since I realized the corrupted DB . So continuing with what I have now will loose all history before that. restoring anything before that will give a hole of the history between the time of restore and now() .

rugged widget Mar 11, 2025, 1:54 PM

#

spring scroll Unfortunately, there is already >3 weeks since I realized the corrupted DB . So ...

and the longer you wait now the more data you will lose

patent jackal Mar 11, 2025, 2:00 PM

#

well, right now, you may have to recover 2 history's 😉 😉

spring scroll Mar 11, 2025, 2:06 PM

#

Exactly. The plan is :
1a) take the youngest backup with correct history and turn that into SQL
1b) filter all the historic INSERTS
2a) make backup of now()
2b) this gives me a valid point to return to
2c) insert 1B) into 2a)
3) if not working goto 2B

patent jackal Mar 11, 2025, 2:11 PM

#

BE carrefull with ID values. You will have to map them ... because sensor A could have ID=123 in your old DB, but received id value 234 in your new DB 😉

#

#

Statistics are averaged at hour level. As your home assistant recreated the SQLite in the same hour, you will have duplicate entries ( same timestamp for the same sensor in both DB.. 😉 )

spring scroll Mar 18, 2025, 8:52 PM

#

patent jackal

Are you saying that the index of the metadata_id can change in different versions of the same database ?

#

In the meanwhile I figured out that the HA-DB gets corrupted (truncated?) when there is an automated backup. I say truncaded as the size goes from (say ) 160MB to 38MB.
I learned to (somewhat) overcome this by making a manual backup (is not truncated ! ) and restore that after the automated backup.
This now happened 5 days is a row. I wonder where does the automated backup differ from a manual backup?

patent jackal Mar 19, 2025, 2:11 AM

#

HA has a daily job ( scheduled at 4:12 AM if i remember) to perform daily DB maintenance like :

remove old ( > 3 days or so by default , configurable) entries in "short term" statistics.
remove old entries in "state" tables
"long term" statistics are not updated during this process, and are NOT deleted.
=> that's why you could see your DB schrink after that. That's normal. DB is not currupted, just we don't keep all records forever.

LT statistics ( sum/min/max/max/avg of NUMERICAL values) are created/maintained "on the fly" during normal execution.

By default automatic backup is executed at 4:45 AM.
Automatic/manual backup should have the same behaviour ( should because this feature is quite new...). To be honest: I'm not fully trusting it ATM ( 😦 ). I prefer a snapshot.

patent jackal Mar 19, 2025, 2:34 AM

#

And about ID's, they are stable over time and upgrades, ...but yes they could change 😉 like:

when a new DB is created 🙂
may be if you select the option rebuild / recreate somewhere in .... recoder_purge i think
if you remove a sensor, then go to "dev tools"-> statistics, then "remove" . At that time all records regarding this sensor are deleted (including it's ID). If you recreate the sensor, it will most likely receive a new ID

#

By the way you can see on the graph below :the left side is using long term statistics ( aggregated at hour leve), while the right side is using short term:

#

There is a very nice add-on called dbstat ( https://github.com/jehy/hass-addons/blob/master/dbstats/README.MD ) to display some graphics about the content of your DB.
Very handy when you want to have the big picture like

GitHub

hass-addons/dbstats/README.MD at master · jehy/hass-addons

This is a repository for my own home assistant addons - jehy/hass-addons

spring scroll Mar 19, 2025, 7:56 AM

#

patent jackal HA has a daily job ( scheduled at 4:12 AM if i remember) to perform daily DB mai...

OK, to things happen early in the morning: 1) a DB maintenance and 2) a backup. This confirms my observation: backup is doing exactly what it is supposed to do but the DB-maintenance is likely to be the culprit. The eldest entry in the logbook is 4:14 this morning and any history before that has gone.
Does the DB-maintenance job log somewhere what it is doing? Or are there some tools that will allow me if the DB that I restore has somehow records that disturb this maintenance process.

All this stuff is not good for the WAF as the young lady is observing our energy consumption 10x more close than I do 😳 .
But thank you for the help so far.

dawn blaze Mar 19, 2025, 8:05 AM

#

As an aside, as mentioned by Antarctica, you can increase the number of days HA will keep. But be forewarned, increasing the number of days is going to pretty notably increase your DB size.

You can add the following to your config.yaml