My InfluxDB has been storing everything everything for 4 years or more now and the database and backup has reached 17Gb. With the help of others on different threads in this forum, I’ve now set a retention policy (it was infinite) of 800d and added a list of Includes with the only stuff I want to keep. Seems to be working as all the stuff I haven’t ‘included’ doesn’t show recent data when I interogate using Grafana. All the includes are still current. So far so good.
But now I want to 1) delete stuff from the database, and then 2) purge, or compact it, to reduce the physical size of the database file and my backups.
I’ve read some posts that seem to tell me how to delete but the suggestions don’t work. The keyword ‘DELETE’ doesn’t even seem to be recognised in the InfluxDB panel SQL box. I’m guessing the suggestions are old and refer to earlier versions and the methods have now changed. I haven’t seen anything on purging.
Here’s a pic of the InfluxDB panel set to Explore with a random query selected (obviously something I’ve not ‘Included’).
I’ve found another Thread where I’ve resolved the Deleting. It’s a bit long winded so I’m trying to see if wildcards can speed things up. As it is I’m currently using the ‘picker’ to create a query to SELECT a block of entity_id s that I want to delete, and then modify the query to do the deleting.
ie the Select query might look like this
SELECT mean("value") AS "mean_value"
FROM "homeassistant"."autogen"."%"
WHERE time > :dashboardTime:
AND time < :upperDashboardTime:
AND ("entity_id"='battery_level'
OR "entity_id"='m2003j15sc_battery_level'
OR "entity_id"='m30_battery_level')
GROUP BY time(:interval:)
FILL(null)
and I edit it down to this
USE "homeassistant";
delete FROM "%"
WHERE "entity_id"='battery_level'
OR "entity_id"='m2003j15sc_battery_level'
OR "entity_id"='m30_battery_level'
It works. The entries disappear but I doubt it makes the database file any smaller so still hoping for someone to point me the way on that.
Hey there, I have similar problem. I am new to HA and I created some entity to log my monthy km for my car leasing. My bad, it tracked wrongly before November was okay, since the counter was set correctly. But the data from October is a mess.
it counted in Mm (million meters!) instead of km.
all data from "this year so far is simply not “nicely” displayed.
Is there a possibility to delete the data from influxDB from october or at least only for the entity “monatlich_gefahrene_km”?
I tried some commands, but I am serioulsy overstrained how to use those properly and I dont want to brick influxDB or something