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:

Helm version v3+
Running Kubernetes cluster version 1.18.0 or higher
kubectl latest version

\uD83D\uDCD8 Instructions

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;

Deploy HiveMQ with PostgreSQL extension

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
```
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

Create config.xml for PostgreSQL extension:

Examples of the config.xml file are in the extension folder under conf/examples.
Configure the postgresql with your database name, host, username, and password.
Configure the <mqtt-to-postgresql-route>, setting <use-authorization-key> to false and <use-authorization-role-key> to true.

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>

Create ConfigMap for PostgreSQL configuration

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

Deploy HiveMQ Platform Operator:

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

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

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"
...

Deploy HiveMQ:

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

Check Pod Status:
Verify that all hivemq pods are running.
```
kubectl get pods -n <namespace>
```
Verify Enterprise PostgreSQL Extension Start:
Check the hivemq.log to confirm successful extension startup.
```
kubectl logs <pod name> -n <namespace> | grep -v "started succ"
```
Perform Quick Tests:
Utilize the MQTT CLI to conduct quick tests.

If you intend to utilize the <statement-template>, ensure that you include the absolute path for the SQL file and verify the existence of the file.

Setting Up Enterprise PostgreSQL Extension with HiveMQ Platform Operator

Prerequisites:

\uD83D\uDCD8 Instructions

\uD83D\uDCCB Related articles