I would consider to switch the recorder to an MariaDB - which could run on a different machine, for example.
With having enough disk space, it could collect data for nearly a year… depends on the amount of sensors and things like that.
Also, sure - the logbook or history will become slower, when you select a huge time range to display… but at all, it should work.
my last setup (before doing a fresh installation) was nearly 15 GB database … with… (i am not sure - 3 Months or half a year of history)…
anyway - such a configuration - and to prevent a massive amount of used disk space, I would really recommend to check, what you need to have in your recorders - and exclude not required information
Doublechecking… statistics on devices are covering a much longer period, could you maybe provide a use case (also for my learning) why you want the individual registration longer than (say) 10 days?
I would like to compare data over several years, then you will find out trends, for example your electricity and gas consumption, what has been the average per year and whether you have used more and have to do something about it.
I also find it interesting to know, for example, how many times our smart doorbell has gone off, not very useful information, but collecting this kind of data just gives you a lot of insight
The one I use is Influx but as a rel.db. stuck guy, I am not too fond of it as I prefer to play around.
MariaDB is my main recorder but have not yet understood how this could also be used aside (!) the main recorder…not-yet as far from my prio
storing data for several years will be an issue due to the fact, that Homeassistant is writing each state change to the Database.
For getting trends and really “longterm” data, I think other methods should be considered - outside from what HomeAssistant can do.
I was planning such a thing, too - but haven’t yet started with this…
One Idea that came up in a discussion was, to create another database (beside the one HomeAssistant is using) - and use NodeRed to store specific data in it.
The idea was somehow like:
Getting the Data from HomeAssistant into NodeRed
Writing it into a SQL Database (maria DB) - while using the following logic:
INSERT INTO mytable(sensorname, lastresetdate, consumption) VALUES
(:friendly_name, :last_reset, :solaredge_monthly_kwh_consumption) ON
DUPLICATE KEY UPDATE consumption = :solaredge_monthly_kwh_consumption;
This should write the sensors state into the database - and it should create a new line, when the sensor will be resetted.
But as of now, the SQL hasn’t been tested and it is just a theoretical “how it could work”
Maria DB will be my main database for recorder.
But the HA recorder will record each value change - and - I don’t need to store too much data in the recorder-db.
The benefit of the secondary database, is, that I don’t need to create queries on the tables created from HomeAssistant.
I could just use the entities I want in nodeRed - write the data I want into the Database - and since that will be my own db layout, I could do “whatever I want” with this data.
It will at least store less data than homeassistant will do…
I could create NodeRed Dashboars, or could automatically create CSV export information on the information I want to get… without needing to know the full db layout from homeassistant - I don’t have to deal with the entitie-IDs in the database, or whatever.
In my point of view, there’s a huge maintenance benefit of writing the data that I want into a second maria db… ( I could even just delete the data without loosing data inside of HA)
There’s only one Recorder possible in HA.
An Idea could be, to use the internal sqlite DB - and then use nodeRed to Collect the Data you want to Store in a Maria DB and Use a MySQL Extension in node Red.
But then, you can’t Access this Data Grimm HomeAssistant itself.
But tbh - I don’t see the reason for having two recorders
Well, the problem is if i want to keep the temperature sensor data for years… i don’t know how to do that.
These are really needed now.
I want these to be access by homeassistant itself.
So is there a way to let the xiaomi zigbee sensors act like a long term sensor so it won’t get purged?
you can use a maria db as recorder - and increase the purge-intervall.
You could also create a template sensor that is using the xiaomi sensor values - and use the state_class as measurement for example.
That SHOULD also activate the long term statistics.
But I don’t understand, why you want to use two recorders (mysql and ha internal sqlite)
Right now i have the data of the hass database, whats over 2 years, i dont want to lose it, and there is no safe way to migrate that database into mariaDB
if i have a second recorder like mariaDB i could let it save the data on an external server.
so i could split the devices for example the long-term statistics without creating tons of templates for temperature/humidity devices to keep them in my db.
So than i could use both to read the data in HASS.
if there is a safe easy way to migrate the data into MariaDB ill do that.
But i checked a lot of topics here, included the Migrate topic and its to complicated and still you can lose a lot of data during the migration.
I could set the Keep_days option to 1000 or something but it will fill up my space on the hass server.
and there is no way to let hass save the db on an external drive