Problems connecting to postgres

[Victor Rodriguez]

Trying to switch over from Proxool, and getting a connection refused.

Here's my spring configuration:

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <constructor-arg index="0">

            <bean class="com.zaxxer.hikari.HikariConfig">

                <property name="poolName" value="springHikariCP"/>

                <property name="connectionTestQuery" value="SELECT 1"/>

                <property name="dataSourceClassName" value="org.postgresql.ds.PGSimpleDataSource"/>

                <property name="jdbcUrl" value="${services.memberServices.db.driverUrl}"/>

                <property name="username" value="${services.memberServices.db.user}"/>

                <property name="password">

                    <bean factory-bean="decryptor" factory-method="decryptString">

                        <constructor-arg value="${services.memberServices.db.password}"/>

                        <constructor-arg>

                            <bean factory-bean="decryptor" factory-method="getSecretKeyFromFile">

                            </bean>

                        </constructor-arg>

                    </bean>

                </property>

            </bean>

        </constructor-arg>

    </bean>

Here's my old Proxool config:

    <bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">

        <property name="alias" value="${services.memberServices.db.alias}"/>

        <property name="driverUrl" value="${services.memberServices.db.driverUrl}"/>

        <property name="driver" value="${services.memberServices.db.driver}"/>

        <property name="user" value="${services.memberServices.db.user}"/>

        <!--property name="password" value="${services.memberServices.db.password}"/-->

        <property name="password">

            <bean factory-bean="decryptor" factory-method="decryptString">

                <constructor-arg value="${services.memberServices.db.password}"/>

                <constructor-arg>

                    <bean factory-bean="decryptor" factory-method="getSecretKeyFromFile">

                    </bean>

                </constructor-arg>

            </bean>

        </property>

        <!--property name="delegateProperties" value="user=${services.memberServices.db.user},password=${services.memberServices.db.password}"/-->

        <property name="delegateProperties">

            <bean class="org.springframework.util.StringUtils" factory-method="arrayToDelimitedString">

                <constructor-arg>

                    <list>

                        <value>user=${services.memberServices.db.user},password=</value>

                        <bean factory-bean="decryptor" factory-method="decryptString">

                            <constructor-arg value="${services.memberServices.db.password}"/>

                            <constructor-arg>

                                <bean factory-bean="decryptor" factory-method="getSecretKeyFromFile">

                                </bean>

                            </constructor-arg>

                        </bean>

                    </list>

                </constructor-arg>

                <constructor-arg value=""/>

            </bean>

        </property>

        <property name="houseKeepingTestSql" value="select count(*) from ${services.memberServices.db.schema}.member_role"/>

        <property name="maximumConnectionCount" value="${services.memberServices.db.maxConnections}"/>

        <property name="maximumActiveTime" value="${services.memberServices.db.maxActiveTime}"/>

        <property name="simultaneousBuildThrottle" value="100"/>

        <!-- log connection pool statistics -->

        <property name="statistics" value="${services.memberServices.db.statisticsDef}"/>

        <property name="statisticsLogLevel" value="INFO"/>

    </bean>

Here's what I see in the debugger just before trying to connect (scrubbed for security purposes):

configuration = {com.zaxxer.hikari.HikariConfig@5531} 

 connectionTimeout = 30000

 validationTimeout = 5000

 idleTimeout = 600000

 leakDetectionThreshold = 0

 maxLifetime = 1800000

 maxPoolSize = 10

 minIdle = 10

 catalog = null

 connectionCustomizerClassName = null

 connectionInitSql = null

 connectionTestQuery = {java.lang.String@5534} "SELECT 1"

 dataSourceClassName = {java.lang.String@5535} "org.postgresql.ds.PGSimpleDataSource"

 dataSourceJndiName = null

 driverClassName = null

 jdbcUrl = {java.lang.String@5536} "jdbc:postgresql://fqdn:5434/dgcs"

 password = {java.lang.String@5537} "CORRECT"

 poolName = {java.lang.String@5538} "springHikariCP"

 transactionIsolationName = null

 username = {java.lang.String@5539} "CORRECT"

 isAutoCommit = true

 isReadOnly = false

 isInitializationFailFast = true

 isIsolateInternalQueries = false

 isRegisterMbeans = false

 isAllowPoolSuspension = false

 dataSource = null

 dataSourceProperties = {java.util.Properties@5540}  size = 0

 customizer = {com.zaxxer.hikari.AbstractHikariConfig$1@5541} 

 threadFactory = null

 metricRegistry = null

 healthCheckRegistry = null

