LabKraken Database Logger

This is a simple Python asyncio database logger for the LabKraken data acquisition daemon. It can connect to an MQTT broker and stream the data into a PostgreSQL or Timescale database.

Setup

The Kraken logger is best installed via the Docker repository provided with this repository.

... via docker-compose

Example docker-compose.yml for the Kraken logger:

services:
  db_timescale_sensors:
    image: timescale/timescaledb
    container_name: db_timescale_sensors
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432/tcp

  database_logger:
    image: ghcr.io/patrickbaus/database_logger
    container_name: database_logger
    restart: always
    depends_on:
      - db_timescale_sensors
    environment:
      DATABASE_HOST=db_timescale_sensors
      DATABASE_USER=kraken
      DATABASE_PASSWORD=kraken
      MQTT_HOST=my-mqtt-broker

How to extend this image

The Kraken logger image can be configured in many ways and the example given above is a minimalistic example for educational purposes only. The easiest way is to use environment variables.

Environment Variables

The environment variables can be used to configure the connection options for both the MQTT server and the PostgreSQL server. For passwords, it is recommended to use docker secrets and the corresponding variable that ends with _FILE.

DATABASE_HOST

This variable defines the hostname of the PostgreSQL database. It is a mandatory parameter. Use it in conjunction with the DATABASE_PORT variable to set the database connection parameters.

DATABASE_PORT

The port used by the database. The default is port 5432.

DATABASE_USER

The username used for authentication to the database. This user should have minimal access privileges. The privileges required are SELECT and INSERT on table sensor_data and SELECT on table sensors. Using docker secrets is the preferred way though and any password set via secrets will take precedence.

DATABASE_USER_FILE

The mount point of the Docker secret from which the DATABASE_USER will be read. Using Docker secrets is a secure way to inject a secret into a container as it is not part of the Docker image and cannot be extracted when the container is shut down. An example implementing docker secrets is shown below.

services:
  database_logger:
    image: ghcr.io/patrickbaus/database_logger
    container_name: database_logger
    restart: always
    depends_on:
      - db_timescale_sensors
    environment:
      DATABASE_HOST=db_timescale_sensors
      DATABASE_USER_FILE=/run/secrets/database_logger_user
      DATABASE_PASSWORD_FILE=/run/secrets/database_logger_password
      MQTT_HOST=my-mqtt-broker
    secrets:
      - database_logger_user
      - database_logger_password

secrets:
  database_logger_user:
    file: ./docker-database_logger_user.secret
  database_logger_password:
    file: ./docker-database_logger_password.secret

The file docker-database_logger_user.secret is located in the same folder as the docker-compose.yml file and contains the user password as a simple string. This file will be mounted into the docker container at runtime to provide access to the password.

DATABASE_PASSWORD

The password used for authenticating the DATABASE_USER. Using docker secrets is the preferred way though and any password set via secrets will take precedence.

DATABASE_PASSWORD_FILE

The mount point of the Docker secret from which the DATABASE_PASSWORD will be read. Using Docker secrets is a secure way to inject a secret into a container as it is not part of the Docker image and cannot be extracted when the container is shut down. An example can be found above.

DATABASE_NAME

The name of the database that contains the two tables sensors and sensor_data. By default, this is sensors

MQTT_HOST

The hostname of the MQTT broker used to publish the sensor data.

MQTT_PORT

The port used to connect to the MQTT broker. By default, this is 1883.

MQTT_CLIENT_ID

If the MQTT broker supports persistent sessions an MQTT_CLIENT_ID can be set to make sure there is no data loss during reconnects. LabKraken publishes all messages with a quality of service (QOS) tag set to 2. This means that all subscribers will receive a message exactly once. Using a custom client id ensures that the messages will get delivered as soon as a disconnected client comes back online. The broker will store the messages for the client until that happens. By default, a random client id is used and persistence is not enabled.

APPLICATION_LOG_LEVEL

Changes the logging verbosity of the Kraken logger. The options are taken from the Python logging module and can be set DEBUG, INFO, WARNING, ERROR, CRITICAL. The default log level is INFO. For more details see Logging.

Logging

The database logger, by default, only logs connection attempts and errors. Setting the log level to DEBUG will print all data events received and stored in the database. To keep the log file from quickly growing to enormous size it is recommended to limit its size in the docker-compose.yml file.

services:
  database_logger:
    image: ghcr.io/patrickbaus/database_logger
    container_name: database_logger
    restart: always
    depends_on:
      - db_timescale_sensors
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

This example uses the default jason-file logger and limits the size of the logs to three files of at most 10 MB in size. The three files will be rotated when full.

Versioning

I use SemVer for versioning. For the versions available, see the tags on this repository.

Documentation

I use the Numpydoc style for documentation.

Authors

• Patrick Baus - Initial work - PatrickBaus

License

This project is licensed under the GPL v3 license - see the LICENSE file for details.