cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.

Troubleshooting Syntax Error when running postgresql set up scripts

No ratings

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.



Version history
Last update:
‎Apr 03, 2017 09:18 AM
Updated by:
Labels (2)