InfluxDB - How to clean the database?

[MrGlad8 (Andree)]

Heya! So I have been using InfluxDB since the first time I started to use Home Assistant. Since then I have been doing a lot to my HA. And I guess there’s a lot of stuff in that database I could remove.
Is there a guide out there explaining how I easily delete parts I no longer need? Like sensor-data that I have removed etc…
Check my image, lets say I dont longer use that “kontroll_ikea_1_power” and want to remove all that stored backup. How do I do that? I see that in the next field there’s “free internal storage” ? I guess I should do something with that?

Thank you!

[tom_l]

First use exclude to stop recording things you don’t need.

Then delete the data for the excluded entities:

InfluxDB: Removing or deleting data

I submit this to assist others who may ned to know how to delete data from InfluxDB via the InfluxDB UI in HASSIO. Go to explore and query for the following SHOW MEASUREMENTS ON "<db_name> eg: homeassistant" SHOW TAG KEYS ON "<db_name>" FROM "<measurement_name> eg: °C" Query template: USE "<***db_name***>"; DELETE FROM "<***measurement_name***>" WHERE "<***tag***>" = '***tag_value***' AND time < '2021-04-04' Actual query eg: USE "homeassistant"; DELETE FROM "°C " WHERE "entity_id" = 'tas…

The actual entity will still be listed but that takes up very little room. I have not found a way to remove them entirely yet.

[MrGlad8 (Andree)]

Thanks a lot!
Frankly I dont know yet how to do this… I mean I dont want everything to be stored… but to use the Include will make me use ALOT of entities right? That list would still be long.
And to use exclude is also hard since that list is also long.
And sure I use an SSD with 200 or so GB but restoring that will take like 2 days since restoring my 1 or so GB today takes 40 min.
Any tips?

[tom_l]

You have two options.

1. Put up with backup issues to to large DB size. Or,

2. Put a bit of effort in to using include/exclude. Clever use of the domain and/or entity_glob options can reduce the amount of work required. There are about 200 entries in my InfluxDB include list. I use Watchman semi-regularly to make sure lists like this do not contain defunct entries.

[psyciknz]

Use retention policies to limit how much data is kept.

[tom_l]

Yeah ideally I would resample data older than a year and only keep daily max, min, and average. I have yet to work out how to do this.

[MrGlad8 (Andree)]

Yeha I feel maybe I should point out what entities I do want to monitor long term like energy, temperature etc with that include-feature and leave the rest. If I try to use the exclude I belive that list will be enormous.
But if I now change the influx config to only have a few enteties as include, will the database remove the rest? Or do I need to like set the database to 1 MB in order for it to clean it first? Would be nice though to keep those enteties I do want… but I guess thats a complex operation in InfluxDB?

[tom_l]

@MrGlad8

But if I now change the influx config to only have a few enteties as include, will the database remove the rest?

No. You still have to delete the existing data as per my post

[MrGlad8 (Andree)]

Right ok, thank you!

[Thomas717 (ThomasL)]

working with the lastest version of InfluxDB using HassOS. I find no way to enter /execute the commands mentioned above. I tried it using api_version 1 and 2, but no success.

I get messages informing me, that the respective keywords (like SHOW or USE) do not exist.

I have no idea how to proceed in my task of cleaning up the dataases.

[tom_l]

@Thomas717

working with the lastest version of InfluxDB using HassOS. I find no way to enter /execute the commands mentioned above.

Go to the InfluxDB add-on. Open the web-ui. Click the “Explore” option in InfluxDB’s left menu. There is a text box where you can submit queries there.