Skip to main content

Using TimescaleDB with Ignition

Install PostgreSQL

  1. Download Postgres
  2. Run the installer.

Install TimescaleDB

TimescaleDB is an extension for PostgreSQL database. Follow the installation guide below for getting the extension installed on your computer.

Installation Guide and Download

  1. Download TimescaleDB installer zip file and extract the timescaledb folder to the desktop.

  2. Add Postgres file path to system environment variables.

    1. Search Environment Variables and click Edit the system environment variables

    2. When the system properties windows comes up, make sure it's on the Advanced tab and click the Environment Variables button.

    3. Click on the Path variable in the System variables table and click the Edit... button.

    4. Double-click the next empty row in the table and paste in the path to the bin folder in the PostgreSQL installation folder.

      Example path:

      C:\Program Files\PostgreSQL\16\bin

    5. Click OK.

  3. Stop the PostgreSQL service.

  4. Right click on setup.exe within the extracted TimescaleDB folder and click Run as administrator. A command prompt will appear.

  5. Press y to tune the PostgresDB installation

  6. When prompted, paste in the path to the data folder in the PostgreSQL installation folder and hit enter

    Example path:

    C:\Program Files\PostgreSQL\16\data

  7. Continue to type y and then enter until no longer prompted to do so.

    • If the installation is not successful due to an "access denied" error, make sure you ran the setup.exe as administrator.

Setup TimescaleDB for Ignition

Now we need to go into Postgres and setup TimescaleDB for use with the Ignition tables. Use a database editor to run the following SQL commands. pgAdmin comes with Postgres and can be used.

Create a database for Ignition to use, or use your existing Ignition Postgres database. Use this database when performing the following commands.

Load the extension into the database.

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Create the hyper-table

SELECT * 
FROM create_hypertable('sqlth_1_data','t_stamp', if_not_exists => True, chunk_time_interval => 86400000, migrate_data => True);

Set up the hyper-table

Add compression to the table.

ALTER TABLE sqlth_1_data SET (timescaledb.compress, timescaledb.compress_orderby = 't_stamp DESC', timescaledb.compress_segmentby = 'tagid');

Create a function that will help translate your time stamp columns format for TimescaleDB.

CREATE OR REPLACE FUNCTION unix_now() returns BIGINT LANGUAGE SQL STABLE as $$ SELECT (extract(epoch from now())*1000)::bigint $$;

Set integer now function

SELECT set_integer_now_func('sqlth_1_data', 'unix_now');

Add compress chunks policy

SELECT add_compression_policy('sqlth_1_data', CAST ('604800000' AS INTEGER));

Run the following command to automatically drop chunks older than the specified cutoff.

SELECT add_retention_policy('sqlth_1_data', CAST ('2592000000' AS BIGINT)); -- 30 Days

Otherwise, you must manually run the following query on a schedule to re-create the same functionality.

SELECT drop_chunks(CAST ('2592000000' AS BIGINT),'sqlth_1_data'); #30 days
Back to top