Connecting QuerySurge to PostgreSQL

Versions: 8.0+

JDBC connectivity to PostgreSQL is achieved in much the same way as is connectivity to other databases - via a JDBC driver. The PostgreSQL JDBC driver is deployed to QuerySurge Agents, and the Connection is set up using the Connection Extensibility feature of the QuerySurge Connection Wizard.

Setting up a QuerySurge Connection with the PostgreSQL JDBC Driver

A QuerySurge PostgreSQL Connection is set up using the Connection Extensibility feature of the QuerySurge Connection Wizard.

1. Download the PostgreSQL JDBC driver. As of QuerySurge 6.4+, QuerySurge uses Java 8; versions 4.3 - 6.3 use Java 7. Download the JDBC 4.2 driver for Java 8 versions of QuerySurge, or the 4.1 driver for Java 7 versions. The driver download can be found here.
   
   Note: We have some reports of the JDBC 4.1 driver mishandling JDBC URLs. 
   
   
2. Deploy the PostgreSQL JDBC driver jar to your Agent(s). The procedure for deploying a new driver to a QuerySurge Agent is here (for Agents on Windows) and here (for Agents on Linux).
   
   
3. Log into QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Steps for using the Connection Extensibility feature can be found here. To use the Connection Extensibility option in the Connection Wizard with the PostgreSQL driver, you'll need the following information:
   
   Driver Class:
   org.postgresql.Driver
   Connection URL template: 
   jdbc:postgresql://<server-or-IP>:<port>/schema-name

   Sample Connection URL:
   jdbc:postgresql://127.0.0.1:5432/PXMART
   
   Note: The default port for PostgreSQL is 5432.
   
   Note: The PostgreSQL driver supports a broad range of URL connection properties that you can see here.

    Note: The JDBC URL for PostgreSQL is the same structure as Amazon's Redshift URL. Depending on your driver version, this might cause unexpected behavior when querying or extracting metadata when both drivers are co-deployed. To avoid utilizing the Redshift driver add the value "OpenSourceSubProtocolOverride=true" to the Postgres URL.

   When you've entered your Connection information, the Connection Wizard will look like:
   
   
   
   
4. If you have a Test Query, feel free to enter it in order to verify that your Connection parameters are correct. A SELECT 1 query is valid in PostgreSQL. However, any standard query that returns a small amount of information is appropriate - one row is enough. You can use the Test Connection button if you've entered a Test Query:
   
   
   
   The Connection test gives a popup showing whether the Connection was successfully completed:
   
   
5. Once you have the information entered and (optionally) verified with a Connection test, click the Save button to save the Connection. You're ready to use the Connection in a Query