Using PostgreSQL and TimescaleDB with Ignition

Installing in Windows

Follow the guide here to install PostgreSQL and TimescaleDB in Windows.

Installing PostgreSQL and TimescaleDB in Windows

Installing in Ubuntu

Follow the guide here to install PostgreSQL and TimescaleDB in Ubuntu Linux.

Installing PostgreSQL and TimescaleDB in Ubuntu

Setup PostgreSQL for Ignition

Configure PostgreSQL for remote connections

Edit the following file with a text editor:

C:\Program Files\PostgreSQL\17\data\pg_hba.conf

If using Ubuntu/Debian, run the following command to edit this file:

sudo nano /etc/postgresql/17/main/pg_hba.conf

Change the lines that look like this:

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

to this (adjust the additional line to a specific subnet if you want to restrict access to specific IPs rather than allowing any IPv4 connection):

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

Creating the user and database for Ignition

You can download the postgresql_ignition.sql attached to this page and modify the ignition password in the file. This script will automatically create the login and passwords needed for Ignition. You can also manually copy/paste the below SQL scripts/commands to the PSQL console if you wish to do it manually.

For Windows, run the following command to automatically create the login and passwords needed for Ignition:

psql -U postgres -f postgresql_ignition.sql

If using Ubuntu/Debian, run the following command with the file in the same folder you're running it from:

cp postgresql_ignition.sql /tmp
sudo -u postgres psql -f postgresql_ignition.sql

Creating the Ignition user/login

CREATE ROLE ignition WITH
	LOGIN
	SUPERUSER
	CREATEDB
	CREATEROLE
	INHERIT
	REPLICATION
	BYPASSRLS
	CONNECTION LIMIT -1
	PASSWORD 'ignition_password';

Create the Historian, AlarmLog, and AuditLog databases

CREATE DATABASE historian
    WITH
    OWNER = ignition
    ENCODING = 'UTF8'
    LOCALE_PROVIDER = 'libc'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

CREATE DATABASE alarmlog
    WITH
    OWNER = ignition
    ENCODING = 'UTF8'
    LOCALE_PROVIDER = 'libc'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

CREATE DATABASE auditlog
    WITH
    OWNER = ignition
    ENCODING = 'UTF8'
    LOCALE_PROVIDER = 'libc'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

Configure Ignition

Set up 3 databases (one each) for the previous databases using the correct IP, database name, and login credentials. These should be named:

AlarmLog
AuditLog
Historian

Next, go to Tags -> History and disable the tag history provider setting for AlarmLog and AuditLog. Then edit the Historian entry and disable Data Partitioning only if using TimescaleDB. If not using TimescaleDB, leave data partitioning set to 1 month. Also disable Data Pruning if using TimescaleDB, otherwise, leave it enabled and set the Pruning Age to an appropriate timeframe to prevent filling the disk storage.

Now go to Alarming -> Journal and create a new database Alarm Journal Profile and name it AlarmLog and point it to the AlarmLog datasource. Enable Data Pruning and set the Pruning Age appropriately.

Now go to Security -> Auditing and create a new database Audit Profile and name it AuditLog and point it to the AuditLog datasource. Enable Data Pruning and set the Pruning Age appropriately.

Setup TimescaleDB for Ignition

Now we need to go into PostgreSQL and setup TimescaleDB for use with the Ignition tables.

To use a pre-configured script to configure TimescaleDB, download the postgresql_historian.sql file attached to this page. You'll need to make sure Ignition has created the sqlth_1_data table inside the historian database before proceeding. The pre-configured script will configure the historian with 24-hour chunks that are compressed after 7 days and purged after 10 years (you can modify the file to adjust any of these settings before running it). If you'd prefer, you can run the below PSQL commands to do all of this manually.

Use the previously created historian database when performing the following commands.

In Windows, run the following command to do this automatically:

psql -U postgres -d historian -f postgresql_historian.sql

Win Ubuntu/Debian, run the following command to do this automatically:

cp postgresql_historian.sql /tmp
sudo -u postgres psql -d historian -f postgresql_historian.sql

Load the extension into the database.

CREATE EXTENSION IF NOT EXISTS timescaledb;

Create the hyper-table

This will partition the table into 24 hour chunks.

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 * FROM set_integer_now_func('sqlth_1_data', 'unix_now');

Add compression policy to compress chunks older than 7 days

CALL add_columnstore_policy('sqlth_1_data', 604800000);

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

1 Year

SELECT * FROM add_retention_policy('sqlth_1_data', 31536000000);

2 Years

SELECT * FROM add_retention_policy('sqlth_1_data', 3072000000);

5 Years

SELECT * FROM add_retention_policy('sqlth_1_data', 157680000000);

10 Years

SELECT * FROM add_retention_policy('sqlth_1_data', 315360000000);

Otherwise, you must manually run the following query on a schedule to re-create the same functionality using values from above (1 year shown).

SELECT * FROM drop_chunks('sqlth_1_data', 31536000000);