WTH: Migrate history when enabling Recorder

[jamesmyatt]

When enabling the Recorder integration, there’s no option to migrate your history to it. WTH? If using the MariaDB addon is the “recommended” way as opposed to the default “SQLite” one, then migrating the history should be supported too.

Sure there are instructions to do this (e.g. Migrating home assistant database from sqlite to mariadb - #84 by le_top) but they’re not exactly simple to follow. And this must affect basically every user.

[RMoRobert (Robert M.)]

@jamesmyatt

If using the MariaDB addon is the “recommended” way as opposed to the default “SQLite” one

It isn’t; the default SQLite database is the recommendation according to the docs:

There are community-documented methods of migration, if that is still helpful. Here’s one I could find, though there may be more:

Migrating home assistant database from sqlite to mariadb.. ONLY if you are VERY familiar with Database administration

As requested by @jr3us, here is an updated script up till today. It might become obsolete as new versions are created and might not work for you! I offer absolute NO guarantee for any data lost!. You are warned. # The first few steps here are typical install and configuration of mariadb. sudo apt install mariadb-server mariadb-client sudo apt install libmariadb-dev sqlite3 sudo mysql_secure_installation I did the following 2 steps using phpmyadmin Created a user hass with all permissions …

Whether migration can or should be built-in is something I can’t answer, and I do know some people claim better performance (for things like viewing history, at least) with another database engine. But, at least, I don’t see any official recommendation not to use the default.

[CChris (Christoph)]

I think, one of the major topics that should be considered in the future is the focus on the Hardware.
I really understand why HA was intended to be able to run on a Pi… For beginners, it might be a nice way to start their way into the world of HomeAssistant - but it also has some (in my eyes) major disadvantages.

it usually requires a local “in memory” database - such as SQLite - which will have an impact on the performance… and of course, the backup strategy… we already have several topics in the forum complaining about missing backups of the energy dashboard - which is (again in my eyes) a consequence of the used Database.

Running a dedicated Database, such as MariaDB - maybe even on a different host will solve all these issues, since your HA Backups are nothing more than backups of the core software and your configuration.
Backups of your DATA will be done on a different level - and you wont loose energy stats or whatever.

I totaly agree:
You ARE able to change the Database - by using the recorder option in the config.
And then, it should Migrate the energy dashboard data and everything else.

[sisri (Przemysław Dmochowski)]

There is already WTH topic for easy migration:

WTH can't we have option to migrate all data from sqlite to MySQL/MariaDB?

There is no easy way to migrate all of historical data (mostly statistics) from sqlite to MariaDB. I don’t want to loose anything so just switching DB in HA is not enough.

please vote there…

[jamesmyatt]

@sisri Ah! Thanks. Not sure why I didn’t find that.

[jamesmyatt]

@RMoRobert , you’re quite right that I’ve exaggerated the situation. But as I see it, there are two “recommended” courses of action:

• For most people, the default sqlite database is “recommended”. This is the case in the official docs, since it works well enough for everyone, regardless of technical capability and hardware.
• If you are able to (e.g. your hardware supports it), then it’s “recommended” to migrated the database to MariaDB, MySQL or similar. This is the case is most community guides, since it the technically superior option, e.g. performance, robustness, functionality, etc.

But I think more and more people are migrating to hardware that could support a dedicated database, i.e. anything with an SSD, including Home Assistant Blue and Yellow.

One of the main issues is that the community migration guides will be instantly out-of-date if the way HA uses the database changes, making for more headaches for more people. There should be an officially supported method to do this migration.