Thoughts?

[Brett Wooldridge]

When using the DataSource style of configuration, it is necessary to define the database name via datasource properties, rather than JDBC URL.  However, I would suggest just sticking to the URL based configuration in your case.  Additionally, HikariCP can validate connections via the JDBC 4.0 isValid() method, so you can eliminate the validation query.  Try this configuration:

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <constructor-arg index="0">

            <bean class="com.zaxxer.hikari.HikariConfig">

                <property name="poolName" value="springHikariCP"/>

                <property name="jdbcUrl" value="${services.memberServices.db.driverUrl}"/>

                <property name="username" value="${services.memberServices.db.user}"/>

                <property name="password">

                    <bean factory-bean="decryptor" factory-method="decryptString">

                        <constructor-arg value="${services.memberServices.db.password}"/>

                        <constructor-arg>

                            <bean factory-bean="decryptor" factory-method="getSecretKeyFromFile">

                            </bean>

                        </constructor-arg>

                    </bean>

                </property>

            </bean>

        </constructor-arg>

    </bean>

If you get a class loading failure with the driver not being found, you can add:

   <property name="driverClassName" value="${services.memberServices.db.driver}"/>

But it may not be necessary.

[Victor Rodriguez]

No luck...here's my spring config...

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <constructor-arg index="0">

            <bean class="com.zaxxer.hikari.HikariConfig">

                <property name="poolName" value="springHikariCP"/>

                <property name="driverClassName" value="${services.memberServices.db.driver}"/>

                <property name="jdbcUrl" value="${services.memberServices.db.driverUrl}"/>

                <property name="username" value="${services.memberServices.db.user}"/>

                <property name="password">

                    <bean factory-bean="decryptor" factory-method="decryptString">

                        <constructor-arg value="${services.memberServices.db.password}"/>

                        <constructor-arg>

                            <bean factory-bean="decryptor" factory-method="getSecretKeyFromFile">

                            </bean>

                        </constructor-arg>

                    </bean>

                </property>

            </bean>

        </constructor-arg>

    </bean>

And, here's my stack trace...

 

Caused by: com.zaxxer.hikari.pool.PoolInitializationException: Exception during pool initialization

at com.zaxxer.hikari.pool.BaseHikariPool.initializeConnections(BaseHikariPool.java:514)

at com.zaxxer.hikari.pool.BaseHikariPool.<init>(BaseHikariPool.java:168)

at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:61)

at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:49)

at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:80)

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:147)

... 87 more

Caused by: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

at org.postgresql.Driver$ConnectThread.getResult(Driver.java:348)

at org.postgresql.Driver.connect(Driver.java:271)

at java.sql.DriverManager.getConnection(DriverManager.java:571)

at java.sql.DriverManager.getConnection(DriverManager.java:215)

at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:95)

at com.zaxxer.hikari.pool.BaseHikariPool.addConnection(BaseHikariPool.java:410)

at com.zaxxer.hikari.pool.BaseHikariPool.initializeConnections(BaseHikariPool.java:512)

... 96 more

Caused by: java.net.ConnectException: Connection refused

at java.net.PlainSocketImpl.socketConnect(Native Method)

at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)

at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)

at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

at java.net.Socket.connect(Socket.java:579)

at java.net.Socket.connect(Socket.java:528)

at org.postgresql.core.PGStream.<init>(PGStream.java:60)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:101)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)

at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)

at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)

at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)

at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)

at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)

at org.postgresql.Driver.makeConnection(Driver.java:393)

at org.postgresql.Driver.access$100(Driver.java:46)

