For more than half a year now I have been struggling with the fact that my database doesn’t purge. I know that this problem was fixed earlier in previous releases, but no, what I just didn’t do, my database is only growing.
Currently, my instances are working with databases in containers. I created containers from stacks, also I created a container, and then manually created a user, gave him all the rights, created a database for him… but still: purge_keep_days: 1 is not working.
Tell me what am I doing wrong? Is there some magical way to make the database clean up by Home Assistant? Starting the purge service only spoils the existing database, in the log I see warnings that one or the other field is missing… after which I just recreate the db.
I am currently using the official Postgres docker container with version 13.
Any suggestions? Or maybe share your comments? Thanks.
What version of Home Assistant are you using? When you build the database in Postgresql, do you let Home Assistant do it or are you doing it manually out side HA?
What do you have auto-purge set to in your config? Also if you call the recorder.purge service manually does that work fine or does that not work either? Trying to understand if its a problem with the purge process itself or the scheduler that’s supposed to be running the purge process nightly.
[EDIT] I also see there’s a note for additional setup steps required for Postgres in particular. I assume you saw that and have followed those instructions right? Looks like its a few additional steps then just running the docker container and pointing HA at it to get Postgres working correctly.
And btw, in doc auto_purge boolean (optional, **default: true**) so we didn’t need to set it into configuration, I used only purge_keep_days: 1 and if I set it, auto purge is watch this parameter and db would be purge it after 1 day, right?
Yes, auto_purge working with postgresql, my database has been staying at 65 since I turned the purge on maybe 6 months ago. Here is a picture of the oldest state table record today. I think you are correct about the default options, but this is what is working for me:
My question was whether you were creating a blank database named homeassistant in postgresql server with correct rights and user/password. Then shutting down Home Assistant, changed configuration.yaml to aim recorder function at this database. Then restarting Home Assistant. At this point, Home Assistant should build all of its tables and indexes. Going this path is probably the most ‘correct’ way to make sure the tables and stuff are created correctly. I admin, it have messes around with the database config and build some of the tables and indexes using postgresql restore and manual methods, probably not proper, but my set up has been rock solid on postgresql servers for 2 years.
These are steps I did to install Postgresql 12 in docker on Ubuntu 20, note I did a postgresql database restore to recreate my home assistant tables, indexes and constraints after the last step below, I had postgresql running on another machine. So this way I did a restore. On that original machine, I believe did these same initial steps. Then configured Home Assistant to aim it’s ‘recorder’ at the postgresql with correct IP and credentials. Home Assistant then created all it’s tables, indexes and constraints.
postgresql 12 docker install ubuntu mac mini 2012
202007181125
# install client on workstation
sudo apt install postgresql-client-common
sudo apt-get install postgresql-client
# https://hub.docker.com/_/postgres
docker pull postgres:12.3
docker run -d \
--name="postgres-12.3" \
--net=host \
-e POSTGRES_PASSWORD=postgres \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v /home/user/postgres-data:/var/lib/postgresql/data \
postgres:12.3
# postgres data files in : /var/lib/postgresql/data
psql -h 192.168.1.10 -U postgres -W
create user "user" with password '****';
create database homeassistant;
grant all privileges on database homeassistant to "user";
# test
psql -h 192.168.1.10 -d homeassistant -U user -W
docker stop postgres-12.3
docker start postgres-12.3
You’re right, you don’t have to set it. I just wanted to confirm it wasn’t false for whatever reason.
Personally I’m probably out of my depth at this point since I don’t have a postgres setup and am not familiar with it so I’ll step out and let you two at it. If you and David can’t crack it I’d recommend taking the issue to discord or reporting a bug in github to get some help from one of the devs.
Growing database size does not mean that HA is not deleting records, you should query the oldest records in the events and states tables. I showed you a screen show if my oldest record staying within the recorder delete parameters.
Is there anything in Home Assistant log?
And perhaps turn on debugging for recorder and see if it shows any useful info:
2021-02-12 04:11:42 DEBUG (Recorder) [homeassistant.components.recorder] Sending keepalive
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:00.005139+00:00
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.041714s
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007076s
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 03:12:13.292928+00:00
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6405 states
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6477 events
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:00.865098+00:00
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.014042s
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006014s
2021-02-12 04:12:00 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 04:12:13.708944+00:00
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6405 states
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6477 events
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:01.449738+00:00
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.010312s
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006148s
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 05:12:14.220935+00:00
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6443 states
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6515 events
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:01.918361+00:00
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006544s
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.004470s
2021-02-12 04:12:01 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 06:12:22.379462+00:00
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6458 states
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6530 events
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:02.391472+00:00
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007196s
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.005162s
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 07:12:22.892455+00:00
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6438 states
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6510 events
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:02.927706+00:00
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007488s
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.004724s
2021-02-12 04:12:02 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 08:12:25.309266+00:00
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6433 states
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6505 events
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:03.398127+00:00
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007405s
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.004427s
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 09:12:25.458971+00:00
2021-02-12 04:12:03 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6426 states
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6498 events
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:04.018995+00:00
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.019163s
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006779s
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 10:12:25.626195+00:00
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6423 states
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6495 events
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:04.602674+00:00
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.033448s
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.004824s
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 11:12:25.866902+00:00
2021-02-12 04:12:04 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6432 states
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6504 events
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:05.097650+00:00
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006513s
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006123s
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 12:12:26.053590+00:00
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6428 states
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6507 events
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:05.651956+00:00
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.013647s
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.009292s
2021-02-12 04:12:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 13:12:26.182925+00:00
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6379 states
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6451 events
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:06.299837+00:00
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.029085s
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.008192s
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 14:12:26.308256+00:00
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6172 states
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6845 events
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:06 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:06.992581+00:00
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.025857s
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006094s
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 15:12:45.045788+00:00
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6093 states
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6165 events
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:07.446323+00:00
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007493s
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.020768s
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 16:12:45.152224+00:00
2021-02-12 04:12:07 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6089 states
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6161 events
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:08.039004+00:00
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.011621s
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006845s
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 17:12:45.514787+00:00
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6214 states
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6290 events
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:08.507060+00:00
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.008668s
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.005658s
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 18:12:45.632663+00:00
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6448 states
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6520 events
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:08.981906+00:00
2021-02-12 04:12:08 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.010351s
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007857s
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 19:12:45.760858+00:00
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6444 states
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6516 events
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:09.515348+00:00
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.009848s
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006548s
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 20:12:45.880878+00:00
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6448 states
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6520 events
2021-02-12 04:12:09 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:10.001826+00:00
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007176s
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007436s
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 21:12:45.903793+00:00
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6438 states
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6510 events
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:10.472478+00:00
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006724s
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007203s
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 22:12:46.061163+00:00
2021-02-12 04:12:10 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6429 states
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6499 events
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:11.068530+00:00
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.013287s
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.012534s
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-08 23:12:46.203059+00:00
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6433 states
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6505 events
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:11.583478+00:00
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.020037s
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007373s
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-09 00:12:46.553413+00:00
2021-02-12 04:12:11 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6429 states
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6501 events
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder] Sending keepalive
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:12.044150+00:00
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.011831s
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006333s
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-09 01:12:46.749122+00:00
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6422 states
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6494 events
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-02-09 02:12:12.511101+00:00
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.007502s
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.006869s
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before 2021-02-09 02:12:12.511101+00:00
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6362 states
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 6432 events
2021-02-12 04:12:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 2 recorder_runs
Seems purge is works like it should. Considering that this is my staging server, I can now try to return the current settings to my production.
Earlier I said that I don’t see the difference (decrease) in the database size graph. Of course, on my staging server I hardly see this, I got used purge in my prod with big datasize of db, and I can get difference in production only, since is there more data will be purged, and after such cleaning I had can see a drop in size on the graph.
Okay guys, thanks for the support, I will return the settings to my production and watch what happens again.
I don’t know. did the message ‘Purging hasn’t fully completed yet’ continue after the last log entry you published above?
As I stated above, I think you should use a SQL query tool and see what the oldest records are in the states and events tables, both before and after the recorder purge runs. I think you can manually call the purge function from the developer console.