Setting Up Enterprise PostgreSQL Extension with HiveMQ Platform Operator

This guide provides detailed steps for configuring the Enterprise PostgreSQL Extension with the HiveMQ Platform Operator. Ensure you meet the specified prerequisites before proceeding.

Prerequisites:

1. Helm version v3+

2. Running Kubernetes cluster version 1.18.0 or higher

3. kubectl latest version

 Instructions

1. Deploy PostgreSQL: (This step is optional if you already have a running Postgres database with all required tables and data).

   1. Add the Bitnami Helm repository:

      helm repo add bitnami https://charts.bitnami.com/bitnami

      Create a postgres_values.yaml file to configure Postgres deployment:

      global: postgresql: auth: password: password postgresPassword: password username: hivemq primary: initdb: scriptsConfigMap: postgresql-db-init

      Create a ConfigMap called postgresql-db-init containing the create-table.sql script this file is located inside hivemq-postgresql-extension/conf/examples/, which creates required tables.
      
      

      kubectl create configmap postgresql-db-init --from-file create-table.sql

      Deploy Postgres using Helm:

      helm upgrade postgres --install bitnami/postgresql --values postgres_values.yaml

      Verify the status of the pod:

      kubectl get pods

      If an error occurs, check the pod logs:

      kubectl logs <pod name>

      Connect to the Postgres pod to verify the connection:

      psql --host 127.0.0.1 -U postgres -d postgres -p 5432

      Use the following commands in the Postgres shell to interact with the database:

      • \l: List the databases.

      • \c <db name>: Connect to a specific database.

      • \dt: List the tables from the connected database.

      • select * from users;

2. Deploy HiveMQ with PostgreSQL extension

   1. Generate hivemq_values.yaml:

      Deploy HiveMQ using the HiveMQ Platform and generate the hivemq_values.yaml file:

      helm show values hivemq/hivemq-platform > hivemq_values.yaml

   2. Configure PostgreSQL extension License:

      Follow the specific steps outlined for configuring the HiveMQ and PostgreSQL extension license. Setting Up HiveMQ License for Your HiveMQ Cluster using HiveMQ Platform Operator

   3. Create config.xml for PostgreSQL extension:

      1. Examples of the config.xml file are in the extension folder under conf/examples.

      2. Configure the postgresql with your database name, host, username, and password.

      3. Configure the <mqtt-to-postgresql-route>

      4. Please refer to the example:

         <hivemq-postgresql-extension xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="config.xsd"> <postgresqls> <postgresql> <id>my-postgresql-id</id> <host>postgres-postgresql</host> <port>5432</port> <database>postgres</database> <username>hivemq</username> <password>password</password> </postgresql> </postgresqls> <mqtt-to-postgresql-routes> <!-- insert every message using the INSERT STATEMENT processor --> <mqtt-to-postgresql-route> <id>my-mqtt-to-postgresql-route-insert</id> <postgresql-id>my-postgresql-id</postgresql-id> <mqtt-topic-filters> <mqtt-topic-filter>test/#</mqtt-topic-filter> </mqtt-topic-filters> <processor> <insert-statement> <table>mqtt_to_postgresql_table</table> <batching>true</batching> <columns> <column> <name>topic</name> <value>mqtt-topic</value> </column> <column> <name>payload_utf8</name> <value>mqtt-payload-utf8</value> </column> </columns> </insert-statement> </processor> </mqtt-to-postgresql-route> <!-- insert message using the STATEMENT TEMPLATE processor --> <mqtt-to-postgresql-route> <id>my-mqtt-to-postgresql-route-template</id> <postgresql-id>my-postgresql-id</postgresql-id> <mqtt-topic-filters> <mqtt-topic-filter>help/#</mqtt-topic-filter> </mqtt-topic-filters> <processor> <!-- please use absolute path here-> <statement-template>/opt/hivemq-4.24.0/extensions/hivemq-postgresql-extension/conf/statement-template.sql</statement-template> </processor> </mqtt-to-postgresql-route> </mqtt-to-postgresql-routes> </hivemq-postgresql-extension>

   4. Create ConfigMap for PostgreSQL configuration

      kubectl create configmap postgresql-config --from-file config.xml --from-file statement-template.sql -n <namespace>

   5. Deploy HiveMQ Platform Operator:

      helm install platform-op hivemq/hivemq-platform-operator -n <namespace>

   6. Edit hivemq_values.yaml: Modify the hivemq_values.yaml file to include PostgreSQL-related changes.

      1. Configure the ConfigMap name created in the previous step. Set enabled: true to enable this extension.

         ... extensions: # HiveMQ Enterprise Extension for PostgreSQL # This extension does not support hot-reload of the configuration. - name: hivemq-postgresql-extension enabled: true # The ConfigMap name that contains the PostgreSQL extension configuration. configMapName: "postgresql-config" ...

      2. Deploy HiveMQ:

         helm upgrade --install -f hivemq_values.yaml hivemq hivemq/hivemq-platform -n <namespace>

      3. Check Pod Status:

         Verify that all hivemq pods are running.

         kubectl get pods -n <namespace>

      4. Verify Enterprise PostgreSQL Extension Start:

         Check the hivemq.log to confirm successful extension startup.

         kubectl logs <pod name> -n <namespace> | grep -v "started succ"

      5. Perform Quick Tests:

         Utilize the MQTT CLI to conduct quick tests.

 Related articles