Redshift driver and Postgres JDBC intercept jdbc: // postgresql connection string

Question

I have a problem that I'm not quite sure how to solve: I have a web application (packaged like a war) and clients can configure which database they want to point to. We support both PostgreSQL and Redshift (as well as others). JDBC4 drivers load automatically, which is good. Here's the problem:

It appears that the Redshift JDBC driver will respond to the jdbc: // postgresql connection string before PostgreSQL. This causes JDBC errors when connecting to a PostgreSQL database.

I specify the driver name "org.postgresql.Driver" as the driver for the data source in my pom.xml, but I'm not sure how the JDBC spring templates select the driver (if it does not pick up the first handler).

Does anyone else encounter such a problem?

Answer 1

This is because the redshift driver is registered as being able to handle both the jdbc:postgresql URL prefix and the jdbc:redshift URL prefix.

When the Postgres and Redshift drivers boot from their cans, each of them registers with DriverManger.

The logic implemented in DriverMananger.getDriver() and DriverManager.getConnection() is a loop through each of the drivers and stops as soon as the driver indicates that it is able to process this URL.

If the Postgres driver is registered first, everything works fine, because the Postgres driver is trying to process jdbc:postgresql . If the Redshift driver manages to register first, then the Postgres driver will never be used.

The only way I decided to solve is to add:

 static { // Put the redshift driver at the end so that it doesn't // conflict with postgres queries java.util.Enumeration<Driver> drivers = DriverManager.getDrivers(); while (drivers.hasMoreElements()) { Driver d = drivers.nextElement(); if (d.getClass().getName().equals("com.amazon.redshift.jdbc41.Driver")) { try { DriverManager.deregisterDriver(d); DriverManager.registerDriver(d); } catch (SQLException e) { throw new RuntimeException("Could not deregister redshift driver"); } break; } } } 

Answer 2

Another solution would be to add "OpenSourceSubProtocolOverride = true" to the JDBC connection string for regular PostgreSQL connections.

Example:

 jdbc:postgresql://localhost:5432/postgres?OpenSourceSubProtocolOverride=true