Skip to content

Export to Postgres (Experimental)

Overview

This page describes how to configure an application service to use the proprietary built-in function "PostgresWrite" to export EdgeX device readings to Postgres.

Note

There is an extension TimescaleDB of Postgres that allows you to more easily and quickly work with time-series data.
If the Postgres database you are running includes this extension, the Application service will create a Hypertable when connecting to the database to better store device readings.
The service list of Edge Xpert CLI has included the TimescaleDB since version 2.1.

Note

The "PostgresWrite" function is currently at the experimental stage. Any settings for this function, such as environment variables and configuration parameters, might be changed in future releases.

Prerequisites

  1. Understand Edge Xpert Application Service

  2. Launch core services

    edgexpert up
    

  3. Set the following environment variables for Postgres. (This step can be skipped if you already have a Postgres database running.)

    • POSTGRES_PASSWORD: This environment variable is required for you to use the PostgreSQL or TimescaleDB image. It must not be empty or undefined. This environment variable sets the superuser password for PostgreSQL or TimescaleDB. The default superuser is defined by the POSTGRES_USER environment variable.
    • POSTGRES_USER: This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.
    • POSTGRES_DB: This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

    The default values for these environment variables for the Postgres integrated in Edge Xpert are as follows:

    POSTGRES_PASSWORD=p@55w0rd
    POSTGRES_USER=iotechuser
    POSTGRES_DB=edgex
    

  4. Launch database. (This step can be skipped if you already have a Postgres or TimescaleDB running.)

    edgexpert up timescaledb
    
    Note that the port for the TimescaleDB integrated in Edge Xpert is 5432 and the hostname is timescaledb.

Configure an Application service to export device readings to Postgres

