volttron-postgresql-historian 2.0.0rc2

VOLTTRON historian agent that stores data in a PostgreSQL database. It extends the SQLHistorian class.

VOLTTRON historian agent that stores data into a PostgreSQL database

Installation notes

1. The PostgreSQL database driver supports recent PostgreSQL versions. It was tested on 10.x, but should work with 9.x and 11.x.
2. The user must have SELECT, INSERT, and UPDATE privileges on historian tables.
3. The tables in the database are created as part of the execution of the SQLHistorianAgent, but this will fail if the database user does not have CREATE privileges.
4. Care must be exercised when using multiple historians with the same database. This configuration may be used only if there is no overlap in the topics handled by each instance. Otherwise, duplicate topic IDs may be created, producing strange results.

Configuration

PostgreSQL historian supports two configuration parameters

• connection - This is a mandatory parameter with type indicating the type of sql historian (i.e. postgresql) and params containing the database access details
• tables_def - Optional parameter to provide custom table names for topics, data, and metadata.
• Optional, You can also override any parameter defined by base historian. See configuration of base historian class

The configuration can be in a json or yaml formatted file. The following examples show minimal connection configurations for a psycopg2-based historian. Other options are available and are documented here Not all parameters have been tested, use at your own risk.

Local PostgreSQL Database

The following snippet demonstrates how to configure the historian to use a PostgreSQL database on the local system that is configured to use Unix domain sockets. The user executing volttron must have appropriate privileges.

Yaml Format:

    connection:
          # type should be postgresql
          type: postgresql
          params:
            # Relative to the agents data directory
            dbname: "volttron"
        
    tables_def:
        # prefix for data, topics, and (in version < 4.0.0 metadata tables)
        # default is ""
        table_prefix: ""
        # table name for time series data. default "data"
        data_table: data
        # table name for list of topics. default "topics"
        topics_table: topics

JSON format:

    {
        "connection": {
            "type": "postgresql", 
            "params": { "dbname": "volttron" }
        }
    }

Remote PostgreSQL Database

The following snippet demonstrates how to configure the historian to use a remote PostgreSQL database.

    {
        "connection": {
            "type": "postgresql", 
            "params": { 
                "dbname": "volttron", 
                "host": "historian.example.com", 
                "port": 5432, 
                "user": "volttron", 
                "password": "secret" }
        }
    }

TimescaleDB Support

Both of the above PostgreSQL connection types can make use of TimescaleDB's high performance Hypertable backend for the primary timeseries table. The agent assumes you have completed the TimescaleDB installation and setup the database by following the instructions here: https://docs.timescale.com/latest/getting-started/setup To use, simply add 'timescale_dialect: true' to the connection params in the agent config as below

    {
        "connection": {
            "type": "postgresql", 
            "params": { 
                "dbname": "volttron", 
                "host": "historian.example.com", 
                "port": 5432, 
                "user": "volttron", 
                "password": "secret" ,
                "timescale_dialect": true }
        }

    }

Pre-requisite

• Before installing this agent, VOLTTRON (>=11.0.0rc0) should be installed and running. Its virtual environment should be active. Information on how to install of the VOLTTRON platform can be found here
• psycopg2 library - this should be installed using the vctl install-lib command.

Installation

1. In the virtual environment running volttron, run the following command to install psycopg2

    vctl install-lib psycopg2-binary
   

   The above command adds the psycopg2 to the poetry dependency list in volttron home, so that poetry can resolve any dependency version mismatch.

2. Setup database

   If this is not a development environment we highly recommend that you create the database and database tables using a user with appropriate permissions. This way the database user used by the historian need not have CREATE privileges Postgres historian expects two tables a. A topics tables that stores the list of unique topics and its metadata. The default name is "topics". If you use a different name please specify it as part of "tables_def" configuration parameter in agent config. See example configuration b. A data table that stores the timeseries data and refers to the topic table using a topic id. The default name is "data". If you use a different name please specify it as part of "tables_def" configuration parameter in agent config. See example configuration

   Below are the sql statements to create database and tables Create Database

      CREATE DATABASE volttron
   

   TOPICS tables:

       CREATE TABLE IF NOT EXISTS topics (
           topic_id SERIAL PRIMARY KEY NOT NULL, 
           topic_name VARCHAR(512) NOT NULL, 
           metadata TEXT, 
           UNIQUE (topic_name)
      )
   

   DATA table:

      CREATE TABLE IF NOT EXISTS data (
          ts TIMESTAMP NOT NULL, 
          topic_id INTEGER NOT NULL, 
          value_string TEXT NOT NULL, 
          UNIQUE (topic_id, ts)
      )
   

   Optional timescale hypertable

      SELECT create_hypertable(data, 'ts', if_not_exists => true)
   

   Create index to speed up data access If using hypertables:

       CREATE INDEX IF NOT EXISTS idx_data ON data (topic_id, ts)
   

   If not using hypertables:

       CREATE INDEX IF NOT EXISTS idx_data ON data (ts ASC)
   

   Provide correct user permissions for database user to be used by historian agent

       CREATE USER <some username> with encrypted password <some password>
       GRANT SELECT, INSERT, UPDATE on database <historian db name> to <username used above>
   

   NOTE For development environments, you can create a test database and test user, grant all privileges on that test database to the test user and let the historian create tables and indexes at startup. We do not recommend this for production environments

3. Create an agent configuration file

   Create an agent configuration with appropriate connection parameters as described in the Configurations section

4. Install and start the volttron-postgresql-historian.

   • You can either pass the configuration file directly using --agent-config

   vctl install volttron-postgresql-historian --agent-config <path to configuration> --start
   

   • OR not pass the config at install time, and use the configuration store for historian's configuration using the command

   vctl install volttron-postgresql-historian --vip-identity <unique id> --start
   vctl config store <vip-identity of postgresql historian> config <path to json config file>
   

5. View the status of the installed agent

   vctl status
   

Development

Please see the following for contributing guidelines contributing.

Please see the following helpful guide about developing modular VOLTTRON agents

Disclaimer Notice

This material was prepared as an account of work sponsored by an agency of the United States Government. Neither the United States Government nor the United States Department of Energy, nor Battelle, nor any of their employees, nor any jurisdiction or organization that has cooperated in the development of these materials, makes any warranty, express or implied, or assumes any legal liability or responsibility for the accuracy, completeness, or usefulness or any information, apparatus, product, software, or process disclosed, or represents that its use would not infringe privately owned rights.

Reference herein to any specific commercial product, process, or service by trade name, trademark, manufacturer, or otherwise does not necessarily constitute or imply its endorsement, recommendation, or favoring by the United States Government or any agency thereof, or Battelle Memorial Institute. The views and opinions of authors expressed herein do not necessarily state or reflect those of the United States Government or any agency thereof.