Using PostgreSQL and TimescaleDB with Ignition

Install PostgreSQL

Download Postgres
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

Download TimescaleDB installer zip file and extract the timescaledb folder to the desktop.
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\17\bin
```
5. Click OK.
6. Try running pg_config from a command line to confirm the path is working.
Stop the PostgreSQL service.
```
net stop postgresql-x64-17
```
Right click on setup.exe within the extracted TimescaleDB folder and click Run as administrator. A command prompt will appear.
Press n to skip tuning the PostgresDB installation
Open a command prompt inside the installer folder and at the command prompt, run the following command using the appropriate PostgreSQL data path and hit enter

Example command line to append configuration to the end of the exising configuration:
```
timescaledb-tune --quiet --yes --dry-run >> "C:\Program Files\PostgreSQL\17\data"
```
Example command line to update the PostgreSQL configuration:
```
timescaledb-tune --quiet --yes "--conf-path=C:\Program Files\PostgreSQL\17\data"
```
If the installation is not successful due to an "access denied" error, make sure you ran the setup.exe as administrator.
Start the PostgreSQL service:
```
net start postgresql-x64-17
```

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

Change the lines that look like this:

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

to this:

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

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 if using TimescaleDB. Enable Data Pruning and set the Pruning Age appropriately.

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 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;

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

Add compression policy to compress chunks older than 7 days

SELECT add_compression_policy('sqlth_1_data', 604800000);

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

1 Year

SELECT add_retention_policy('sqlth_1_data', 31536000000);

2 Years

SELECT add_retention_policy('sqlth_1_data', 3072000000);

5 Years

SELECT add_retention_policy('sqlth_1_data', 157680000000);

10 Years

SELECT 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 drop_chunks('sqlth_1_data', 31536000000);