This chapter uses the actual example in CentOS 7.2 to show how to store relevant information through PostgreSQL.

As an important member of the open-source relational database, PostgreSQL boasts that it is the most advanced open-source database in the world. Compared with other open-source relational databases such as MySQL, PostgreSQL is a community driven open-source project, which is maintained by more than 1000 contributors around the world. PostgreSQL provides a single full-featured version, unlike MySQL, which provides multiple different community, commercial and enterprise versions. PostgreSQL is based on a free BSD/MIT license. Organizations can use, copy, modify, and redistribute code by providing a copyright notice.

PostgreSQL has many features, and it has more support in the field of GIS. Its "no lock" feature is very prominent. It supports function and conditional index, and has a mature clustering scheme. PostgreSQL also has powerful SQL programming capabilities such as statistical functions and statistical syntax support. Through Timescaledb plug-in, PostgreSQL can be transformed into Timescaledb, a time series database with complete functions.

Function overview

• Client online status storage
• Client agent subscription
• Persistent publish message
• Retain message persistence
• Message acknowledgment persistence
• Custom SQL

Install and verify PostgreSQL server

Readers can refer to PostgreSQL Official documents or Docker To download and install PostgreSQL server, this article uses PostgreSQL version 10.1.

For the convenience of management and operation, free graphic management software can be downloaded and used Postico (MacOS only) or pgAdmin.

Configure EMQ X server

For the EMQ X installed in RPM mode, the configuration files related to PostgreSQL are located in / etc / emqx / plugins / emqx ﹐ backend ﹐ pgsql.conf. If only testing the PostgreSQL persistence function, most configurations do not need to be changed. Just fill in the user name, password and database:

backend.pgsql.pool1.server = 127.0.0.1:5432

backend.pgsql.pool1.pool_size = 8

backend.pgsql.pool1.username = root

backend.pgsql.pool1.password = public

backend.pgsql.pool1.database = mqtt

backend.pgsql.pool1.ssl = false

Leave the rest of the configuration file unchanged, and then you need to start the plug-in. There are two ways to start plug-ins: command line and console. Readers can choose one of them.

Start from the command line

emqx_ctl plugins load emqx_backend_pgsql

Launch from admin console

On the plug-in page of the EMQ X management console, locate the emqx ﹣ backend ﹣ PgSQL plug-in and click start.

Client online status storage

When the client is online or offline, the plug-in will update the online status, online or offline time, and node client list to the PostgreSQL database.

Data sheet

Create the mqtt? Client device online status table:

CREATE TABLE mqtt_client(
  id SERIAL primary key,
  clientid character varying(100),
  state integer, -- Online state: 0 Offline 1 Online
  node character varying(100), -- Access node name
  online_at timestamp, -- On-line time
  offline_at timestamp, -- Offline time
  created timestamp without time zone,
  UNIQUE (clientid)
);

Configuration item

Open the configuration file and configure the Backend rule:

## hook: client.connected,client.disconnected
## action/function: on_client_connected,on_client_disconnected


## Client online and offline
backend.pgsql.hook.client.connected.1 = {"action": {"function": "on_client_connected"}, "pool": "pool1"}

## Client offline
backend.pgsql.hook.client.disconnected.1 = {"action": {"function": "on_client_disconnected"}, "pool": "pool1"}

Use example

The browser opens the http://127.0.0.1:18083 EMQ X management console, creates a new client connection in the tool - > websocket, specifies the client ID as the sub ﹣ client, and then clicks the connection. After the connection is successful, manually disconnect:

View the mqtt ﹣ client table, and a client up / down line record will be written / updated:

Client agent subscription

When the client goes online, the enclosure directly reads the preset to be subscribed list from the database, and the agent loads the subscription topic. In the scenario that the client needs to communicate (receive messages) through a predetermined topic, the application can set / change the proxy subscription list from the data level.

Data sheet

To create a mqtt sub agent subscription relationship table:

CREATE TABLE mqtt_sub(
  id SERIAL primary key,
  clientid character varying(100),
  topic character varying(200), -- topic
  qos integer, -- QoS
  created timestamp without time zone,
  UNIQUE (clientid, topic)
);

