Limit size of home-assistant_v2.db

Configuration
21

I, to make it working i had to change value_template string: the correct one is “states.fast_sensor.state”. Now is OK.

sensor:
  - platform: template
    sensors:
      sensor_for_db:
        value_template: "{{ states.your_fast_changing_sensor.state }}"
        entity_id: []
        friendly_name: 'Your template sensor'        

automation:
  - alias: 'update_template_sensor'
    trigger:
      - platform: time_pattern
        minutes: '/2'
    action:
      - service: homeassistant.update_entity
        entity_id: sensor.sensor_for_db
1 Like
22

I think using time_throttle filter would be a more elegant solution for this.
https://www.home-assistant.io/components/filter/#time-throttle

2 Likes
23

Thank you, works very well. I didn’t know that filter.

1 Like
24

I had a 1.1GB home-assistant_v2.db file and using the instructions from this thread was able to slim things down to 6.9MB with a 90 day setting

 { “keep_days”: 90 }

Thanks for writing this up!

25

Does anybody know a way to conditionally set the rate a sensor state is being recorded in the home-assistant_v2.db?
It looks like the majority of records in my db are being generated by my alarm system PIRs. When I’m home I’d like to limit this to i.e. 1 value per minute but while I’m away I’d like them recorded in real time.

26

Sorry, found the answer scrolling up a bit :blush:

27

Perfect @denver, this dropped my database size considerably from just over 3GB to now 347MB.

1 Like
28

I have the same problem, despite having the follwing configuration:

recorder:
  purge_keep_days: 10
  purge_interval: 1
  include:
    domains:
      - homemonitor
      - light
      - person
      - sensor
    entities:
      - binary_sensor.0x00158d0002883657_water_leak
      - binary_sensor.0x00158d00028837be_water_leak
  exclude:
    entities:
      - homemonitor.all_monitors

There must therefore be something which trashes the log. Is there a way to check what this is? (to exclude it manually)

29

Calling recorder.purge worked great. Was 6GB, now 1.8GB.

1 Like
31

Below is a simple Node Red flow to purge and repack the database.
It uses a BigTimer as scheduler. This does nothing other than kick off the flow at regular intervals, so you can replace it with your scheduler of choice. This example runs at 1am on Sunday, and purges the db with a retention period of 7 days (keep the latest 7 days of data).

On completion it sends a notification to HA to visualize the background process. Remove the respective nodes if this becomes annoying after a while.

