InfluxDB: Time series database in Docker

An InfluxDB database is a database optimized for time data. For visualization InfluxDB is often used in combination with Grafana. Note: This article refers to InfluxDB version 1, information about version 2 will follow soon.

Docker Basics

Docker allows applications to be launched by command in a so-called container.
A container is an isolated environment independent of the operating system (OS):
When a container is first launched, Docker independently loads all the necessary sources
from the internet.
Docker can be installed on Windows, macOS or an Linux Distribution

Initialize database

Before we can use the container, a database must be created on it. The following command starts the container, creates the database and stops the container again afterwards. The password should be adjusted at this point, of course:

mkdir -p /docker
mkdir -p /docker/influxdb
docker run \
--rm -e INFLUXDB_DB=iobroker \
-e INFLUXDB_ADMIN_USER=admin \
-e INFLUXDB_ADMIN_PASSWORD=adminpassword \
-e INFLUXDB_USER=iobroker \
-e INFLUXDB_USER_PASSWORD=password4iobrokerdb \
-v /docker/influxdb:/var/lib/influxdb influxdb /init-influxdb.sh

The output should look something like this:

docker run --rm -e INFLUXDB_DB=ha -e INFLUXDB_ADMIN_USER=admin -e INFLUXDB_ADMIN_PASSWORD=???-e INFLUXDB_USER=ha -e INFLUXDB_USER_PASSWORD=??? -v /var/web/ha.libe.net/influxdb:/var/lib/influxdb influxdb /init-influxdb.sh
..
ts=2022-01-18T11:01:45.570263Z lvl=info msg="Executing query" log_id=0Z7DRsEl000 service=query query="CREATE DATABASE ha"
[httpd] 127.0.0.1 - - [18/Jan/2022:11:01:45 +0000] "POST /query?chunked=true&db=&epoch=ns&q=CREATE+DATABASE+ha HTTP/1.1" 200 57 "-" "InfluxDBShell/1.8.3" 06a1e3a9-784e-11ec-8002-0242ac110002 6635
/init-influxdb.sh: ignoring /docker-entrypoint-initdb.d/*
...
ts=2022-01-18T11:01:45.590635Z lvl=info msg="Closed service" log_id=0Z7DRsEl000 service=subscriber
ts=2022-01-18T11:01:45.590686Z lvl=info msg="Server shutdown completed" log_id=0Z7DRsEl000

Start InfluxDB

After initializing the container, I created my own network with "docker network create smart-home", the database can then be started with the following command:

docker run -d \
--name=influxdb \
--network=smart-home \
-p 8086:8086 \
--restart=always \
-v /docker/influxdb:/var/lib/influxdb influxdb

Alternatively, InfluxDB can be included via a Docker compose file when using multiple containers, see: Home Assistant Docker Conbee 2 and Zigbee2MQTT / deCONZ

Shell docker exec

When using the Docker container, the database can be managed via docker exec in the terminal

View and understand retention policy

bernhard@sox:~$ docker exec -it influxdb influx
Connected to http://localhost:8086 version 1.8.3
InfluxDB shell version: 1.8.3
...
Please set a database with the command "use <database>".
> use ha
Using database ha
> SHOW RETENTION POLICIES
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true

By default, a retention policy named "autogenous" is created when the database is created. The policy has an infinite retention and a "shrdGroupDuration" of seven days. "0s" as retention stands for infinite. When the retention is exceeded , shrdGroups are deleted, which would always delete blocks of seven days with the default setting. Accordingly, the shardGroupDuration has an influence on the retention if the value is other than 0s.

Display values and delete specific values

I had some false messages with the value "-127" from my temperature sensor in the beginning. To remove them a certain time value can be used.

Find out the key for the values: "SHOW SERIES ON ha"
Display of the values with "select time,value from "°C" where value < -100 limit 10"
Deleting the displayed values over its time value: "delete from "°C" where time = 1642559861967671040"

> SHOW SERIES ON ha
key
---
%,domain=sensor,entity_id=aussen_battery
%,domain=sensor,entity_id=aussen_humidity
%,domain=sensor,entity_id=bad_battery
%,domain=sensor,entity_id=bad_humidity
%,domain=sensor,entity_id=baldrian_battery
%,domain=sensor,entity_id=baldrian_humidity
...
°C,domain=sensor,entity_id=gefrierschrank_temperature
°C,domain=sensor,entity_id=heating_pufferspeicher
°C,domain=sensor,entity_id=heating_ruecklauf
°C,domain=sensor,entity_id=heating_vorlauf
°C,domain=sensor,entity_id=heating_waermetauscher
°C,domain=sensor,entity_id=heating_warmwasser

> select time,value from "°C" where value < -100 limit 10
name: °C
time                value
----                -----
1642559861967671040 -127
1642577899531245824 -127
1642582871417732096 -127
1642583544890542080 -127
1642590298966192128 -127
1642590767340711168 -127
1642590767352859136 -127
1642590767357784064 -127
1642590767358241024 -127
1642590767358556928 -127
> delete from "°C" where time = 1642559861967671040
> delete from "°C" where time = 1642577899531245824
> delete from "°C" where time = 1642582871417732096
> delete from "°C" where time = 1642583544890542080
> delete from "°C" where time = 1642590298966192128
> delete from "°C" where time = 1642590767340711168
> delete from "°C" where time = 1642590767357784064
> delete from "°C" where time = 1642590767358241024
> delete from "°C" where time = 1642590767358556928

Queries

https://docs.influxdata.com/influxdb/v1.3/query_language/functions/

Upgrade Influxdb 1 to 2

docker run -p 8086:8086 \
  -v ./influxdb:/var/lib/influxdb \
  -v ./influxdb2:/var/lib/influxdb2 \
  -e DOCKER_INFLUXDB_INIT_MODE=upgrade \
  -e DOCKER_INFLUXDB_INIT_USERNAME=ha \
  -e DOCKER_INFLUXDB_INIT_PASSWORD=pASSED \
  -e DOCKER_INFLUXDB_INIT_ORG=my-org \
  -e DOCKER_INFLUXDB_INIT_BUCKET=my-bucket \
  influxdb