Skip to main content
Skip table of contents

TimescaleDB


Requirements

  • A list of supported Versions can be found here: System Requirements
  • TimescaleDB matching your installed PostgreSQL server.
  • psql.exe

This installation assumes an already installed HAKOM TSM database under PostgreSQL. The following steps show how to install and configure TimescaleDB in a HAKOM TSM PostgreSQL database.

After installing TimescaleDB. restores using files created using pg_dump will not work anymore. In order to restore a dump file, the TimescaleDB Extension must be deactivated temporarily.

Installation

Preparation

Before installing TimescaleDB, as with any database change, a backup of the current database should be performed (pg_dump -U ZAMS databasename > databasename.sql)

Download the latest TimescaleDB binaries for the appropriate PostgreSQL server and operating system version:

https://docs.timescale.com/latest/getting-started/installation

Installling TimescaleDB

Depending on your environment please follow the according steps provided by TimescaleDB: https://docs.timescale.com/latest/getting-started/installation

The following describes the installation process on a windows machine:

  1. Stop PostgreSQL Server Service and all services accessing PostgreSQL Server
  2. Check if the bin directory of the PostgreSQL server is entered in Windows %PATH% environment variables
  3. Execute the Setup.exe in timescaledb-posgresql-v.v_x.y.z-windows...\timescaledb\ as administrator
    1. The setup will prompt to optimize the postgresql.conf file. It is recommended to apply this step. The required "conf" file is located at C:\Program Files\PostgreSQL\XY\data\ (XY corresponds to the respective major and minor version, for example 12). If the optimization option was selected, some scaling and memory optimization steps have to be confirmed within Command Pompt.

    2. If TimscaleDB was successfully installed, you will see the following message:

      BASH 
      TimescaleDB installation completed successfully. 
Press ENTER/Return key to close...

4. The PostgreSQL Server Service can now be started up again

Activating TimescaleDB

To activate TimescaleDB in your environment please refer to the following tutorials: https://docs.timescale.com/latest/getting-started/setup

The following shows the steps for activation of a TimescaleDB version 1.7.4.

After installing TimescaleDB, you have to activate the TimescaleDB Extension for the databases where it will be used.

  1. Open a command prompt

  2. Connect to PostgreSQL Server using psql -U postgres -h localhost

    BASH 
    C:\Windows\system32>psql -U postgres -h localhost
Password for user:
psql (12.5)

  3. Connect to PostgreSQL database (for example to "hakom_tsm") using \c hakom_tsm

    BASH 
    postgres=# \c hakom_tsm

  4. Activate TimescaleDB for the selected database using CREATE EXTENSION IF NOT EXISTS timescaledb;

    BASH 
    hakom_tsm=# CREATE EXTENSION IF NOT EXISTS timescaledb;
WARNING:
WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.7.4
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION
hakom_tsm=#

TimescaleDB is now enabled for your database.

HAKOM TSM data Table to TimescaleDB Hypertable

The following steps are based on these TimescaleDB tutorials:

Next we will create a TimescaleDB Hypertable on the basis of a standard HAKOM TSM data table (FWT_TSDATA).

Tip

The same procedure can be applied on any other type of HAKOM TSM tables, such as FWT_TSDATAARCHIVE, FWT_TSDATACOMPRESSED or FWT_TSQUOTATIONARCHIVE).

1. Empty Copy of the data table

First an empty copy of the desired table must be created: CREATE TABLE ZAMS.FWT_TSDATA_TSCDB (LIKE ZAMS.FWT_TSDATA INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); 

BASH 
hakom_tsm=# CREATE TABLE ZAMS.FWT_TSDATA_TSCDB (LIKE ZAMS.FWT_TSDATA INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
CREATE TABLE
hakom_tsm=#


The new table must be granted for the hakom user which is used for the database connections in HAKOM.Config:

BASH 
hakom_tsm=# GRANT SELECT, INSERT, UPDATE, DELETE ON FWT_TSDATA_TSCDB TO hakom;
GRANT

2. Convert the data table into TimescaleDB Hypertable

Determine partition size

For the conversion into a TimescaleDB table a date column or optionally an integer column must be defined. For date columns TimescaleDB can derive the chunk interval lengths from the date. For integer columns, like the "Time" column in FWT_TSDATA, TimescaleDB must be told which date logic the "Time" column follows. This is done by using chunk_time_interval command to specify an interval at which the data should be partitioned.

Since the FWT_TSDATA table can contain time series data of different time series and grids, different partition sizes and thus chunk interval lengths may be useful. Thus, there is no universal chunk size for a HAKOM time series data table which provides the best performance under all circumstances. If different chunk sizes are used, the FWT_TSDATA table can be migrated into several differently partitioned TimescaleDB hypertables with only a part of the time series. 

