Hi all
I having issue with Ignition and PostgreSQL DB as historian DB.
Ignition generates waring each 1 minute that sqlth_drv table is already exists - and indeed it exists, and has data inside which was added there by Ignition itself. I spend a lot of time trying to fix this warning but I cannot find the cause of it since Ignition clearly has access to DB (it creates table and writes data to it).
So one of latest test which I did is following:
Empty DB is created in PostgreSQL DB
I disabled historian provider in Ignition
Disabled DB connection to historian DB in Ignition (it referencing old DB - not new one which I created in step 1)
Cleared log files (deleted folder with DB connection name inside C:\Program Files\Inductive Automation\Ignition\data\datacache)
Changed path (in DB connection) to the new DB which was created in step 1.
Enabled DB connection.
Enabled historian provider
After all those steps Ignition created tables in a DB and starts writing data to it with no issues. No warnings yet, but in the moment when VM is rebooted or Ignition service is restarted waring appears each minute - Ignition continue writing historian data into DB with no issue (at least I am no seeing issues)
There are no other DB connection which looking in same DB.
Original set up had redundancy for Ignition but for test porpoises I shut down backup VM.
Set up which I have:
Ignition 8.1.19 Windows Server 2025 Standard v24H2
PostgreSQL v18.1 Windows Server 2025 Standard v24H2
Does somebody faced this issue before?
Is there any suggestions what can I test or do in order to figure out / fix this warning?
Try setting the gateway.Database.TableVerifier logger on the gateway to DEBUG.
I suspect we're not able to see the existing sqlth_drv table, possibly due to some permissions or scoping error with the DB, and so the translator is trying to regenerate it each time. That logger should tell you what we're trying to do.
I experimented with Extra Connection Properties → currentSchema=public
Also tried set currentSchema=public as a part of Connect URL → tsdb?currentSchema=public
Both did not change return from a logger Querying existing tables: Schema='' remaining empty.
Also tried to set all privilege's which I could find to the user which Ignition uses to connect to DB:
Firewalls on both VM are turned off completely.
Also interesting that if I create new empty DB and create new connection to it in Ignition all works without any warning until I restart the Ignition service.
Also if I delete data from a sqlth_drv after a while (few minutes) ignition will add required data back to it. So looks like it can write to it, can create it but cannot see that it exists.
I'd contact support at this point. Best case, this is something they've seen before and they can guide you to a solution. Worst case, they can reproduce it and get it filed as a bug internally that requires code changes on our part.
Are you actually using the postgres role for your connection from Ignition? That is the DB super user by default, and isn't wise to use in your application. If you want Ignition to have total control over its DB, but just that, consider creating an ignition DB user and making it the owner of the database. That enables normal table/view/whatever creation, but gives no other control over the DB cluster installation (particularly the security parts) from Ignition.
No I am not planning to use it in production, I have ignition user with all required permissions, but log constantly has warning so i decided to test it with postgres user.
It's not a production yet, it's been stages to go to production in a few weeks.
No. You need to contact Support so they can take a look at your live system and collect more information from you. This forum isn't an official support venue and there's no SLAs or other such guarantees. Get in contact with support, they'll collect more information from you, and either help you fix the problem or get a bug ticket filed internally.
The error you are receiving is most likely due to the JDBC Translator not recognizing that a table has already been created when it checks for tables. Can you check the Postgres Translator on your gateway and make sure the Translator has following
Create Table Syntax has: CREATE TABLE IF NOT EXISTS {tablename} ({creationdef}{primarykeydef})
Create Index Syntax has: CREATE INDEX IF NOT EXISTS {indexname} ON {tablename}({columnname}).
