Home-assistant_v2.db doesnt purge

Configuration
21

UPDATE: I just tested my recorder.purge automation and can verify that it works. Here’s what I did:

- alias: Database Purge
  trigger:
    platform: time
    at: '12:00:00'
  action:
    - service: recorder.purge
      data:
        keep_days: 10
        repack: true

Again, the key is the repack option being set to true, otherwise, filesize won’t be reduced. Hopefully, this helps you out.

22

Their question was how to add the repack option under the configuration of the recorder component in configuration.yaml or if it is implied repack: true. Not in an automation.

EDIT: I just tested this in my development/testing sandbox by adding repack: true to my recorder configuration and it throws an error on restart saying it is an invalid config. In my production environment (without the repack option) I can watch my database size grow and shrink day to day. I believe the recorder automatically repacks to shrink the database. There is a note in the config docs that says the repack option is only supported for SQLite. I use MySQL on a separate MariaDB 10 server.

23

I’m using MariaDB 10 also, and I found Purge just straight up not work for me.

When my DB gets too big I just create a new Database and update my config file, it’s a little clunky but it works

24

In your case, use the “purge_keep_days:” option in your config under recorder:. anything older than that gets automatically purged by HA. My config is below and I don’t have any problems with database size and it correctly purges.

recorder:
  db_url: !secret mysql_db
  purge_interval: 1
  purge_keep_days: 28
  exclude:
    domains:
      - camera
      #- group
      #- input_boolean
      #- input_number
      #- input_select
      #- input_text
      - mqtt
1 Like
25

Repack is generally discouraged because it has a number of problems.

The normal purge will not shrink the database but it should mostly make it stop growing because the purged space will be reused.

26

The doc says “keep_purge_days” may not reduce db size and only repack will.
Caution : You’ll need to ensure there’s sufficient diskspace left as large as the DB pre-purge.

Repack isn’t a config option but you’ll need to call the “recorder.purge” service with following json:
{“keep_purge_days”:“2”,
“repack” : “true”}

Mine went from 373MB down to 28MB. Will let you know if I get myself into any troubles because of this.

27 
- id: db_purge 
  alias: Database Purge
  trigger:
    platform: time
    at: '12:00:00'
  action:
    - service: recorder.purge
      data:
        keep_days: 60
        repack: true

I used the above in automations.yaml and went from a 12G db to 346M. I had recorder set to purge_interval: 1 and purge_keep_days: 60. it was doing its job but the db had grown large before that. Everything has been working as expected post repack for me.

1 Like
28

This is an important and often overlooked point. If the database is large, already, reducing he amount of data you pump to is doesn’t reduce its size. My experience is also that repacking, or deleting the database is the only solution. Bear in mind that the process takes some time. I thin my 4Gb DB took maybe 10 minutes on my elderly lapbook server.

29

Nobody wants to lose their history. OTOH, if I have a card that shows the last 2 days of data, how do I look at the data in the file before that? It’s in there, but how do I get it out?!

Cheers,
Ambi

30

You can look back a week in the history.