I configured my Home Assistant influxdb integration to log absolutely everything, but my InfluxDB database grew out of control.
After setting up proper filtering for the entities I really needed, I had to manually delete (drop) all the unwanted measurements.
First, I dumped all the measurements in a text file :
influx -database ha -username admin -execute "show measurements"
With the influx command line tool and a bit of bash scripting, I was able to send an InfluxQL command DROP MEASUREMENT, but often some measurements were not dropped (still displayed with « SHOW MEASUREMENTS »), and sometimes still had some records.
This is a known issue and it won’t probably be fixed in versions 1.x.
Finally, I ended up writing a « quick and dirty » python script to drop the measurements listed in a text file.
It performs the following steps:
- Reads the measurement to drop, line by line, from a text file
- Issues a SHOW MEASUREMENT query, and iterates through the results until a match is found
- Tries to display the count of points found
- Executes the DROP MEASUREMENT query
- Attemps to display the points count again (but I never saw this happening) or, if the measurement is not found, considers it was successfully dropped
Ensure you have installed the influxdb pip package :
pip install influxdb
Here is the script :
import time
from influxdb import InfluxDBClient
client = InfluxDBClient('nas', 8086, 'admin', '', 'ha')
file = open("//nas/data/ms-2025-01-04-delete.txt")
for line in file :
ligne = line.strip()
print()
print("%s : " % ligne, end="")
measurements = client.get_list_measurements()
for measurement in measurements :
if ligne == measurement['name'] :
print("found, ", end="")
result = client.query('select count(value) from "' + ligne + '";')
try:
print("count before: %d, " % result.raw['series'][0]['values'][0][1], end="")
except Exception :
print("N/A, ", end="")
client.drop_measurement(ligne)
result = client.query('select count(value) from "' + ligne + '";')
try:
count_after = result.raw['series'][0]['values'][0][1]
print("count after: %d" % count_after, end="")
except Exception :
print("drop OK (not found)", end="")
file.close()