---
title: PostgreSQL
slug: bhdo/postgresql
createdAt: 2022-11-24T18:29:38.000Z
---

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](https://hub.docker.com/_/postgres) for more information.&#x20;

# Step 1: Add Device

Follow the steps to [Connect a Device](https://public-docs.litmus.io/bhdo/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.&#x20;

For this use case, select the **Simulator&#x20;**&#x64;evice 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](https://public-docs.litmus.io/bhdo/add-tags) to the device. Create tags that you want to use to create outbound topics for the connector.&#x20;

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

- **Name**: Select **int-integer**.&#x20;
- **Value Type**: Select **int**.&#x20;
- **Tag Name**: Enter **temp**.&#x20;
- **Polling Interval**: Enter **1**.&#x20;
- **Address**: Enter **0**.&#x20;



- **Name**: Select **float-float**.&#x20;
- **Value Type**: Select **float**.&#x20;
- **Tag Name**: Enter **pressure**.&#x20;
- **Polling Interval**: Enter **1**.&#x20;
- **Address**: Enter **1**.&#x20;

# Step 3: Run PostgreSQL Container

:::hint{type="info"}
**Note**: The version used in this use case for PostgreSQL is 9.6.5.
:::

**To run the PostgreSQL Container:**

1. In Belden Horizon Data Operations, navigate to **Applications&#x20;**> **Containers**.
2. Click **Run**.
   The command line appears.
3. Enter the following.&#x20;
   `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,`
4. Click **Run**.&#x20;
   The PostgreSQL container appears and is running.
5. Copy the IP address for the PostgreSQL container.
6. Navigate to **Applications&#x20;**> **Volumes**.&#x20;
   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](https://public-docs.litmus.io/bhdo/add-a-connector) and select the **DB - PostgreSQL&#x20;**&#x70;rovider.&#x20;

Configure the following parameters.&#x20;

- **Name**: A recognizable name to identify the connector.&#x20;
- **Hostname**: Paste the IP address you copied in Step 3. &#x20;
- **Port**: The PostgreSQL Server port. The default value is **5432**.&#x20;
- **Username**: Enter **postgres**.&#x20;
- **Password&#x20;**(Optional): Enter the user password.&#x20;
- **Database**: Enter **db-example**.&#x20;
- **Table**: Enter **devicehub**.&#x20;
- **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)](https://public-docs.litmus.io/bhdo/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](https://public-docs.litmus.io/bhdo/work-with-tables-in-sql-connectors-create-table-and-show-mapping#configure-keyvalue-pairs).&#x20;
- **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)](https://public-docs.litmus.io/bhdo/work-with-tables-in-sql-connectors-create-table-and-show-mapping) to learn more.&#x20;
- **Throttling limit**: The maximum number of messages per second to be processed. The default value is zero, which means that there is no limit.&#x20;
- **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. &#x20;

# Step 5: Enable the DB - PostgreSQL Connector

**To enable the connector:&#x20;**

1. Return to the *Belden Horizon Data Operations Integration* browser tab.
2. Click the connection toggle on the connector tile.&#x20;
   The connector shows connected after verifying the certificate, username, and password.

   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/_CnGcoblarUST8xcuwUQ6_postgresql.png" size="60" width="351" height="263" caption="Toggle to enable connector" position="center" showCaption="true"}

# 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.&#x20;

**To create outbound topics:**

1. Click the connector tile.
   The connector *Dashboard&#x20;*&#x61;ppears.&#x20;
2. Click the **Topics&#x20;**&#x74;ab.&#x20;
3. Click the **Import from DeviceHub** tags icon.&#x20;
   The *DeviceHub Import&#x20;*&#x64;ialog box appears.

   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/4fC8yaOKctxiiTvUDGfWu_import-from-devicehub.png" size="80" width="1148" height="570" position="center" showCaption="false"}
4. Select all the tags to import and click **Import**.&#x20;

After importing the tags, click the toggle in the connector tile to restart the connection.&#x20;

::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/5JvJnTLUV21CLhGOI643R_postgresql.png" size="60" width="351" height="263" caption="Toggle to enable connector" position="center" showCaption="true"}

# Step 7: Enable Topics

To enable the topics, return to the Topics tab and click the **Enable all topics** icon.&#x20;

::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/ysQ6tuAI-jB7gve8RfnoC_enable-all-topics.png" size="80" width="1148" height="570" caption="Enable all topics icon" position="center" showCaption="true"}

# Step 8: Make Queries in the PostgreSQL Container Terminal

**To make queries in the MongoDB container terminal:**

1. In Belden Horizon Data Operations, navigate to **Applications&#x20;**>**&#x20;Containers**.
2. Click the **Terminal&#xA0;**&#x69;con next to the PostgreSQL container.
   The PostgreSQL shell opens.
3. From the PostgreSQL container terminal, enter **psql -h locahost -U postgres&#xA0;**&#x61;nd press **ENTER**.
   The *psql (13.2 (Debian 13.2-1.pgdg100+1))* version number appears.
4. Enter **\l&#xA0;**&#x61;nd press **ENTER**.
   The list of databases appears in the console.
5. Enter **\l db-example&#xA0;**&#x61;nd press **ENTER**.
   The list of databases shows only db-example.
6. Enter **\c db-example&#xA0;**&#x61;nd press **ENTER**.
   `You are now connected to database "db-example" as user "postgres"` appears.
7. Enter **\dt** and press **ENTER**.
   The list of relations appears showing the devicehub schema.
8. Enter **\d** devicehub and press **ENTER**.
   The *public.devicehub* table appears.
9. Enter **\du** and press **ENTER**.****
   The *List of roles* appears.
10. Enter **select \* from devicehub;&#xA0;**&#x61;nd press **ENTER**.
    The data for the devicehub table appears.

    ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/HOsMXyQViXIcPsygcVFOq_applications-postgresql-table.png" size="80" width="1576" height="739" caption="PostgreSQL connector messages" position="center" showCaption="true"}

