Postgres Error: root entity collection does not exist

[qn]

Hi,

When I install the new Thingworx Postgre 6.5.2 (but there is the same problem with 6.5.0 and 6.5.1), the Thingworx Composer could not start. In "Application.log", there is an error of Postgre:

..
Reading configuration from configTableCol
Datastore database...ON
CRITICAL ERROR ON STARTUP: ERROR: relation "root_entity_collection" does not exist
...

I used an PostgreSQL client to check the database and there is the table "root_entity_collection". Does someone had the same problem and how did you resolve it ?

My postgre user, created for Thingworx, is not the same as my linux user. By the way, I wonder if Thingworx use the linux user to login to Postgre DB. It would not be possible, I even did not give the postgre username and password yet. I even can't start the Thingworx Composer. Tomcat8 does not have any error.

[qn · Accepted solution]

The problem happens when Postgre does not have the right schema name in "search_path". By default, the search_path is '"$user",public'​ (SHOW search_path). That means Postgre will look for schema with name [postgre_user_name] or public​ schema. By default, all database have one public schema.  So there are some solutions for this problem:

1. Change the search_path
   1. SET search_path TO mySchema, public;
   2. Modify the file "postgresql.conf" (in /etc/postgresql/[postgre_version]/main/postgresql.conf)
2. Use the postgre user name for schema name ("$user")
3. Use the public schema by default

[Arpit-Singh]

For me it was permission on the Postgres and Tomcat folder.

[qn]

What are now your permissions on the Postgres and Tomcat folders to fix the issue ?

[Arpit-Singh]

My username didn't had write permissions on those 2 folders so it was giving the same error in the Application.log file.