[{"id":"1cf76219.6c9a4e","type":"api-call-service","z":"7f52f4e2.25e814","name":"Purge (7 days)","server":"112d9be2.b9d714","version":1,"debugenabled":false,"service_domain":"recorder","service":"purge","entityId":"","data":"{\"keep_days\": \"7\"}","dataType":"json","mergecontext":"","output_location":"payload","output_location_type":"msg","mustacheAltTags":false,"x":420,"y":860,"wires":[["86d6a0f2.8c265","b0f83d37.f6525"]]},{"id":"86d6a0f2.8c265","type":"debug","z":"7f52f4e2.25e814","name":"PURGE","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":580,"y":820,"wires":[]},{"id":"63595202.9b5c4c","type":"inject","z":"7f52f4e2.25e814","name":"Do Purge & Repack","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":170,"y":820,"wires":[["1cf76219.6c9a4e"]]},{"id":"b4e17d86.5d4e1","type":"api-call-service","z":"7f52f4e2.25e814","name":"Purge - Repack","server":"112d9be2.b9d714","version":1,"debugenabled":false,"service_domain":"recorder","service":"purge","entityId":"","data":"{\"repack\": \"true\"}","dataType":"json","mergecontext":"","output_location":"","output_location_type":"none","mustacheAltTags":false,"x":760,"y":860,"wires":[["98a6e148.976c7"]]},{"id":"b0f83d37.f6525","type":"delay","z":"7f52f4e2.25e814","name":"","pauseType":"delay","timeout":"10","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":600,"y":920,"wires":[["b4e17d86.5d4e1"]]},{"id":"fd694d23.a8465","type":"comment","z":"7f52f4e2.25e814","name":"Purge and Repack HA database","info":"- Use a BigTimer as scheduler\n- Scheduled to purge at 1am on Sunday\n- Set to purge 7 days data\n- Set Longitude and Latitude for your location!\n- Sends notification to HA on completion\n","x":150,"y":740,"wires":[]},{"id":"3e60b9e.d348d46","type":"bigtimer","z":"7f52f4e2.25e814","outtopic":"","outpayload1":"on","outpayload2":"","name":"HA Purge - Sun 01:00","comment":"","lat":"52.3676","lon":"4.9041","starttime":"60","endtime":"75","starttime2":"0","endtime2":"0","startoff":"0","endoff":"0","startoff2":"0","endoff2":"0","offs":0,"outtext1":"","outtext2":"","timeout":1440,"sun":true,"mon":false,"tue":false,"wed":false,"thu":false,"fri":false,"sat":false,"jan":true,"feb":true,"mar":true,"apr":true,"may":true,"jun":true,"jul":true,"aug":true,"sep":true,"oct":true,"nov":true,"dec":true,"day1":0,"month1":0,"day2":0,"month2":0,"day3":0,"month3":0,"day4":0,"month4":0,"day5":0,"month5":0,"day6":0,"month6":0,"day7":0,"month7":0,"day8":0,"month8":0,"day9":0,"month9":0,"day10":0,"month10":0,"day11":0,"month11":0,"day12":0,"month12":0,"d1":0,"w1":0,"d2":0,"w2":0,"d3":0,"w3":0,"d4":0,"w4":0,"d5":0,"w5":0,"d6":0,"w6":0,"xday1":0,"xmonth1":0,"xday2":0,"xmonth2":0,"xday3":0,"xmonth3":0,"xday4":0,"xmonth4":0,"xday5":0,"xmonth5":0,"xday6":0,"xmonth6":0,"xd1":0,"xw1":0,"xd2":0,"xw2":0,"xd3":0,"xw3":0,"xd4":0,"xw4":0,"xd5":0,"xw5":0,"xd6":0,"xw6":0,"suspend":false,"random":false,"randon1":false,"randoff1":false,"randon2":false,"randoff2":false,"repeat":false,"atstart":false,"odd":false,"even":false,"x":160,"y":880,"wires":[["1cf76219.6c9a4e"],[],[]]},{"id":"98a6e148.976c7","type":"function","z":"7f52f4e2.25e814","name":"Prep Notification","func":"var runtime = new Date().toLocaleString();\nvar finalmsg = msg.payload.domain + \".\" + msg.payload.service + \"\\n\\n\" + runtime + \"\\nPurged \" + msg.payload.data.keep_days + \" days data.\" \n\nvar newpayload = {\n  \"data\":\n  {\n    \"message\": `${finalmsg}`,\n    \"title\": \"HA Database Purge\"\n  }\n}\n\nmsg.payload = newpayload\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":940,"y":920,"wires":[["a3fde5e3.c7ad88"]]},{"id":"a3fde5e3.c7ad88","type":"api-call-service","z":"7f52f4e2.25e814","name":"Notify HA (Persistant)","server":"112d9be2.b9d714","version":1,"debugenabled":false,"service_domain":"notify","service":"persistent_notification","entityId":"","data":"{}","dataType":"json","mergecontext":"","output_location":"","output_location_type":"none","mustacheAltTags":false,"x":1100,"y":860,"wires":[[]]},{"id":"112d9be2.b9d714","type":"server","name":"Home Assistant","legacy":false,"addon":false,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true}]
7 Likes
32

This is brilliant, thanks for sharing, added to Node-Red and deployed in minutes, works like a champ!