How to configure Columbus's postgres databases as a Data Sources in Spotfire.

How to configure Columbus's postgres databases as a Data Sources in Spotfire.

Columbus uses PostgreSQL 8.4.20 for its databases. Follow these steps to setup a Data Source in Spotfire to connect to the Columbus Server's columbus_webapp and omero4_4 databases.

On the Columbus Server

• Create a new postgres user. Some further reading can be found here.
  At its simplest level to create a new super user 'joe' prompting for a password use the following in the Command Line Interface:
  $ su - postgres
  $ $ createuser -P -s -e joe

On the Spotfire Server

• Download the jdbc driver here. As an example at time of writing the latest is postgresql-42.2.14.jar
• Copy this file to the Spotfire Servers install directory:
  /opt/tibco/tss/SPOTFIRE_VERSION/tomcat/lib
• Run the configuration tool via uiconfig.bat If you are unfamiliar with the use of this tool consult the Spotfire Server Installation Guide.
• Go to Configuration > Data Source Templates
• Click the New button, add the PostgresSQL template available here, name and save
• At this point you should see something like this:

On the Spotfire Client

On starting the Spotfire Client you should also see Postgres as a Data Source Type available in the Information Designer (Tools > Information Designer > Setup Data Source > Type)

Typical connection URL/s would be as follows:

jdbc:postgresql://columbus:5432/omero4_4

jdbc:postgresql://columbus:5432/columbus_webapp

Where columbus is the hostname of the server, 5432 the default port for PostgreSQL and omero4_4 or columbus_webapp the database name.

Common errors and troubleshooting: