I’m doing a fresh install of HA on a Pi5 4GB with a 128Gb MicroSD card SanDisk 128GB Extreme PRO A2 card.
I am looking to use it for doing long term energy recording of a EV / Battery / Inverter / Solar install plus home energy usage etc. Fox ESS inverter/battery and a VW iD3 plus a couple of some modbus Eastron meters.
I have a few questions:
What the best approach would be for a reliable config of HA which gives me long term history (will certainly be 5+ years).
SQLite ≥ 3.40.1 (I understand this is the default / current one)
I am very tempted to just stuck with the default SQLite as DBA skills and need this to “just work” but I am wondering to what database size / history it will be cope with, any thoughts?
My backup plan would be to shutdown HA & take a image copy of the SD card once a month etc Does this seem reasonable?
Is this the only thing to change in the configuration.yaml file to keep 10 years ?
recorder:
purge_keep_days: 3650
It’s not going to be super critical as its main use will just be monitoring so really wanted something simple and reliable.
I am unsure of how many entities or intervals HA will be recording but I can tune that given time.
First, stick with sqllite for your DB.
Second, don’t store you data like that. You will be very unhappy. That DB file will be around 100GB or more. Just someone crying because their 2yr DB that was 0ver 50GB was crashing, it was maria, they were trying to save it but there wasn’t enough memory or disk space, they were on their 12th straight day of a repair.
When your daily database that HA rely’s on to function starts taking multiple seconds for each read/write, you will hate life and question your choices.
Keep a normal database size, install influxDB and glances to keep long term data in an updated but off the mainline fashion, and add a state_class to any data you want to save long term. Data with a state_class will stay in the live DB as another table so you can still see long term trends “live”.
Energy data is kept in long-term statistics, and these are kept forever (currently at least). Anything that you want to keep forever can be turned into a long-term statistics (see here). You wouldn’t want to do this for every sensor you have though.
SQLite is currently the fastest database option in my experience (I migrated back from mariaDB) adn the longterm statistics are good enough for energy usage (i can see 2.5 years of history now in my energy dashboard, that is as long as this feature is available). My recorder is set to two weeks history.
If you have a Pi5 I would add an SSD to it for that extra snappy response time.
I second the recommendation to use influxdb for long-term storage. It is designed specifically for storing time-series data with highly customizable retention periods. HA provides filters so you only have to send it the domains / entities you want to keep. There are dashboard cards to see graphs in HA but you can also use powerful analysis tools like Grafana or Chronograf. I’ve been using it for years and it’s exceptional. Keep your HA database small and nimble.
If you follow the trend of advice in the thread, then you can automate backups (including the database) as frequently as you like without shutting down HA. It’s common to do this nightly. The speed of this has improved massively in recent releases, and now only takes a matter of seconds if your DB isn’t excessive.
There are then add-on solutions available to automatically copy your local backups to the cloud (Dropbox, Google) or other local storage, concurrently with only keeping the number of backups you want to (I’ve gone for keeping the 5 most recent).
EDIT: I should add that installing any update to core/supervisor/os or an addon will offer the option to take a backup.