How to manually use recorder purge service

Configuration
1

My home assistant db log size is getting very large and I can’t seem to purge the database. Any tips?

I tried the config script as well as the purge service. Not sure what I’m doing wrong. Can’t figure it out via the other threads. Thanks!

2

How large is large? Mine is currently just under 80 MB.

If you haven’t changed any of the recorder configuration settings, it should be purging automatically every day and keeping ten days worth of data. (I’ve been using the default.)

If your database is getting “too large”, then you should consider excluding some things. FWIW, here’s how I have mine configured:

recorder:
  exclude:
    domains:
      - group
      - history_graph
      - media_player
      - scan_store
      - zone
      - zwave
    entities:
      - sensor.date
      - sensor.time
      - sensor.date__time
3

My MySQL database is now at 3590.8 MB, and that is with 10 days of data.

What database do you run? Can you log in to it and query it?

If you can, then run the following SQL to verify that something is wrong.

select max(time_fired), min(time_fired)
from events;
4

Database is 148 mb after deleting it a week ago.

Recorder setting is set to purge every 2 days but nothing is happening.

I’ll try the exclusion settings you have. The database is SQL lite i believe.

1 Like
5

I’ve had troubles as well to get my database purged properly - hwne that happened, I use this in the /dev-service tab:

{
  "keep_days":"1",
  "repack":"true"
}

like this
image

18 Likes
6

I have recorder configured to purge every day and keep 5 days worth. But My DB has not purged since September. I just tried the purge service call above as well but no change.

MariaDB [homeassistant]> select min(created),max(created) from events;
+---------------------+---------------------+
| min(created)        | max(created)        |
+---------------------+---------------------+
| 2018-09-06 02:54:37 | 2019-01-28 20:42:01 |
+---------------------+---------------------+
1 row in set (38.60 sec)

MariaDB [homeassistant]> select min(created),max(created) from states;
+---------------------+---------------------+
| min(created)        | max(created)        |
+---------------------+---------------------+
| 2018-09-06 02:54:37 | 2019-01-28 20:41:59 |
+---------------------+---------------------+
1 row in set (14.86 sec)

Can I just delete all the data in events, recorder_runs and states?
I assume I should keep the schema_changes which contains just 1 row with schema version.

MariaDB [homeassistant]> show tables;
+-------------------------+
| Tables_in_homeassistant |
+-------------------------+
| events                  |
| recorder_runs           |
| schema_changes          |
| states                  |
+-------------------------+

Seems easier to just create a cron job to erase the data older than x days or just delete all once in a while.

7

How did you connect to MariaDB on hassio and do the purge? My recorder.purge is not working keep seeing this in the log… whatever that means “Error purging history: (MySQLdb._exceptions.IntegrityError)”

8

Figured it out for anyone who cares. Login to Hassio then run mysql -h 127.0.0.1 -u hass -p
then you can select the database etc.

Or alternatively you can just use another app like Sequel Pro for mac, just put your credentials and you are good to go, then you can select the tables and click truncate.

9

Hi

I’m having same issue with MariaDB.

The purge seems not to work

My config is

recorder:
  purge_keep_days: 7
  purge_interval: 1
  db_url: !secret mariadb_url

Even if I run it manually from the Services developer tools I have no decrease in the DB size.

I’ve downloaded the Sequel Pro for Mac, but which are the tables to be truncated and how should I do it?
I’m not familiar with this tool.

Thanks

1 Like
10

I can’t tell if a manual purge is working. I’m using the service

recorder.purge

With the following JSON

{"keep_days":"2", "repack":"false"}

Nothing is showing up in the Developer Tools - Log window. Should I see the purge command in there?

2 Likes
11

Hi all!
May be you have this error in MariaDB schema: