Skip to main content

Using PostgreSQL and 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\17\bin

    5. Click OK.

    6. Try running pg_config from a command line to confirm the path is working.

  3. Stop the PostgreSQL service. 

    net stop postgresql-x64-17

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

  5. Press n to skip tuning the PostgresDB installation

  6. 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"
  7. If the installation is not successful due to an "access denied" error, make sure you ran the setup.exe as administrator.
  8. 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 (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, then run the following command to automatically create the login and passwords needed for Ignition, or manually copy/paste the following SQL statements and modifying them as needed to a PSQL console inside pgAdmin (when prompted for the password, this is for the postgres user, not the ignition user):

psql -U postgres -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 if using TimescaleDB. Enable Data Pruning and set the Pruning Age appropriately.to match the retention policy of they hypertable configured below (default if using pre-configured script is 10 years).

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 the pgAdmin database management tool to run the following SQL commands in a PSQL console.

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

To use a pre-configured script, download the postgresql_historian.sql file attached to this page and run the following command,command to 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), otherwise follow the PSQL commands to do all of this manually:

psql -U postgres -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 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);
Back to top