PostgreSQL

9 min

You can generate data from an edge device, then connect and store the data in the PostgreSQL database using the DB-PostgreSQL connector. Data is collected from the selected device.

See the PostgreSQL documentation for more information.

Step 1: Add Device

Follow the steps to Connect a Device. The device will be used to store tags that will be eventually used to create outbound topics in the connector. Make sure to select the Enable Data Store check box.

For this use case, select the Simulator device and Simulator (Gen1) driver, and use the name Product A.

Step 2: Add Tags

After connecting the device in Belden Horizon Data Operations, you can Add Tags to the device. Create tags that you want to use to create outbound topics for the connector.

For this use case, add two tags with the following details.

Name: Select int-integer.
Value Type: Select int.
Tag Name: Enter temp.
Polling Interval: Enter 1.
Address: Enter 0.

Name: Select float-float.
Value Type: Select float.
Tag Name: Enter pressure.
Polling Interval: Enter 1.
Address: Enter 1.

Step 3: Run PostgreSQL Container

Note: The version used in this use case for PostgreSQL is 9.6.5.

To run the PostgreSQL Container:

In Belden Horizon Data Operations, navigate to Applications > Containers.
Click Run. The command line appears.
Enter the following. docker run -p 54325432 --name postgresql -e POSTGRES_PASSWORD=<> -e POSTGRES_USER-postgres -e POSTGRES_DB=db-example -v pgdata:/var/lib/postgresql/data -d 5432:5432 postgres,
Click Run. The PostgreSQL container appears and is running.
Copy the IP address for the PostgreSQL container.
Navigate to Applications > Volumes. The pgdata mount point is /var/lib/docker/volumes/pgdata/_data.

Step 4: Add the DB - PostgreSQL Connector

Follow the steps to Add a Connector and select the DB - PostgreSQL provider.

Configure the following parameters.

Name: A recognizable name to identify the connector.
Hostname: Paste the IP address you copied in Step 3.
Port: The PostgreSQL Server port. The default value is 5432.
Username: Enter postgres.
Password (Optional): Enter the user password.
Database: Enter db-example.
Table: Enter devicehub.
Show Mapping: If you want to send data to a custom table, select this check box and unselect Create table. See Work with Tables in SQL Connectors (Create Table and Show Mapping) to learn more. To add key/value pairs for the custom table, see Configure Key/Value Pairs.
Create table: If you want to send data to an existing table in the default format, or you want to create a new table in the default format, select this check box and unselect Show Mapping. See Work with Tables in SQL Connectors (Create Table and Show Mapping) to learn more.
Throttling limit: The maximum number of messages per second to be processed. The default value is zero, which means that there is no limit.
Persistent storage: When enabled, this will cause messages to undergo a store-and-forward procedure. Messages will be stored within Belden Horizon Data Operations when cloud providers are online.

Step 5: Enable the DB - PostgreSQL Connector

To enable the connector:

Return to the Belden Horizon Data Operations Integration browser tab.
Click the connection toggle on the connector tile. The connector shows connected after verifying the certificate, username, and password.
Toggle to enable connector

Step 6: Add Topics to PostgreSQL Connector

You will now need to import the tags created in Step 2 as topics for the PostgreSQL connector. The topics will be created as outbound topics.

To create outbound topics:

Click the connector tile. The connector Dashboard appears.
Click the Topics tab.
Click the Import from DeviceHub tags icon. The DeviceHub Import dialog box appears.
Select all the tags to import and click Import.

After importing the tags, click the toggle in the connector tile to restart the connection.

Toggle to enable connector

Step 7: Enable Topics

To enable the topics, return to the Topics tab and click the Enable all topics icon.

Enable all topics icon

Step 8: Make Queries in the PostgreSQL Container Terminal

To make queries in the MongoDB container terminal:

In Belden Horizon Data Operations, navigate to Applications > Containers.
Click the Terminal icon next to the PostgreSQL container. The PostgreSQL shell opens.
From the PostgreSQL container terminal, enter psql -h locahost -U postgres and press ENTER. The psql (13.2 (Debian 13.2-1.pgdg100+1)) version number appears.
Enter \l and press ENTER. The list of databases appears in the console.
Enter \l db-example and press ENTER. The list of databases shows only db-example.
Enter \c db-example and press ENTER. You are now connected to database "db-example" as user "postgres" appears.
Enter \dt and press ENTER. The list of relations appears showing the devicehub schema.
Enter \d devicehub and press ENTER. The public.devicehub table appears.
Enter \du and press ENTER. The List of roles appears.
Enter select * from devicehub; and press ENTER. The data for the devicehub table appears.
PostgreSQL connector messages