WTH More focus on database recovery

[snigehere]

Over the past few months I have lost valuable statistical data that I am not able to access any more. As time goes on the statistics are more and more important but HA does very little to help when things go wrong

1. More robustness in the database design to prevent failures from corrupting the database, and when a corruption does occur more effort by HA to recover and restore.

2. There needs to be a repair alert when the database has an issue. At present a corrupt database is simply renamed and a new one added… this error does show up in the log but unless you look there every day its easily missed. If you restart its lost all together. from the a active log.

3. Users should have an easy to use method of recovering data from a backup version of the database and merging data from the recovered database into the new one,

Worst case, If I knew the database had been corrupted when the system started, I could recover from backup and restore… I may loose a day of data but that’s better than loosing months of statistics.

Better case would be to be able to restore up to the last write from a database log.

ideal would be, in the event of failure, for HA to offer recover from backup and data recovery during startup rather than just renaming and continuing.

[JerryM]

Do these issue exist on both SQLite and PostgreSQL? What are you experiencing with WAL mode enabled?

I am getting ready to start my database and would love more input on this topic. Thanks

[potelux]

Duplicate?

WTH why can't HA correct a corrupted database

I have had issues with the database getting corrupted several times in the last 5 years and lost all my data. Each time it renamed the database file and created a new one. I am not familiar with databases and had no way to fix it (I tried finding help from the community but it was beyond my skill level). It would be nice if HA could fix the database or just delete the section that is corrupt if that would be possible… that would be an awesome stability improvement for HA!

[Bob930 (Robert Schulte)]

Thanks for letting me know about this topic. Is there a way to delete my post?

[snigehere]

@JerryM I use the database provided by HA as default

[JerryM]

@Bob930

Thanks for letting me know about this topic. Is there a way to delete my post?

I think it’s the 3 small dots but maybe a mod will merge it instead.

@ snigehere
The default database for home assistant is SQLite.
Im sure our issues are touched on here:

Fix corrupted / malformed SQLite database (home-assistant_v2.db)

I restored a snapshot when switching hardware / sd card (HASS OS on Pi 2 B 32 bit to HASS OS on Pi 3 B+ 64 bit). Result: The home-assistant_v2.db from a restored snapshot is malformed or corrupted. This leads so error messages and not functional Home Assistant (history graphs not generated/shown, sensor state changes aren´t stored etc.). I tested it several times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process. sqlalchem…

SQLite, which is lightweight and easy to set up but has limitations:

1. Corruption Risk:
2. Automatic Renaming:
3. Write-Ahead Logging (WAL):

There are other alternatives MariaDB/MySQL or PostgreSQL.

What hardware do you use? For me, I’ve been thinking of getting a Raspberry Pi NVME setup with MariaDB.
Id be interested to hear how a pro with a time-tested setup to give some info on how they achieved it.

Honestly, I was shocked to learn SQLite was still around.