PostgreSQL

8 min

The PostgreSQL connector writes Litmus Edge (LE) DeviceHub messages into a table in a PostgreSQL database. The connector writes rows in batches, and uses one transaction for each batch.

The connector also supports Execute Command for ad-hoc SQL queries.

Supported communication

Outbound (write data from LE to the database): Yes

Inbound (read data from the database to LE): No

Execute Command (ad-hoc SQL query): Yes

Compatibility

The following product and version are validated. Other products and versions might work.

Product	Version
PostgreSQL	12

Connector configuration

Field	Type	Description	Required	Default
Name	String	Name of this connector instance.	Yes	-
Hostname	String	DNS name or IP address of the PostgreSQL host. The host must be reachable from the LE system. Length is 3 to 255 characters.	Yes	-
Port	Integer	Port of the PostgreSQL host. Range is 1 to 65535.	Yes	5432
SSL Mode	Dropdown	TLS connection mode: disable, require, verify-ca, or verify-full.	No	disable
CA Certificate	String (Secret)	CA certificate for TLS. You can also add it to the system-wide certificate list. Applies only when SSL Mode is not disable.	No	-
Certificate	String (Secret)	PEM client certificate for mutual TLS authentication. Applies only when SSL Mode is not disable.	No	-
Private key	String (Secret)	Private key that matches the client certificate. Applies only when SSL Mode is not disable.	No	-
Username	String	Database user that the connector authenticates as.	Yes	-
Password	String (Secret)	Password for the database user. Not needed for trust or certificate authentication.	No	-
Database	String	Name of the target database.	Yes	-
Schema	String	Schema that holds the target table. Leave it empty to use the database's default search path, which is usually public.	No	-
Table	String	Target table that the connector writes into. The table must exist unless you enable Create table.	Yes	-
Mapping	Object	Key-value pairs that map payload data to the columns of your table. The key is a column in your table. The value is a payload field name such as deviceId, the matching default-schema column name such as device_id, or static text with an @ prefix, such as @plant-a. Leave it empty to use the default table schema.	No	-
Enable JSONPath	Boolean	Read Mapping values that start with $ as JSONPath expressions, such as $.factory.line.machine.sensors.temperature. Values that don't start with $ keep their usual meaning, and an expression that matches nothing writes NULL.	No	Disabled
Create table	Boolean	Create the target table with the default table schema if it doesn't exist. The database user needs permission to create a table.	Yes	Disabled
Commit timeout	Integer	Maximum time in milliseconds that one batch insert transaction can take before the connector treats it as failed and retries it. Range is 10 to 65535.	No	30000
Batch timeout	Integer	Maximum time in milliseconds after the previous commit attempt before the connector writes a batch that isn't full. Range is 10 to 65535.	No	500
Max transaction size	Integer	Maximum number of messages in one batch insert. The connector writes a full batch without waiting for Batch timeout. Range is 1 to 65535.	No	500
Bulk insert count	Integer	Number of messages the connector buffers before it writes a batch. A value of 0 uses Max transaction size. Range is 0 to 65535.	No	0
Write mode	Dropdown	When a message counts as delivered: sync or async.	No	sync
Parallel transactions	Integer	Maximum number of batch inserts that run at the same time, each on its own database connection. Values above 1 increase throughput, but the connector can then write rows out of publish order. Range is 1 to 64.	No	1
Parallel Publish Count	Integer	Number of messages the connector publishes at the same time. In sync write mode, this value limits how many rows a batch can collect. Range is 1 to 4000.	No	1000

Additional attributes

Field	Type	Description	Required	Default
Throttling limit	Integer	Maximum outbound messages per second. A value of 0 disables throttling. Minimum is 0.	No	0
Persistent storage	Boolean	Store outbound queue messages on disk. If the connection drops or the system loses power, the messages are retained and sent once the connection is restored.	No	Enabled
Persistent storage size limit	Integer	Maximum disk space in GB used for persisted data. Applies only when Persistent storage is enabled. Minimum is 1.	Conditional	1
Queue Mode	Dropdown	Order in which the connector sends buffered messages. fifo sends the earliest message first. lifo sends the latest message first. Applies only when Persistent storage is enabled.	No	lifo
Enable Logging	Boolean	Write connection and publish error messages to the connector log. Logging can increase CPU usage, so enable it only for debugging.	No	Disabled

SSL modes

Value	Behavior
disable	No TLS.
require	The connector encrypts the connection but doesn't verify the server certificate.
verify-ca	The connector verifies the server certificate against the CA certificate.
verify-full	The connector verifies the CA certificate and the host name. Use this mode in production.

Default table schema

When you enable Create table and the target table doesn't exist, the connector creates it with the following schema.

CREATE TABLE IF NOT EXISTS TABLE_NAME
(
    id BIGSERIAL NOT NULL,
    record_uuid UUID NOT NULL,
    arrived_at  TIMESTAMP WITH TIME ZONE NOT NULL,
    device_id   VARCHAR(64)    NULL,
    register_id VARCHAR(64)    NULL,
    tag_name    VARCHAR(64)    NULL,
    datatype    VARCHAR(32)    NULL,
    value       VARCHAR NULL,
    success     BOOLEAN        NULL,
    PRIMARY KEY (id)
);

To write into a table with different columns, create the table yourself and use Mapping. For mapping and JSONPath examples, see the integration guide.

Write modes

Write mode	Behavior
sync	The connector acknowledges a message only after it commits the batch that holds the message. If the database is unreachable, the message stays in persistent storage, and the connector writes it after it reconnects.
async	The connector acknowledges a message once it buffers it. If a batch fails to write, the connector drops it and counts the messages as dropped.

Caution: In async write mode, or with Persistent storage disabled, you can lose data during a database outage. Use async only when throughput matters more than delivery.

IT Hub

Use the IT Hub ProcessorIT Hub Processor to run an ad-hoc SQL query through the connector. The connector streams the result back one message per row, on the response topic supplied in the request.

Add a ConnectorAdd a Connector

Manage ConnectorsManage Connectors

IT Hub ProcessorIT Hub Processor