TimescaleDB
This page shows how to install and configure TimescaleDB in a PowerTSM PostgreSQL database, how to convert PowerTSM data tables into TimescaleDB hypertables, and how to update such a database.
Prerequisites
Section titled “Prerequisites”- A list of supported versions can be found here: System Requirements.
- An already installed PowerTSM database under PostgreSQL.
- TimescaleDB matching your installed PostgreSQL server.
psql.exe
Installation
Section titled “Installation”Preparation
Section titled “Preparation”Installing TimescaleDB
Section titled “Installing TimescaleDB”Follow the according steps provided by TimescaleDB for the target environment: Install TimescaleDB.
The following describes the installation process on a Windows machine:
-
Stop PostgreSQL Server Service and all services accessing PostgreSQL Server.
-
Check if the
bindirectory of the PostgreSQL server is entered in the WindowsPATHenvironment variable. -
Execute the
Setup.exeintimescaledb-postgresql-v.v_x.y.z-windows...\timescaledb\as administrator.-
The setup will prompt to optimize the
postgresql.conffile. It is recommended to apply this step. The required.conffile is located atC:\Program Files\PostgreSQL\XY\data\(XYcorresponds 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 Prompt. -
If TimescaleDB was successfully installed, you will see the following message:
TimescaleDB installation completed successfully.Press ENTER/Return key to close...
-
-
The PostgreSQL Server Service can now be started up again.
Activating TimescaleDB
Section titled “Activating TimescaleDB”To activate TimescaleDB, refer to the following tutorials: Getting started with TimescaleDB.
The following shows the steps for activation of a TimescaleDB version 1.7.4.
After installing TimescaleDB, the TimescaleDB Extension must be activated for each database where it will be used.
-
Open a command prompt.
-
Connect to PostgreSQL Server using
psql -U postgres -h localhost.C:\Windows\system32>psql -U postgres -h localhostPassword for user:psql (12.5) -
Connect to PostgreSQL database (for example to
hakom_tsm) using\c hakom_tsm.postgres=# \c hakom_tsm -
Activate TimescaleDB for the selected database using
CREATE EXTENSION IF NOT EXISTS timescaledb;.hakom_tsm=# CREATE EXTENSION IF NOT EXISTS timescaledb;WARNING:WELCOME TO_____ _ _ ____________|_ _(_) | | | _ \ ___ \| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/Running version 1.7.4For more information on TimescaleDB, please visit the following links:1. Getting started: https://docs.timescale.com/getting-started2. API reference documentation: https://docs.timescale.com/api3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architectureNote: 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 EXTENSIONhakom_tsm=#
TimescaleDB is now enabled for the database.
Converting a PowerTSM Data Table to a Hypertable
Section titled “Converting a PowerTSM Data Table to a Hypertable”The following steps are based on these TimescaleDB tutorials:
The following steps create a TimescaleDB Hypertable on the basis of a standard PowerTSM data table (FWT_TSDATA).
Create an Empty Copy of the Data Table
Section titled “Create an 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);
hakom_tsm=# CREATE TABLE ZAMS.FWT_TSDATA_TSCDB (LIKE ZAMS.FWT_TSDATA INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);CREATE TABLEhakom_tsm=#Convert the Data Table into a Hypertable
Section titled “Convert the Data Table into a Hypertable”Determine Partition Size
Section titled “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 the chunk_time_interval parameter to specify an interval at which the data should be partitioned.
Assuming that time series data are mostly retrieved over a complete day or a multiple of it, 1 day (i.e. 86,400 seconds, or 86,400,000 milliseconds) is chosen as the partition size.
The following assumes a PowerTSM database with internal time unit second.
Convert Table into Hypertable
Section titled “Convert Table into Hypertable”The FWT_TSDATA_TSCDB table is converted into a TimescaleDB hypertable with chunk_time_interval 1 day with the following command: SELECT create_hypertable('ZAMS.FWT_TSDATA_TSCDB', 'time', chunk_time_interval => 86400);
hakom_tsm=# SELECT create_hypertable('ZAMS.FWT_TSDATA_TSCDB', 'time', chunk_time_interval => 86400); create_hypertable--------------------------- (2,zams,fwt_tsdata_tsc,t)(1 row)Adding Further Dimensions
Section titled “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.
More information about TimescaleDB dimensions
Migrate Time Series Data
Section titled “Migrate 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;
hakom_tsm=# INSERT INTO ZAMS.FWT_TSDATA_TSCDB SELECT * FROM ZAMS.FWT_TSDATA;INSERT 0 82772The table is migrated and ready to use!
Migrate Table in Time Series Definitions
Section titled “Migrate Table in Time Series Definitions”To use the table in PowerTSM components, the according tables (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:
hakom_tsm=# UPDATE FWT_TIMESERIES SET actualdatatable='FWT_TSDATA_TSCDB' WHERE actualdatatable='FWT_TSDATA';UPDATE 30Update
Section titled “Update”Double check with PowerTSM support whether the table structure of PowerTSM 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
Section titled “Safe Update”TimescaleDB tables behave within PostgreSQL as standard PostgreSQL tables, as such PowerTSM update scripts for PostgreSQL should be also executable against TimescaleDB tables. To be on the safe side the following steps are recommended for updates.
- Run the standard PowerTSM PostgreSQL update → that will update all PowerTSM tables under standard PowerTSM naming conventions and skipping newly created tables (that are used within TimescaleDB scope).
- Create new TimescaleDB Hypertables as described in Converting a PowerTSM Data Table to a Hypertable.
- Migrate Time Series Data from old Hypertable into new Hypertable using a simple insert into query (similar to Migrate Time Series Data).
- Delete old Hypertable.
- Rename new Hypertable to match with time series definitions in
FWT_TIMESERIES(using ALTER TABLE) or updateFWT_TIMESERIESwith the new table name.
Simple and Quick Update
Section titled “Simple and Quick Update”As an alternative to the Safe Update procedure you may also try to run PowerTSM update scripts against TimescaleDB Hypertables directly, since these are supposed to fully support PostgreSQL schema management commands (ALTER TABLE schema management).