mqtt-to-postgresql

The technical documentation of the microservice mqtt-to-postgresql, which subscribes to an MQTT broker and stores messages in a PostgreSQL or timescaleDB database.

If you landed here from Google, you probably might want to check out either the architecture of the United Manufacturing Hub (to understand how we store messages efficiently from MQTT / Kafka in our PostgreSQL / TimescaleDB), or our knowledge section for more information on the general topics of IT, OT and IIoT.

This microservice is deprecated and should not be used anymore in production. Please use kafka-to-postgresql instead.

mqtt-to-postgresql subscribes to the MQTT broker (in the stack this is VerneMQ), parses incoming messages on the topic “ia/#” and stores them in the PostgreSQL / timescaleDB databases (if they are in the correct datamodel).

Getting started

Here is a quick tutorial on how to start up a basic configuration / a basic docker-compose stack, so that you can develop

docker-compose -f ./deployment/mqtt-to-postgresql/docker-compose-mqtt-to-postgresql-development.yml -env-file ./.env up -d -build

Message flow processing

The diagram below shows an abstract flow of an incoming MQTT message

MQTT message flow

Environment variables

This chapter explains the environment variables used by mqtt-to-postgresql

Variable nameDescriptionTypePossible valuesExample value
LOGGING_LEVELDefines which logging level is used, mostly relevant for developers. If logging level is not DEVELOPMENT, default logging will be usedstringanyDEVELOPMENT
CERTIFICATE_NAMESet to NO_CERT to allow non-encrypted MQTT accessstringany, NO_CERTNO_CERT
BROKER_URLThe MQTT broker URLstringIP, DNA nameunited-manufacturing-hub-vernemq-local-service:1883
POSTGRES_HOSTDatabase DNS name or IP address of the PostgreSQL databasestringall DNS names or IP addressesunited-manufacturing-hub
POSTGRES_USERThe username to use for PostgreSQL connectionsstringan existing user with access to the specified database in postgresqlfactoryinsight
POSTGRES_PASSWORDThe password to use for PostgreSQL connectionsstringallchangeme
POSTGRES_DATABASEThe name of the PostgreSQL databasestringan existing database namefactoryinsight
DRY_RUNIf set to true, the microservice will not write to the databasebooltrue, falsetrue
REDIS_URIURI for accessing redis sentinelstringall valid URIsunited-manufacturing-hub-redis-node-0.united-manufacturing-hub-redis-headless:26379
REDIS_URI2Backup URI for accessing redis sentinelstringall valid URIsunited-manufacturing-hub-redis-node-0.united-manufacturing-hub-redis-headless:26379
REDIS_URI3Backup URI for accessing redis sentinelstringall valid URIsunited-manufacturing-hub-redis-node-0.united-manufacturing-hub-redis-headless:26379
REDIS_PASSWORDPassword for accessing redis sentinelstringallchangeme
MY_POD_NAMEThe pod name. Used for tracing, logging and MQTT client IDstringallapp-factoryinput-0
MQTT_TOPICMQTT topic to listen in onstringanyia/#
Last modified November 23, 2022: Feat/linkfixes (#176) (120bad7)