DB - PostgreSQL SSL

5 min

Review the PostgreSQL SSL Integration Guide.

Overview

PostgreSQL SSL integration enables an Edge System to connect with a PostgreSQL database using SSL authentication.

Supported Communication

Outbound (Edge to Database) - Yes
Inbound (Database to Edge) - No

Configurations

Variable	Detail	Required
Name	Enter a name for the connector	Yes
Hostname	IP Address or Hostname of the PostgreSQL Server (Must be reachable from the Edge system)	Yes
Port	PostgreSQL Server port	Yes
SSL Mode	SSL Mode: One of "require", "verify-ca", "verify-full"	Yes
CA Certificate	SSL CA Certificate	No
Certificate	SSL Certificate	No
Private key	SSL Private key	No
Username	User name (can be empty)	No
Password	User password (can be empty)	No
Database	Database name	Yes
Table	Table name	Yes
Show Mapping	Data from a predefined table could be mapped into a custom one. Example: {"from": "to"} where "from" is a column from predefined table, and "to" is a column from destination table. See Work with Tables in SQL Connectors (Create Table and Show Mapping) to learn more.
Create table	If selected and table doesn't exist, the table is created with the necessary format. See Work with Tables in SQL Connectors (Create Table and Show Mapping) to learn more.	No
Commit timeout	Transaction commit timeout (ms)	No
Max transaction size	Maximum number of messages before a transaction is committed regardless of timeout	No
Bulk insert count	To enable this option, enter the number of messages to group together and send as one bulk insert statement. Enabling this option can improve how quickly data is processed when dealing with high volumes of tags.	No
Throttling limit	Set a limit on the number of outbound messages per second. If sending data to a server that has restrictions on the rate of incoming messages, this ensures that no messages are lost.	No
Queue mode	Select the sequence of data transfer: FIFO or LIFO. Selecting FIFO (First In First Out) will send the earliest message first. Selecting LIFO (Last In First Out) will send the latest message first.	No
Persistent storage	If enabled, messages in the outbound queue are stored on disk. If there are interruptions in connection or power loss, messages will be retained and eventually sent when the connection is restored.	No

Compatibility Validation

This information is just for reference. More products and versions may be supported.

Product	Version
PostgreSQL	12

Note: When the Create table check box is not selected, the table format (columns) that you will create must still adhere to the following format.

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)
);