Community Tip - You can change your system assigned username to something more personal in your community settings. X
In the following scenario (for redhat in this case), running the dbsetup script results in the error:
./thingworxPostgresDBSetup.sh
psql:./thingworx-database-setup.sql:1: ERROR: syntax error at or near ":"
LINE 1: CREATE TABLESPACE :"tablespace" OWNER :"username" location :...
^
psql:./thingworx-database-setup.sql:3: ERROR: syntax error at or near ":"
LINE 1: GRANT ALL PRIVILEGES ON TABLESPACE :"tablespace" to :"userna...
^
psql:./thingworx-database-setup.sql:5: ERROR: syntax error at or near ":"
LINE 1: GRANT CREATE ON TABLESPACE :"tablespace" to public;
^
psql:./thingworx-database-setup.sql:14: ERROR: syntax error at or near ":"
LINE 1: CREATE DATABASE :"database" WITH
^
psql:./thingworx-database-setup.sql:16: ERROR: syntax error at or near ":"
LINE 1: GRANT ALL PRIVILEGES ON DATABASE :"database" to :"username";
Given that the installed components match the requirements guide (tomcat 8, Postgresql 9.4.5+ for Thingworx 7.x), run the following command:
psql -q -h localhost -U twadmin -p 5432 -v database=thingworx -v tablespace=thingworx -v tablespace_location=/app/navigate/ThingworxPostgresqlStorage -v username=twadmin
\i ./FULLPATH/thingworx-database-setup.sql
If you are experiencing the above-mentioned syntax error, then likely the output will be:
psql: FATAL: database "twadmin" does not exist.
Then from postgres bin directory, run the following:
./psql postgres
\set
Then the second command;
\q
psql -q -h localhost -U twadmin -p 5432 -v database=thingworx -v tablespace=thingworx -v tablespace_location=/app/navigate/ThingworxPostgresqlStorage -v username=twadmin
\set
We see the following outputs:
./psql postgres
Password:
Type "help" for help.
postgres=# \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'PostgreSQL 9.4.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit'
DBNAME = 'postgres'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UTF8'
postgres=# \q
-bash-4.1$ psql -q -h localhost -U twadmin -p 5432 -v database=thingworx -v tablespace=thingworx -v tablespace_location=/ThingworxPostgresqlStorage -v username=twadmin
Password for user twadmin:
twadmin=# \set
AUTOCOMMIT = 'on'
QUIET = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'PostgreSQL 8.4.20 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit'
database = 'thingworx'
tablespace = 'thingworx'
tablespace_location = '/ThingworxPostgresqlStorage'
username = 'twadmin'
DBNAME = 'twadmin'
USER = 'twadmin'
HOST = 'localhost'
PORT = '5432'
ENCODING = 'UTF8'
Note, even though Postgresql 9.4.5 has been installed by the system administrator, there are still traces of Postgresql 8.4.20 present in the system that cause the syntax error issue (possibly as part of the default OS packaging). Removing the 8.4.20 rpms will resolve the problem.