Recorder.purge not working

[dzkahn]

I have this in my configuration.yaml

recorder:
  purge_keep_days: 2
  purge_interval: 1
  exclude: 
    entities:
      - binary_sensor.family_room_motion_detector
      - binary_sensor.hallway_motion_detector

home-assistant_v2.db continues to grow and it is not being purged, the exclude entities is working fine

Any ideas

[tom_l]

Have you been restarting every day?

Restarting home assistant resets the purge interval timer.

[dzkahn]

Yes, I have restarting often as I have been working on my Home Assistant a bunch the last few days. What you said makes sense, why didn’t I think about that.

thanks for your help

[DavidFW1960 (David)]

Just throwing it out there…

MariaDB growing too big... Can't get it smaller

Where did this rumor come from? The recorder should purge after 30 minutes if it is due at startup. The database is rarely the problem. Most of the delay with logbook and history is in HA code, either the Python core or the frontend. A faster CPU in those places helps a lot.

I don’t believe it as my experience corresponds with yours but there seems to be a PR that’s going to correct this soon.

[tom_l]

I’m still confused after reading that thread. Is it true or a rumour?

The code tries to get back into sync after a restart. So if it purged last night at 9PM and you restart today at 6PM, it should purge in about 3 hours’ time (with a lower limit of 30 minutes).

So I guess the fully correct way to state it is that it can take up to 24 hours.

[DavidFW1960 (David)]

I’m confused as well however I believe what we both said is correct… is if you restart HA it resets.

[Jan.Schmidt]

@dzkahn

Any ideas

Hi,
i dont use this because its “global”.
“some” of my sensordata should be purged the next day some of them should keeped over a longer period.

to add this you need sqlite3 and a external script - running as cronjob on your system.

eg: i have a sensor “next holidays (next_free)” or “next garbage date & type (sensor.fes)”

It’s nice to knew the actual state but the state from yesterday is just garbage.

Here is a cutout from my script - save it somewhere, make it executable
check if your database is in the same path as on my setup
(/usr/share/hassio/homeassistant/home-assistant_v2.db)

all the “magic” happens here:
drop everything from sensor.fes older than 1 day:
statement=“$older_states_than 1 days’) AND entity_id LIKE ‘%sensor.fes%’”

and run the script \edit with sudo./edit

When the output looks good for you - change remove the # in front of the sqlite3 and add them infront of the echo lines.

#!/bin/bash

#var
db=/usr/share/hassio/homeassistant/home-assistant_v2.db
older_events_than="time_fired < datetime('now','-"
older_states_than="created < datetime('now','-"

#stop HA
docker stop homeassistant

#backup db
cp $db ~/home-assistant_v2.db

events() {
         echo sqlite3 $db "SELECT * FROM events WHERE $statement;"
         #sqlite3 $db "DELETE FROM events WHERE $statement;"
         }

states() {
         echo sqlite3 $db "SELECT * FROM states WHERE $statement;"
         #sqlite3 $db "DELETE FROM states WHERE $statement;"
         }

#events
statement="$older_events_than 1 days') AND event_data LIKE '%%next_free%%'"
events

statement="$older_events_than 1 days') AND event_data LIKE '%sensor.fes%'"
events

#states
statement="$older_states_than 1 days') AND entity_id LIKE '%sensor.fes%'"
states

#rebuild db
sqlite3 $db "vacuum;"
#start HA
docker start homeassistant

cheers

[amelchio]

@tom_l

I’m still confused after reading that thread. Is it true or a rumour?

I dug into the history. It seems that it used to be true, so not a rumor. However, it is no longer true since November 2017 where PR #10279 was merged.

[palengh (Pål Engh)]

@tom_l

Restarting home assistant resets the purge interval timer.

This is true. I notice the created, changed and last opened time stamps on the db file ate all identical to the time I executed the recorder.purge command.

What if some script back-dated the file date of the db file everytime HA starts?
… or is the purge interval timer a separate entity within HA?

[tom_l]

As of version 0.109, the default is for purge to run at 04:12 every morning, whether you have restarted or not.

Recorder - The recorder configuration purge_interval has been deprecated. The recorder database is now purged every night. This can be disabled with the new auto_purge configuration though that is usually not recommended. - (@amelchio - #33646) (recorder docs)

[BrunoN (Bruno Nogueira)]

Hi everyone!

I’m having issues with the purge settings. I have been deleting the file manually because the auto purge nor a automation works.

I have the following configuration within configuration.yaml but it doesn’t erase anything.

recorder:
  auto_purge: true

I’ve also tried to set an automation or even run the service recorder.purge right away but the data is still there.

Any idea of what this can be?

I’m using the default db.

Edit: I’m using the version 0.112.2

Thanks!

[tom_l]

If that is the only config you have, then you are using the default purge_keep_days of 10.

So it won’t begin purging anything until 10 days after you added the recorder (I.e. the data is 10 days old).

[BrunoN (Bruno Nogueira)]

Thank for your input!

I’ve added the purge_keep_days, restarted home assistant and runned the service again, but no luck… The history from the last days is still there

recorder:
    auto_purge: true
    purge_keep_days: 1

The auto_purge shoudn’t auto delete everything at 4AM?

Thanks!

[DavidFW1960 (David)]

@BrunoN

The auto_purge shoudn’t auto delete everything at 4AM?

it will autodelete everything older than 1 day. The database size won’t change with those settings though. To make it run now, you can call the service from dev tools.

[tom_l]

And to actually delete the rather than just mark as “deleted” you have to repack.

Read this: https://www.home-assistant.io/integrations/recorder/#service-purge