Troubleshooting Syntax Error when running postgresql set up scripts

[#1]

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:

• Run this directly from bin directory of postgres deployment –

psql -q -h localhost -U twadmin -p 5432 -v database=thingworx -v tablespace=thingworx -v tablespace_location=/app/navigate/ThingworxPostgresqlStorage -v username=twadmin

• That must get into command line interface. From there  run the following with full qualified path to the sql file on disk (replace FULLPATH with the path to sql file )

\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:

1. psql.bin (9.4.11)

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.