Kafka to Postgresql

The technical documentation of the kafka-to-postgresql microservice, which consumes messages from a Kafka broker and writes them in a PostgreSQL database.

Kafka-to-postgresql is a microservice responsible for consuming kafka messages and inserting the payload into a Postgresql database. Take a look at the Datamodel to see how the data is structured.

This microservice requires that the Kafka Topic umh.v1.kafka.newTopic exits. This will happen automatically from version 0.9.12.

How it works

By default, kafka-to-postgresql sets up two Kafka consumers, one for the High Integrity topics and one for the High Throughput topics.

The graphic below shows the program flow of the microservice.

Kafka-to-postgres-flow
Kafka-to-postgres-flow

High integrity

The High integrity topics are forwarded to the database in a synchronous way. This means that the microservice will wait for the database to respond with a non error message before committing the message to the Kafka broker. This way, the message is garanteed to be inserted into the database, even though it might take a while.

Most of the topics are forwarded in this mode.

The picture below shows the program flow of the high integrity mode.

high-integrity-data-flow
high-integrity-data-flow

High throughput

The High throughput topics are forwarded to the database in an asynchronous way. This means that the microservice will not wait for the database to respond with a non error message before committing the message to the Kafka broker. This way, the message is not garanteed to be inserted into the database, but the microservice will try to insert the message into the database as soon as possible. This mode is used for the topics that are expected to have a high throughput.

The topics that are forwarded in this mode are processValue, processValueString and all the raw topics.

Kubernetes resources

  • Deployment: united-manufacturing-hub-kafkatopostgresql
  • Secret: united-manufacturing-hub-kafkatopostgresql-certificates

Configuration

You shouldn’t need to configure kafka-to-postgresql manually, as it’s configured automatically when the cluster is deployed. However, if you need to change the configuration, you can do it by editing the kafkatopostgresql section of the Helm chart values file.

Environment variables

Environment variables
Variable nameDescriptionTypeAllowed valuesDefault
DEBUG_ENABLE_FGTRACEEnables the use of the fgtrace library. Not reccomended for productionstringtrue, falsefalse
DRY_RUNIf set to true, the microservice will not write to the databasebooltrue, falsefalse
KAFKA_BOOTSTRAP_SERVERURL of the Kafka broker used, port is requiredstringAnyunited-manufacturing-hub-kafka:9092
KAFKA_SSL_KEY_PASSWORDKey password to decode the SSL private keystringAny""
LOGGING_LEVELDefines which logging level is used, mostly relevant for developersstringPRODUCTION, DEVELOPMENTPRODUCTION
MEMORY_REQUESTMemory request for the message cachestringAny50Mi
MICROSERVICE_NAMEName of the microservice (used for tracing)stringAnyunited-manufacturing-hub-kafkatopostgresql
POSTGRES_DATABASEThe name of the PostgreSQL databasestringAnyfactoryinsight
POSTGRES_HOSTHostname of the PostgreSQL databasestringAnyunited-manufacturing-hub
POSTGRES_PASSWORDThe password to use for PostgreSQL connectionsstringAnychangeme
POSTGRES_SSLMODEIf set to true, the PostgreSQL connection will use SSLstringAnyrequire
POSTGRES_USERThe username to use for PostgreSQL connectionsstringAnyfactoryinsight
Last modified April 17, 2023: build: version 0.9.13 (6cb0a01)