We assume that mostly time series data are retrieved over a complete day or a multiple of it. Therefore we choose 1 day (i.e. 86,400 seconds, or 86,400,000 milliseconds) as partition size.

HAKOM supports in the current version 3 different time units, (0) seconds since 1.1.1972 UTC+1, (1) seconds since UNIX epoch (1.1.1970 UTC) and (2) milliseconds since UNIX epoch. Depending on which method is activated as InternalTimeUnit, the partitioning size in seconds or milliseconds

We assume a HAKOM database with internal time unit second.

Convert table into Hyperabelle

The FWT_TSDATA_TSCDB table is converted into a TimescaleDB hypertable with chunk_time_intervall 1 day with the following command: SELECT create_hypertable('ZAMS.FWT_TSDATA_TSCDB', 'time', chunk_time_interval => 86400);

BASH 
hakom_tsm=# SELECT create_hypertable('ZAMS.FWT_TSDATA_TSCDB', 'time', chunk_time_interval => 86400);
     create_hypertable
---------------------------
 (2,zams,fwt_tsdata_tsc,t)
(1 Zeile)

More information about TimescaleDB time units: https://docs.timescale.com/latest/api#create_hypertable-units

Adding further dimensions

Under certain circumstances, additional time dimensions can be useful. After creating the hypertable, before filling it with data, additional time dimensions can be added for partitioning purposes. However, this is not necessary for a FWT_TSDATA table.

It is not recommended to add the column "timeseries_id" (integer column) as an additional dimension, as this could lead to unexpected results in TimescaleDB.

More information about TimescaleDB dimensions: https://docs.timescale.com/latest/api#add_dimension

3. Migrate HAKOM time series data

Now all or selected data from the FWT_TSDATA table can be migrated to the newly created hypertable. For all data use: INSERT INTO ZAMS.FWT_TSDATA_TSCDB SELECT * FROM ZAMS.FWT_TSDATA;

BASH 
hakom_tsm=# INSERT INTO ZAMS.FWT_TSDATA_TSCDB SELECT * FROM ZAMS.FWT_TSDATA;
INSERT 0 82772


Out of shared memory error

If an out of shared memory error occurs during inserting data, then the partitioning size (chunk_time_interval) was too small, or the max_locks_per_transaction setting of the server is set too low.

CODE 
FATAL: out of shared memory 
HINT: You might need to increase max_locks_per_transaction.

To change the max_locks_per_transaction setting of the server, the following steps should be followed:

  1. Stop PostgreSQL server
  2. open \postgresql\data\postgresql.conf
  3. max_locks_per_transaction = change to chunk size * 2
  4. Start PostgreSQL server
  5. Perform insert again

More information about transaction locks: https://docs.timescale.com/latest/getting-started/configuring#locks

The table is migrated and ready to use!

Migrate table in HAKOM time series definitions

To use the table in HAKOM components the according talbes (actualdatatable for live data or archivedatatable for historical data) must be updated in FWT_TIMESERIES accordingly.

The following query shows the update of all time series that use standard FWT_TSDATA table to the new Hypertable:...................................................................................................................................................................................................................................

BASH 
hakom_tsm=# UPDATE FWT_TIMESERIES SET actualdatatable='FWT_TSDATA_TSCDB' WHERE actualdatatable='FWT_TSDATA';
UPDATE 30

Update

Double check with HAKOM Support, whether table structure of HAKOM TSM data tables used within TimescaleDB have been changed. If not, a standard PostgreSQL update can be conducted. If yes, follow the below instructions.

Safe update

TimescaleDB tables behave within PostgreSQL as standard PostgreSQL tables, as such HAKOM update scripts for PostgreSQL should be also executable against TimescaleDB tables. To be on the safe side we recommend following steps for updates.

  1. Run standard HAKOM PostgreSQL update → that will update all HAKOM tables under standard HAKOM naming conventions and skipping newly created tables (that are used within TimescaleDB scope)
  2. Create new TimescaleDB Hypetables as described in https://hakom.atlassian.net/wiki/spaces/TES/pages/11174679
  3. Migrate Time Series Data from old Hypertable into new Hypertable using a simple insert into query (similar to https://hakom.atlassian.net/wiki/spaces/TES/pages/11174679
  4. Delete old Hypertable
  5. Rename new Hypertable to match with time series definitions in FWT_TIMESERIES (using ALTER TABLE: https://docs.timescale.com/latest/using-timescaledb/schema-management#updating-schemas) or update FWT_TIMESERIES with the new table name.

Simple and quick update

As an alternative to the Safe update procedure you may also try to run HAKOM Update scripts against TimescaleDB Hypertables directly, since these supposed to fully support PostgreSQL schema management commands (https://docs.timescale.com/latest/using-timescaledb/schema-management#updating-schemas). 

Note this variant is not tested by HAKOM and we cannot guarantee, that TimescaleDB correctly apply all schema management commands.
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close