PostgreSQL Route

PostgreSQL Overview

The POSTGRESQL route stores MQTT messages in PostgreSQL databases. It supports SSL connections, parameterized queries, and automatic reconnection for reliable data persistence.

PostgreSQL is ideal for structured data that requires complex queries, joins, and ACID compliance. Use it when you need relational integrity and powerful SQL capabilities.

Basic Syntax

DEFINE ROUTE SensorDB WITH TYPE POSTGRESQL
    ADD SQL_CONFIG
        WITH SERVER "postgres.example.com"
        WITH PORT '5432'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreSensorReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO readings (ts, sensor_id, value) VALUES (NOW(), '{sensor_id}', '{value.json}')"

Connection Configuration

SQL_CONFIG Parameters

Server Settings

SERVER

string

required

PostgreSQL server hostname or IP address.

PORT

integer

PostgreSQL port. Default: 5432.

DATABASE

string

required

Target database name.

Authentication

USERNAME

string

required

Database username.

PASSWORD

string

required

Database password.

SSL/Security

USE_SSL

boolean

Enable SSL connection. Default: false.

TRUST_SERVER_CERTIFICATE

boolean

Trust server certificate without validation. Default: false.

Timeouts

CONNECTION_TIMEOUT

integer

Connection timeout in seconds. Default: 30.

COMMAND_TIMEOUT

integer

Command timeout in seconds. Default: 30.

Event Configuration

EVENT Parameters

Trigger & Routing

SOURCE_TOPIC

string

required

MQTT topic pattern that triggers the INSERT. Supports wildcards.

DESTINATION_TOPIC

string

MQTT topic to publish query result status.

Query

QUERY

string

required

SQL INSERT statement with placeholders for dynamic values.

Query Placeholders

Placeholder	Description
`{value.json}`	Full JSON payload
`{value.json.field}`	Specific field from JSON
`{timestamp}`	Message timestamp
`{source_topic}`	Original MQTT topic
`{field}`	Field extracted from topic path

Complete Examples

Basic Sensor Storage
With SSL
Multiple Events
JSON Column

Store temperature readings with timestamps:

DEFINE ROUTE TemperatureDB WITH TYPE POSTGRESQL
    ADD SQL_CONFIG
        WITH SERVER "postgres.example.com"
        WITH PORT '5432'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreTemperature
        WITH SOURCE_TOPIC "sensors/+/temperature"
        WITH DESTINATION_TOPIC "db/status/temperature"
        WITH QUERY "INSERT INTO temperature_readings (recorded_at, sensor_id, value, unit) VALUES (NOW(), '{sensor_id}', {value.json.value}, '{value.json.unit}')"

Input at sensors/temp001/temperature:

{"value": 23.5, "unit": "celsius"}

Secure connection with SSL:

DEFINE ROUTE SecureDB WITH TYPE POSTGRESQL
    ADD SQL_CONFIG
        WITH SERVER "postgres.cloud.com"
        WITH PORT '5432'
        WITH DATABASE "production"
        WITH USERNAME "app_user"
        WITH PASSWORD "secure_password"
        WITH USE_SSL "true"
        WITH TRUST_SERVER_CERTIFICATE "false"
    ADD EVENT StoreEvent
        WITH SOURCE_TOPIC "events/#"
        WITH QUERY "INSERT INTO events (ts, topic, payload) VALUES (NOW(), '{source_topic}', '{value.json}')"

Different tables for different data types:

