Restoring history database after patch tuesday

[madbrain]

This tuesday, a forced update on my windows host caused my HAOS VM to abort and restart. I just noticed today that all the history since tuesday is gone.

I have made dozens if not hundreds of configuration changes since, including setting up new Zwave devices. I have nightly backups with those changes, but they are also missing the history. I have previous backups also with the intact history, but without the configuration updates.

Is there a documented/supported way to restore just the DB history, without overwriting the configuration at the same time ? I have tried in the past and never succeeded.

The same host was also running a Unifi controller. I was in the middle of making config updates when the patch tuesday update hit. The AP became corrupt, to the point of needing a factory reset.

This is the point at which I give up on Windows pro as a server platform. But i’d still like to preserve my DB history even though I’m going to migrate away from it. And long power outages are still a thing in my area, so something similar could still happen on Linux, too.

[tom_l]

@madbrain

Is there a documented/supported way to restore just the DB history

Kind of.

Assuming you are using the default SQL database, download one of one of these backups to your PC:

@madbrain

I have previous backups also with the intact history, but without the configuration updates.

Make sure it is the latest one as you will lose all state, event and statistics data after that backup was made. Your config changes will remain intact. This is what I meant by “kind of”. It depends what is more important to you, data before or after that backup was made. If “before” then…

Install and configure the SAMBA share add-on (if you have not already).

Install the Community Add-ons Advanced Web terminal add-on.

On your PC use 7zip or similar software to open the backup you downloaded.

Then inside that open the homeassistant archive.

Then inside that open the data folder.

Then copy/extract just the the home-assistant_v2.db file to your PC.

In the Advanced Web Terminal add-on issue this command:

ha core stop

Wait for Home Assistant to shut down.

Then using the SAMBA Share addon (or SSH or any other method) copy the extracted home-assistant_v2.db file from your PC to the /config folder of your Home Assistant server overwriting the existing file.

Then in the Advanced Web Terminal add-on issue this command:

ha core start

If you want to merge the data from the backup with the current database you will need advice from someone with much more SQL knowledge than me.

[madbrain]

Thank you !

I got it to work. My history is back. I am OK losing the last couple days worth of history - just not years worth, and not all the config changes.

I did run into a couple issues following your procedure.

1. when I typed “ha core stop” in the advanced terminal, I got a “Processing…” message, and the add-on was seemingly hung

2. after I copied the history over, using the “enter” key in the add-on to reconnect did not work. I was able to launch a separate SSH client and login to HA way, then run “ha core start” . At that point, it came back up with the 2 day old history and the latest settings.

It would be great if there was a checkbox in the backup tool to restore only the history. That is something I have faced on several occasions in the past, and I expect it won’t be the last due to the occasional power outages.

Some kind of automation support would also be useful. Maybe get a notification when the purge runs, and the history gets pruned due to corruption. Some way to restore the history through an automation would also be great, though that would be trickier.

Thanks again !

[tom_l]

@madbrain

It would be great if there was a checkbox in the backup tool to restore only the history.

You can create a feature request for that (search the category first, to see if there is one already available you can vote for).

Feature Requests

[madbrain]

Done.

Add ability to restore only history from backups

I have had multiple instances of history corruption due to power outages or silly software things like patch tuesday rebooting the host unattended, and hence the HAOS VM. I don’t always notice that the history is corrupt right away. It often takes at least a few days. At that point, many configuration changes may have been made to HA. I find myself in the situation of having backups a few days old, with outdated configuration and history, or the most recent backup with no history. My preference…

[tom_l]

Just FYI, I use the MariaDB add-on. It use to be faster than the default SQL DB. Not so much nowadays - but I can restore the add-on (and the data it contains) independently of Home Assistant.

[madbrain]

Thanks. Is MariaDB any more resilient to power outages than SQLite ?

I looked into using it a few years ago, but the setup was a bit complicated. I see there is now a guide out there with 19 steps, which don’t seem too bad. It doesn’t cover data migration, though. I would also prefer to stick with the default DB option supported out of the box, and keep it simple, without waiting for 3rd party to intervene if something breaks in a HA update. There is also the issue of migrating back to SQLite if needed.

I think the option to restore history separately even with SQLite is valuable, so I filed the RFE.

[tom_l]

@madbrain

Is MariaDB any more resilient to power outages than SQLite ?

Untested, (I have a UPS) but I would not assume any special resilience.

@madbrain

I would also prefer to stick with the default DB option supported out of the box, and keep it simple

I can understand that. Just thought I’d mention it as an option.

[madbrain]

I also have several UPSes in the house, but they don’t last long enough for most of the outages. And obviously don’t help for patch Tuesday.