Ignition PostgreSQL Historian: Enterprise Data Storage

-- Connect to PostgreSQL as superuser
CREATE DATABASE ignition_historian;

-- Create a dedicated user for Ignition
CREATE USER ignition_user WITH PASSWORD 'secure_password_here';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE ignition_historian TO ignition_user;

-- Connect to the new database
\c ignition_historian

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO ignition_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO ignition_user;

# Ignition Gateway connection settings
# Navigate to: Config > Databases > Connections > Create new connection

Name:             PostgreSQL_Historian
Description:      Historian database for tag history and alarms
JDBC Driver:      org.postgresql.Driver
JDBC URL:         jdbc:postgresql://db-server:5432/ignition_historian
Username:         ignition_user
Password:         ********

# Connection pooling (recommended settings)
Max Active:       20
Max Idle:         10
Min Idle:         5
Validation Query: SELECT 1

# Python script to enable Tag Historian on existing tags
# Run in the Script Console or as a Gateway Event Script

# Define tag configuration with historian enabled
tag_config = {
    "name": "Temperature_Reactor_01",
    "tagType": "AtomicTag",
    "dataType": "Float8",
    "historyEnabled": True,
    "historyProvider": "PostgreSQL_Historian",
    "historySampleRate": 10000,        # 10 seconds
    "historyMaxAge": 365,              # days to retain
    "historyMaxAgeUnits": "DAY"
}

# Apply configuration to the tag path
system.tag.configure(
    basePath="[default]Plant/Reactor",
    tags=[tag_config],
    collisionPolicy="o"  # overwrite existing
)

print("Tag Historian configured for PostgreSQL.")

-- Create the partitioned historian table
-- (run this BEFORE Ignition creates its default tables,
--  or migrate existing data)

CREATE TABLE sqlt_data_1_2024 PARTITION OF sqlt_data_1
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY RANGE (t_stamp);

-- Create monthly sub-partitions for 2024
CREATE TABLE sqlt_data_1_2024_01 PARTITION OF sqlt_data_1_2024
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE sqlt_data_1_2024_02 PARTITION OF sqlt_data_1_2024
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... repeat for each month

-- Create indexes on each partition for fast lookups
CREATE INDEX idx_sqlt_data_2024_01_tstamp
    ON sqlt_data_1_2024_01 (t_stamp);
CREATE INDEX idx_sqlt_data_2024_01_tagid
    ON sqlt_data_1_2024_01 (tagid);

-- Automate partition creation with pg_partman (recommended)
SELECT partman.create_parent('public.sqlt_data_1',
    't_stamp', 'native', 'monthly');