I am looking for a method to purge my database without loosing historical data for some special entities. I know, that there are historical data in the state_class, but I also need the data for special switches and lights.
recorder:
# The number of days of history to keep in the recorder database
purge_keep_days: 90
auto_purge: true
auto_repack: true
# Connection to MariaDB on Carla, DB Homeassistant
db_url: mysql://HomeAssistant:<user>@<ip>:port/homeassistant?charset=utf8mb4
commit_interval: 30
db_retry_wait: 30
I understood that I can remove entities from the database, but what I want to achieve is the opposite. I will remove everything without a list of excemptions. The excemption-data should be kept vor 90days and the “rest” for 10d. I tried an automation but I am not sure what happens to the database if I try to remove a list from more than 1000 entities with my automation below. The automation will also remove all data and will not keep the last 10 days for the “rest”. So I guess the solution is not the best. Does anyone have an idea how I can implement my usecase?
automation:
- alias: Database Purge
trigger:
platform: time
at: '03:00:00'
action:
service: recorder.purge_entities
data_template:
entity_ids: >
{% set exclude_entities = ['sensor.temperature', 'light.your_light_entity', 'switch.your_switch_entity'] %}
{% set all_entities = states | map(attribute='entity_id') | list %}
{% set filtered_entities = all_entities | reject('in', exclude_entities) | sort %}
{{ filtered_entities }}
jo! Seems to be the same, but it also seems to be the case that we have to wait years for an implementation! I’d like to understand if it’s worth thinking about my solution or if this kills the mariaDB! If we add “keep_days:10”, will the “rest” be at 10 days and the excemptions at 90d, or is there something I missed?
I like the idea of trying to set up a home-grown variable retention period automation. I’ve thought about doing that, but never quite got around to it. Instead my solution is to have a short keep_days paramater, exclude every entity I can, and log any long-term statistics I want to keep to a set of text files I can open up and view in other tools like spreadsheets or data analysis apps.
I think what you’re asking is the impact of purging large numbers of records from within the automation. I would imagine if it were run frequently enough, you could keep the number of records purged during any given run to a manageable level.
Unfortunately I’ve sort of given up on the idea that the HA database will be of much use to me for long-term data, and treat it as just a way to make pretty near-real-time graphs in the UI.
I have an automation that purges specific entities with specific retention periods. Anything that is not in the automation is retained forever. I generate the automation from metadata.
Moin,
thanks a lot for sharing your thoughts and your configuration. I am thinking about a diffrent approach. Instead of purging the mariaDB, I am thinking about to install an Influx DB in parallel to the recorder. Influx can handle big database size much more better than mariaDB. mariaDB will only cover the last 7d.It is also very easy to use grafana with Influx V1.,but it is only an idea.