Edge Xpert offers a proprietary built-in function "PostgresWrite" to export EdgeX device readings to Postgres. A sample configuration is provided with Edge Xpert CLI utility. This is available at /usr/share/edgexpert/examples/app-configurable/postgres-write/configuration.toml.

  1. Copy and save the sample configuration file as a separate file, for example postgres-write.toml. Revise this configuration to match the information obtained from steps 3 and 4 of Prerequisites.
    For example, if you have the following: Postgres Host: timescaledb, Postgres Port: 5432, Database name: edgex, Table name: edgex_readings, StoreEventId: true, Auth mode: usernamepassword, Secret path: postgres

    PostgresWrite should use the following parameters:

    [Writable.Pipeline.Functions.PostgresWrite]
      [Writable.Pipeline.Functions.PostgresWrite.Parameters]
        Host = "timescaledb"
        Port = "5432"
        DatabaseName = "edgex"
        TableName = "edgex_readings"
        StoreEventId = "true"
        AuthMode = "usernamepassword"
        ChunkTimeInterval = "7 days"
        SecretPath = "postgres"
        PersistOnError = "false"
    
    More configuration details are described in next section.

    If you would like to launch the application service in secure mode, you should add your username and password into the secret store. Please refer to Edge Xpert Application Services for further details on adding secrets into the secret store. If you would like to launch the application service in insecure mode, you should modify the Writable.InsecureSecrets.postgres configuration as shown below:

    [Writable.InsecureSecrets.postgres]
      path = "postgres"
      [Writable.InsecureSecrets.postgres.Secrets]
        username = "iotechuser"
        password = "p@55w0rd"
    

  2. Once ready, launch an application service with your configuration:

    # Under secure mode
    $ edgexpert up --secret app-service --path=<path to your configuration file>
    
    # Under insecure mode
    $ edgexpert up app-service --path=<path to your configuration file>
    

  3. Now, an application service is up and ready to export readings to Postgres. To simulate the device sending readings from southbound, we could start up virtual device service:

    # Under secure mode
    $ edgexpert up --secret device-virtual
    
    # Under insecure mode
    $ edgexpert up device-virtual
    

  4. To query readings written to Postgres, please refer to Postgres documentation for more details. The following example uses the psql command line interface inside the TimescaleDB docker container to query all records that are inserted under the table edgex_readings:

 # runs a new command in the running container of TimescaleDB.
 docker exec -it timescaledb /bin/sh

 # login TimescaleDB
 psql -U iotechuser -h localhost -d edgex -p 5432

 # execute an SQL statement to query all readings
 select * from edgex_readings;

 |         timestamp          |      device_name      |               event_id               | resource_name | value_type | value_bool | value_string | value_uint8 | value_uint16 | value_uint32 | value_uint64 | value_int8 | value_int16 | value_int32 | value_int64 | value_float32 | value_float64 | value_binary | value_object | value_bool_arr | value_string_arr | value_uint8_arr | value_uint16_arr | value_uint32_arr | value_uint64_arr | value_int8_arr | value_int16_arr | value_int32_arr | value_int64_arr | value_float32_arr | value_float64_arr |
 |----------------------------|-----------------------|--------------------------------------|---------------|------------|------------|--------------|-------------|--------------|--------------|--------------|------------|-------------|-------------|-------------|---------------|---------------|--------------|--------------|----------------|------------------|-----------------|------------------|------------------|------------------|----------------|-----------------|-----------------|-----------------|-------------------|-------------------| 
 | 2022-01-19 06:05:35.663088 | Random-Integer-Device | 339956ea-9aaf-4078-bc8c-76be64408ba6 | Int16         | Int16      |            |              |             |              |              |              |            |       -1408 |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:05:35.666676 | Random-Integer-Device | 2d9cf179-25e3-41bb-ab0c-eaa623a89957 | Int64         | Int64      |            |              |             |              |              |              |            |             |             |         -26 |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   | 
 | 2022-01-19 06:05:35.665638 | Random-Integer-Device | 4a8a717f-87fe-44c1-b6b1-7908de289beb | Int32         | Int32      |            |              |             |              |              |              |            |             |       -1837 |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |                  
 | 2022-01-19 06:05:35.66762  | Random-Integer-Device | abf4b236-cb3f-4092-bd6f-0fa5040c4924 | Int8          | Int8       |            |              |             |              |              |              |        105 |             |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:05:50.665596 | Random-Integer-Device | dfb75b02-afd0-45be-8739-b078e8fc6e68 | Int16         | Int16      |            |              |             |              |              |              |            |       -2234 |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:05:50.669146 | Random-Integer-Device | d82b59da-2e7e-4b72-b47e-28869a71b1c0 | Int32         | Int32      |            |              |             |              |              |              |            |             |       -1962 |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:05:50.671024 | Random-Integer-Device | 97e6a4ee-b0ac-4d57-856b-5f54ad233173 | Int64         | Int64      |            |              |             |              |              |              |            |             |             |         503 |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:05:50.672087 | Random-Integer-Device | 31da1251-f9c2-40a7-be33-b83c1f3db0c5 | Int8          | Int8       |            |              |             |              |              |              |         78 |             |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:05.634867 | Random-Integer-Device | e4fad47b-1b4f-4e20-bc01-66dad9180178 | Int16         | Int16      |            |              |             |              |              |              |            |        1926 |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:05.638029 | Random-Integer-Device | 7f405a92-0144-4681-b1f8-7aeea4f2439e | Int64         | Int64      |            |              |             |              |              |              |            |             |             |       -2195 |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:05.636053 | Random-Integer-Device | 433c2525-0104-4cdf-95d7-3f5413cb8190 | Int32         | Int32      |            |              |             |              |              |              |            |             |        1640 |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:05.639581 | Random-Integer-Device | 98e5f7c8-14e3-4e89-9044-b2f1264eaac5 | Int8          | Int8       |            |              |             |              |              |              |        -25 |             |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:20.642985 | Random-Integer-Device | 3355f6d5-1aab-4a94-95ea-d128205a5eda | Int16         | Int16      |            |              |             |              |              |              |            |       -1826 |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:20.644266 | Random-Integer-Device | 8c71d5df-7e03-427d-9f0b-8914f9634f7e | Int32         | Int32      |            |              |             |              |              |              |            |             |       -1292 |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:20.649711 | Random-Integer-Device | 36fcc73f-fe0d-4557-a015-57ec45c92f4d | Int64         | Int64      |            |              |             |              |              |              |            |             |             |       -1436 |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |
 | 2022-01-19 06:06:20.650486 | Random-Integer-Device | 928fc215-3d77-465a-9537-225995ba5f38 | Int8          | Int8       |            |              |             |              |              |              |          1 |             |             |             |               |               |              |              |                |                  |                 |                  |                  |                  |                |                 |                 |                 |                   |                   |

More configuration details about PostgresWrite

PostgresWrite
Uses the pgx library to write EdgeX events to Postgres
Sample configuration:
[Writable.Pipeline.Functions.PostgresWrite]
[Writable.Pipeline.Functions.PostgresWrite.Parameters]
Host = "timescaledb"
Port = "5432"
DatabaseName = "edgex"
TableName = "edgex_readings"
StoreEventId = "true"
AuthMode = "usernamepassword"
ChunkTimeInterval = "7 days"
SecretPath = "postgres"
PersistOnError = "false"
Configuration Description
Host The configuration setting to specify the Postgres hostname. Default Value: timescaledb
Port The configuration setting to specify the Postgres TCP port. Default Value: 5432
DatabaseName The configuration setting to specify the Postgres database name. Default Value: edgex
TableName The configuration setting to specify the Postgres table name. Default Value: edgex_readings
StoreEventId Specify whether the event ID associated with the reading should be stored. Default Value: false
AuthMode Mandatory configuration to specify the authentication mode used to connect to Postgres. As of release of Edge Xpert 2.1, authmode only accepts usernamepassword for Username / Password authentication.
ChunkTimeInterval The configuration setting to specify the chunk time interval for Hypertable. The value should be specified as interval type or an integral value in microseconds. This configuration only takes effect when the target database is TimescaleDB. Default Value: 7 days
SecretPath Mandatory configuration to specify the path in secret provider to retrieve secrets.
PersistOnError Specifies whether the data should be stored for later retry if encountering any error during message publish. If PersisOnError is true and Store and Forward is enabled, the data will be stored for later retry. See Store and Forward for more details. Default Value: false
Back to top