Configuration item

Open the configuration file and configure the Backend rule:

## hook: client.connected
## action/function: on_subscribe_lookup
backend.pgsql.hook.client.connected.2    = {"action": {"function": "on_subscribe_lookup"}, "pool": "pool1"}

Use example

When a sub client device goes online, it needs to subscribe to two QoS 1 topics: sub client / upstream and sub client / downlink:

1. Initialize the insertion agent subscription topic information in the mqtt sub table:

insert into mqtt_sub(clientid, topic, qos) values('sub_client', 'sub_client/upstream', 1);

insert into mqtt_sub(clientid, topic, qos) values('sub_client', 'sub_client/downlink', 1);

1. On the WebSocket page of the EMQ X management console, create a new client connection with the client sub client and switch to the subscription page. It can be seen that the current client automatically subscribes to two QoS 1 topics: sub client / upstream and sub client / downlink:

1. Switch back to the WebSocket page of the management console, publish messages to the sub ﹣ client / downlink topic, and receive the published messages in the message subscription list.

Persistent publish message

Data sheet

Create mqtt ﹣ MSG mqtt message persistence table:

CREATE TABLE mqtt_msg (
  id SERIAL primary key,
  msgid character varying(60),
  sender character varying(100), -- news pub Of clientid
  topic character varying(200),
  qos integer,
  retain integer, -- Whether retain news
  payload text,
  arrived timestamp without time zone -- Message arrival time(QoS > 0)
);

Configuration item

Open the configuration file, configure the Backend rule, and support message filtering with the topic parameter. Here, use the wildcard to store any topic message:

## hook: message.publish
## action/function: on_message_publish

backend.pgsql.hook.message.publish.1     = {"topic": "#", "action": {"function": "on_message_publish"}, "pool": "pool1"}

Use example

On the WebSocket page of the EMQ X management console, use clientdi sub client to establish a connection and publish multiple messages to the topic upstream topic. EMQ X persists the message list to the mqtt MSG table:

Retain message persistence

Table structure

Create mqtt ﹣ retain retain message storage table:

CREATE TABLE mqtt_retain(
  id SERIAL primary key,
  topic character varying(200),
  msgid character varying(60),
  sender character varying(100),
  qos integer,
  payload text,
  arrived timestamp without time zone,
  UNIQUE (topic)
);

Configuration item

Open the configuration file and configure the Backend rule:

## At the same time, open the following rules to enable retain persistence for three lifecycles

## When publishing a non empty retain message (store)
backend.pgsql.hook.message.publish.2     = {"topic": "#", "action": {"function": "on_message_retain"}, "pool": "pool1"}

## Querying the retain message when the device subscribes to a topic
backend.pgsql.hook.session.subscribed.2  = {"topic": "#", "action": {"function": "on_retain_lookup"}, "pool": "pool1"}

## When publishing an empty retain message (clear)
backend.pgsql.hook.message.publish.3     = {"topic": "#", "action": {"function": "on_retain_delete"}, "pool": "pool1"}

Use example

After the connection is established in WebSocket page of EMQ X management console, issue the message and check keep:

Publish (message is not empty)

When a non empty retain message is published, EMQ X will use topic as the unique key to persist the message to the mqtt'retain table. Different retain messages will be published under the same topic, and only the last message will be persisted:

Subscribe

After the client subscribes to the retain topic, EMQ X will query the mqtt ﹣ retain data table and perform the operation of delivering the retain message.

Publish (message is empty)

In MQTT protocol, publishing an empty retain message will empty the retain record, which will be deleted from the mqtt_retain table.

Message acknowledgment persistence

After the message acknowledgment (ACK) persistence is turned on, when the client subscribes to QoS 1 and QoS 2 level topics, EMQ X will initialize the ACK record in the database with the unique key of clientid + topic.

Data sheet

Create the mqtt acked message confirmation table:

CREATE TABLE mqtt_acked (
  id SERIAL primary key,
  clientid character varying(100),
  topic character varying(100),
  mid integer,
  created timestamp without time zone,
  UNIQUE (clientid, topic)
);

