Database

The technical documentation of the database microservice, which stores the data of the application.

  3 minute read  

The database microservice is the central component of the United Manufacturing Hub and is based on TimescaleDB, an open-source relational database built for handling time-series data. TimescaleDB is designed to provide scalable and efficient storage, processing, and analysis of time-series data.

You can find more information on the datamodel of the database in the Data Model section, and read about the choice to use TimescaleDB in the blog article.

How it works

When deployed, the database microservice will create two databases, with the related usernames and passwords:

  • grafana: This database is used by Grafana to store the dashboards and other data.
  • factoryinsight: This database is the main database of the United Manufacturing Hub. It contains all the data that is collected by the microservices.

Then, it creates the tables based on the database schema.

If you want to learn more about how TimescaleDB works, you can read the TimescaleDB documentation.

Kubernetes resources

  • StatefulSet: united-manufacturing-hub-timescaledb
  • Service:
    • Internal ClusterIP for the replicas: united-manufacturing-hub-replica at port 5432
    • Internal ClusterIP for the config: united-manufacturing-hub-config at port 8008
    • External LoadBalancer: united-manufacturing-hub at port 5432
  • ConfigMap:
    • Patroni: united-manufacturing-hub-timescaledb-patroni
    • Post init: timescale-post-init
    • Postgres BackRest: united-manufacturing-hub-timescaledb-pgbackrest
    • Scripts: united-manufacturing-hub-timescaledb-scripts
  • Secret:
    • Certificate: united-manufacturing-hub-certificate
    • Patroni credentials: united-manufacturing-hub-credentials
    • Users passwords: timescale-post-init-pw
  • PersistentVolumeClaim:
    • Data: storage-volume-united-manufacturing-hub-timescaledb-0
    • WAL-E: wal-volume-united-manufacturing-hub-timescaledb-0

Configuration

There is only one parameter that usually needs to be changed: the password used to connect to the database. To do so, set the value of the db_password key in the _000_commonConfig.datastorage section of the Helm chart values file.

Environment variables

Environment variables
Variable nameDescriptionTypeAllowed valuesDefault
BOOTSTRAP_FROM_BACKUPWhether to bootstrap the database from a backup or not.int0, 10
PATRONI_KUBERNETES_LABELSThe labels to use to find the pods of the StatefulSet.stringAny{app: united-manufacturing-hub-timescaledb, cluster-name: united-manufacturing-hub, release: united-manufacturing-hub}
PATRONI_KUBERNETES_NAMESPACEThe namespace in which the StatefulSet is deployed.stringAnyunited-manufacturing-hub
PATRONI_KUBERNETES_POD_IPThe IP address of the pod.stringAnyRandom IP
PATRONI_KUBERNETES_PORTSThe ports to use to connect to the pods.stringAny[{"name": "postgresql", "port": 5432}]
PATRONI_NAMEThe name of the pod.stringAnyunited-manufacturing-hub-timescaledb-0
PATRONI_POSTGRESQL_CONNECT_ADDRESSThe address to use to connect to the database.stringAny$(PATRONI_KUBERNETES_POD_IP):5432
PATRONI_POSTGRESQL_DATA_DIRThe directory where the database data is stored.stringAny/var/lib/postgresql/data
PATRONI_REPLICATION_PASSWORDThe password to use to connect to the database as a replica.stringAnyRandom 16 characters
PATRONI_REPLICATION_USERNAMEThe username to use to connect to the database as a replica.stringAnystandby
PATRONI_RESTAPI_CONNECT_ADDRESSThe address to use to connect to the REST API.stringAny$(PATRONI_KUBERNETES_POD_IP):8008
PATRONI_SCOPEThe name of the cluster.stringAnyunited-manufacturing-hub
PATRONI_SUPERUSER_PASSWORDThe password to use to connect to the database as the superuser.stringAnyRandom 16 characters
PATRONI_admin_OPTIONSThe options to use for the admin user.stringComma separated list of optionscreaterole,createdb
PATRONI_admin_PASSWORDThe password to use to connect to the database as the admin user.stringAnyRandom 16 characters
PGBACKREST_CONFIGThe path to the configuration file for Postgres BackRest.stringAny/etc/pgbackrest/pgbackrest.conf
PGDATAThe directory where the database data is stored.stringAny$(PATRONI_POSTGRESQL_DATA_DIR)
PGHOSTThe directory of the runnning databasestringAny/var/run/postgresql
Last modified April 17, 2023: build: version 0.9.13 (6cb0a01)