I see that there is the mariadb in the add on store, but its states it would be a replacement for the SQLite db that HA uses…
I have a decent sd card running on the PI, and looking at adding my 4TB SSD drive to it as well, so if its possible, i wouldnt mind setting up to store the data on that instead of the SD card if thats possible.
Still in the research stages, but just curious if its been done and how it was done (#1 SSD as storage for the system) (#2 Ability to keep historical stats on key devices within my HA setup for review and to help improve flows after running for some time…
For long-term data most people (including me) use a time-series DB like influxdb, which is made for this purpose.
I store one week of history on the HA DB (I use Postgres as DB) and everything I want to track for the long term, I push the data to influxdb through the influxdb integration. I can then also make some nice graphs in Grafana from the InfluxDB data.
So is there an easy way to migrate the stock db that came with HA over to postgres? Or a setting to enable longer history retention in HA for the db that it has? Seems that would be the better option instead of starting all over with configuration. If the stock SQLite db will hold data longer, then i can just setup influx and move forward from there.
It can but it’s not recommended, especially for the duration you want (weeks/months).
If you search this forum for ‘corrupted database’ you will discover that the SQLite database can be damaged. A common complaint is the inability to use the database after it has been restored from a snapshot backup.
The usual “fix” is to simply delete it and Home Assistant will create a new (empty) one. In fact, recent versions of Home Assistant ignore a corrupted SQLite database and create a new one. Obviously this is not good for you if you plan to store weeks/months of data.
well i guess it would be a lot of data, but only for key sensors as i build out the home automations. But like i said im researching things now so i can start planning my game plan on implementation of the changes.
So is there a way to port over the current db to MariaDB or Postgres DB without having to start all over with HA and setting everything up?
The HA DB doesn’t store configuration data, only history and usage. You can safely define your MySQL/Postgres DB in config, stop HA, delete the sqlite DB and then restart it.
As for keeping past data, I don’t know of any way to do that off the top of my head.
You can configure which db that HA uses and how long data will be stored etc. through the recorder integration.
What do you mean with this? You only need to change the db that HA uses to store the info, you don’t need to configure your entities again or anything like this if you were talking about this.
Influxdb and the HA DB are configured and working independent from eacht other, I’m not pushing data from the HA DB to the influxDB, I have separate configuration which entities should be recorded in the HA DB and which in the InfluxDb.
Yea I’m not concerned with old data, just wasn’t sure if i would loose anything to do with devices and entities since im just starting out with HA and learning nodered flows and automations, figured now would be the time to upgrade a little so that i dont get to far down the road and make it harder to migrate. So if its just a matter of defining the new DB, then I’m all in and will look at the steps to proceed.