DEFINE ROUTE MultiTableDB WITH TYPE POSTGRESQL
    ADD SQL_CONFIG
        WITH SERVER "postgres.example.com"
        WITH PORT '5432'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    
    ADD EVENT StoreSensorData
        WITH SOURCE_TOPIC "sensors/+/data"
        WITH QUERY "INSERT INTO sensor_data (ts, sensor_id, value) VALUES (NOW(), '{sensor_id}', '{value.json}')"
    
    ADD EVENT StoreAlerts
        WITH SOURCE_TOPIC "alerts/+"
        WITH QUERY "INSERT INTO alerts (ts, alert_type, message) VALUES (NOW(), '{alert_type}', '{value.json}')"
    
    ADD EVENT StoreMetrics
        WITH SOURCE_TOPIC "metrics/#"
        WITH QUERY "INSERT INTO metrics (ts, topic, data) VALUES (NOW(), '{source_topic}', '{value.json}')"

Store full payload in JSONB column:

DEFINE ROUTE JSONStorage WITH TYPE POSTGRESQL
    ADD SQL_CONFIG
        WITH SERVER "postgres.example.com"
        WITH PORT '5432'
        WITH DATABASE "iot_data"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreJSON
        WITH SOURCE_TOPIC "devices/#"
        WITH QUERY "INSERT INTO device_data (ts, topic, payload) VALUES (NOW(), '{source_topic}', '{value.json}'::jsonb)"

This allows powerful JSON queries in PostgreSQL:

SELECT * FROM device_data WHERE payload->>'temperature' > '25';

Table Schema Examples

Create tables to match your data:

-- Basic sensor readings
CREATE TABLE sensor_readings (
    id SERIAL PRIMARY KEY,
    recorded_at TIMESTAMP DEFAULT NOW(),
    sensor_id VARCHAR(50),
    value DOUBLE PRECISION,
    unit VARCHAR(20)
);

-- Full JSON storage
CREATE TABLE device_data (
    id SERIAL PRIMARY KEY,
    ts TIMESTAMP DEFAULT NOW(),
    topic VARCHAR(255),
    payload JSONB
);

-- Alerts table
CREATE TABLE alerts (
    id SERIAL PRIMARY KEY,
    ts TIMESTAMP DEFAULT NOW(),
    alert_type VARCHAR(50),
    message TEXT
);

Troubleshooting

Connection Refused

Verify SERVER and PORT are correct
Check PostgreSQL is running and accepting connections
Verify firewall allows connections on port 5432
Check pg_hba.conf allows your IP address

Authentication Failed

Verify USERNAME and PASSWORD are correct
Check user has permissions on the DATABASE
Verify authentication method in pg_hba.conf

SSL Connection Errors

Ensure USE_SSL matches server requirements
For self-signed certs, set TRUST_SERVER_CERTIFICATE “true”
Verify SSL is enabled on PostgreSQL server

Query Syntax Errors

Check placeholder names match topic structure
Verify table and column names exist
Test query manually in psql first
Ensure proper escaping for special characters

Timeout Errors

Increase CONNECTION_TIMEOUT for slow networks
Increase COMMAND_TIMEOUT for complex queries
Check server load and query performance

Next Steps

MySQL Route

Configure MySQL database storage.

Storage Overview

Compare all storage options.

Quick Start

MQTT Broker

LoT Language

LoT Notebooks

Actions

Routes

Models

Rules

PostgreSQL Overview

Basic Syntax

Connection Configuration

SQL_CONFIG Parameters

Event Configuration

EVENT Parameters

Query Placeholders

Complete Examples

Table Schema Examples

Troubleshooting

Next Steps

MySQL Route

Storage Overview

Quick Start

MQTT Broker

LoT Language

LoT Notebooks

Actions

Routes

Models

Rules

​PostgreSQL Overview

​Basic Syntax

​Connection Configuration

​SQL_CONFIG Parameters

​Event Configuration

​EVENT Parameters

​Query Placeholders

​Complete Examples

​Table Schema Examples

​Troubleshooting

​Next Steps

MySQL Route

Storage Overview

PostgreSQL Overview

Basic Syntax

Connection Configuration

SQL_CONFIG Parameters

Event Configuration

EVENT Parameters

Query Placeholders

Complete Examples

Table Schema Examples

Troubleshooting

Next Steps