Configuration item

Open the configuration file, configure the Backend rule, and use the topic wildcard to filter the messages to be applied:

## Initialize ACK record on subscription
backend.pgsql.hook.session.subscribed.1  = {"topic": "#", "action": {"function": "on_message_fetch"}, "pool": "pool1"}


## Update arrival status when message arrives
backend.pgsql.hook.message.acked.1       = {"topic": "#", "action": {"function": "on_message_acked"}, "pool": "pool1"}

## Delete record lines on unsubscribe
backend.pgsql.hook.session.unsubscribed.1= {"topic": "#", "action": {"sql": ["delete from mqtt_acked where clientid = ${clientid} and topic = ${topic}"]}, "pool": "pool1"}

Use example

After the connection is established in WebSocket page of EMQ X management console, subscribe to the topic with QoS > 0:

At this time, the mqtt_acked table will insert the initialization data row. Each time a message with QoS > 0 is published to the subject, the data row mid will increase by 1 automatically after the message arrives:

Custom SQL

In addition to plug-in built-in functions and table structures, emqx ﹣ backend ﹣ PgSQL also supports user-defined SQL statements. Through using template syntax such as ${clientid} to dynamically construct SQL statements, such as client connection history, update user-defined data tables and other operations can be realized.

SQL statement parameter description

hook	Available parameters	Example (in sql statement, ${name} represents the parameters that can be obtained)
client.connected	clientid	insert into conn(clientid) values(${clientid})
client.disconnected	clientid	insert into disconn(clientid) values(${clientid})
session.subscribed	clientid, topic, qos	insert into sub(topic, qos) values(${topic}, ${qos})
session.unsubscribed	clientid, topic	delete from sub where topic = ${topic}
message.publish	msgid, topic, payload, qos, clientid	insert into msg(msgid, topic) values(${msgid}, ${topic})
message.acked	msgid, topic, clientid	insert into ack(msgid, topic) values(${msgid}, ${topic})
message.delivered	msgid, topic, clientid	insert into delivered(msgid, topic) values(${msgid}, ${topic})

Update custom data representation example

Using the existing equipment table clients, which has basic fields such as equipment connection authentication, equipment status record, and equipment management for other management services, now it is necessary to synchronize the EMQ X equipment status to this table:

CREATE TABLE "public"."clients" (
    "id" serial,
    "deviceUsername" varchar(50), --  MQTT username
    "client_id" varchar(50), -- MQTT client_id
    "password" varchar(50), -- MQTT password
    "is_super" boolean DEFAULT 'false', -- Whether ACL super Client
    "owner" int, -- Create user
    "productID" int, -- Products belonging to them
    "state" boolean DEFAULT 'false', -- Online state
    PRIMARY KEY ("id")
);

-- The sample data already exists in the initialization system state by false
INSERT INTO "public"."clients"("deviceUsername", "client_id", "password", "is_super", "owner", "productID", "state") VALUES('mqtt_10c61f1a1f47', 'mqtt_10c61f1a1f47', '9336EBF25087D91C818EE6E9EC29F8C1', TRUE, 1, 21, FALSE);

Custom UPDATE SQL statement:

## Configure custom UPDATE SQL in connected / disconnected hook
## You can configure multiple SQL statements "SQL": ["SQL a", "SQL B", "SQL C"]

## Connection time
backend.pgsql.hook.client.connected.3 = {"action": {"sql": ["update clients set state = true where client_id = ${clientid}"]}, "pool": "pool1"}

## When disconnected
backend.pgsql.hook.client.disconnected.3 = {"action": {"sql": ["update clients set state = false where client_id = ${clientid}"]}, "pool": "pool1"}

When the client goes online, it will fill in and execute the scheduled SQL state ment, and update the status field of the device online to true:

Advanced options

backend.pgsql.time_range = 5s

backend.pgsql.max_returned_count = 500

summary

After understanding the data structure stored in PostgreSQL and user-defined SQL, readers can expand relevant applications in combination with PostgreSQL.

For more information, please visit our website emqx.io , or focus on our open source projects github.com/emqx/emqx , please visit Official documents.