at org.postgresql.Driver$ConnectThread.run(Driver.java:308)

... 1 more

Also, I tried tweaking your PostgresTest to connect to my db and I also get connection refused :(.  Will keep experimenting.  thanks!

[Victor Rodriguez]

So, just for the heck of it, I rolled back to Proxool and removed the delegateProperties and then I got "The server requested password-basedauthentication, but no password was provided." Not sure how meaningful that is, but could it be that Hikari isn't putting user=<user>,password=<password> in the url? I'll keep experimenting...

[Victor Rodriguez]

OK.  I got this to work.  While in the debugger, I realized that I now just have to extend PGSimpleDataSource and override setPassword so that it knows how to decrypt an encrypted password.  Is connectionTestQuery not recommended in this case as well?

     <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <constructor-arg index="0">

            <bean class="com.zaxxer.hikari.HikariConfig">

                <property name="poolName" value="springHikariCP"/>

                <property name="connectionTestQuery" value="SELECT 1"/>

                <property name="dataSourceClassName" value="org.postgresql.ds.PGSimpleDataSource"/>

                <property name="dataSourceProperties">

                    <props>

                        <prop key="serverName">serverName</prop>

                        <prop key="portNumber">portNumber</prop>

                        <prop key="databaseName">databaseName</prop>

                        <prop key="user">user</prop>

                        <prop key="password">cleartextPassword</prop>

                    </props>

                </property>

            </bean>

        </constructor-arg>

    </bean>

[Brett Wooldridge]

That's certainly not the stacktrace I would expect if you were using purely jdbcUrl-based configuration.  If just the jdbcUrl is being defined, no dataSourceClassName, then when this code runs...

https://github.com/brettwooldridge/HikariCP/blob/2.3.x/hikaricp-common/src/main/java/com/zaxxer/hikari/pool/PoolUtilities.java#L99

  if (dataSource == null && dsClassName != null) {
     dataSource = createInstance(dsClassName, DataSource.class);
     PropertyBeanSetter.setTargetFromProperties(dataSource, dataSourceProperties);
     return dataSource;
  }
  else if (jdbcUrl != null) {
     return new DriverDataSource(jdbcUrl, driverClassName, dataSourceProperties, username, password);
  }

I would expect the first conditional to evaluate to false, and the "else if" conditional to evaluate to true.  If that occurs (as it should), the "DataSource" class that HikariCP uses to create connections should be an instance of DriverDataSource.  And therefore, when I see this fragment in your stack trace:

at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:95)

at com.zaxxer.hikari.pool.BaseHikariPool.addConnection(BaseHikariPool.java:410)

at com.zaxxer.hikari.pool.BaseHikariPool.initializeConnections(BaseHikariPool.java:512)

I expect to see com.zaxxer.hikari.util.DriverDataSource above the addConnection() call in the stack (between addConnection() and PostgreSQL's getConnection()).

I recommend enabling DEBUG level logging for the com.zaxxer package, you should see a properties dump when the pool starts up, and possibly other informative logs coming after.

-Brett

[Brett Wooldridge]

I think you should go back to the purely jdbcUrl-based configuration, if what you need to do is decrypt the password.  Subclassing the PGSimpleDataSource would be an awful hack for a simple configuration issue.  Again, turn on DEBUG logging for the com.zaxxer package, and post the output here (after editing the username/password of course).

[Victor Rodriguez]

OK, but I don't have logging working yet since we're using log4j elsewhere.  I'll go back to the purely jdbcUrl config and set some breakpoints and see what I can report back.  Also, I'm using HikariCP-java6, so I don't know if that makes a difference.   Hopefully there aren't too completely different code paths.

        <dependency>

            <groupId>com.zaxxer</groupId>

            <artifactId>HikariCP-java6</artifactId>

            <version>2.3.5</version>

        </dependency>

[Victor Rodriguez]

SUCCESS!  I guess I must have fat-fingered something last night.  Thanks!

     <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <constructor-arg index="0">

            <bean class="com.zaxxer.hikari.HikariConfig">

                <property name="poolName" value="springHikariCP"/>