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

Understand Edge Xpert Application Service
Launch core services
```
edgexpert up
```
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
```
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.

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, Postgres MaxConn: 10, 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"
    MaxConn = "10"
    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"
```

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>

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
```

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
edgex=# SELECT timestamp, edgex_resources.device_name, edgex_resources.resource_name , edgex_valuetypes.value_type value_type, encode(value, 'escape') value
  FROM edgex_readings
  LEFT JOIN edgex_resources ON edgex_readings.resource_id = edgex_resources.id
  LEFT JOIN edgex_valuetypes ON edgex_readings.value_type_id = edgex_valuetypes.id;

timestamp                  |      device_name      | resource_name |  value_type  |                value                 
---------------------------+-----------------------+---------------+--------------+--------------------------------------
2023-03-28 20:15:37.949225 | Random-Binary-Device  | Binary        | Binary       | \231\x0B#\217\300\317\274\352\311
2023-03-28 20:16:18.990487 | Random-String-Device  | String        | String       | test2
2023-03-28 20:16:38.08947  | Random-Float-Device   | Float32Array  | Float32Array | [-1.233859e+03, -7.860917e+02,
2023-03-28 20:17:08.172643 | Random-Float-Device   | Float64Array  | Float64Array | [-8.956846e+02, 1.231154e+03,
2023-03-28 20:17:12.036833 | Random-Float-Device   | Float64       | Float64      | @w\317\230\307\342\202A
2023-03-28 20:17:15.354804 | Random-Float-Device   | Float32       | Float32      | \304\x06\210\222
2023-03-28 20:18:02.102674 | Random-Integer-Device | Int8Array     | Int8Array    | [-36, -67, 18, -87, 35]
2023-03-28 20:18:05.58343  | Random-Integer-Device | Int64Array    | Int64Array   | [-201, -1497, 202, 82, 942]
2023-03-28 20:18:08.679588 | Random-Integer-Device | Int64         | Int64        | \000\000\000\000\000\000\x05\301
2023-03-28 20:20:27.202224 | Random-Integer-Device | Int8          | Int8         | \355
2023-03-28 20:20:45.235397 | Random-Integer-Device | Int16         | Int16        | \375\322
2023-03-28 20:28:37.155604 | Simple-Device01       | Counter       | Object       | {"f1":"ABC","f2":123}
2023-03-28 20:29:09.929199 | Simple-Device01       | Xrotation     | Int32        | \000\000\000\000
2023-03-28 20:29:11.936153 | Simple-Device01       | Image         | Binary       | \377\330\377\333\000\204\000\x08\x06
2023-03-28 20:29:27.917178 | Simple-Device01       | SwitchButton  | Bool         | \000

Note

For the Bool, Int8, Int16, Int32, Int64, Uint8, Uint16, Uint32, Uint64, Float32, Float64 data type, the PostgresWrite pipeline function converts the reading value to binary data in big-endian byte order and stores it to the database. When we read the data from the database, we should decode the binary reading value according to big-endian byte order and the data type. For example, we read binary data [0, 123] from the database and convert the binary data to Uint value 123 according to big-endian byte order.
For the String, Object, ObjectArray, BoolArray, Int8Array, Int16Array, Int32Array, Int64Array, Uint8Array, Uint16Array, Uint32Array, Uint64Array, Float32Array, Float64Array data type, the PostgresWrite pipeline function converts the reading string representation to binary and store it to database. When we read the data from the database, we should decode the binary reading to string and parse the string to specified data type. For example, we read binary data from the database and convert the binary data to string {"f1":"ABC","f2":123}, then we can parse the string according to the requirement.
For the Binary data type, the PostgresWrite pipeline function stores the binary data directly to the database.

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`
MaxConn	The configuration setting to specify the maximum number of client connections allowed. The timescaledb will auto tune the number if you leave it as empty.
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`

Export to Postgres (Experimental)

Overview

Prerequisites

Configure an Application service to export device readings to Postgres

More configuration details about PostgresWrite