JDBC connectivity issue

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby
configuration. Running postgres 9.4 on centos6.

What we are trying to accomplish is in an event of a failover, to first
try to connect to the master. If that fails to then reach out to the
standby.

I looked online and found the suggested way to do this, but it's not
working.

This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

That seems to be correct:

https://jdbc.postgresql.org/documentation/94/connect.html
"Connection Fail-over

To support simple connection fail-over it is possible to define multiple
endpoints (host and port pairs) in the connection url separated by
commas. The driver will try to once connect to each of them in order
until the connection succeeds. If none succeed, a normal connection
exception is thrown.

The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database"

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf -
failed to set properly port number on target class
org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for
input string: "5432,10.16.10.13:5432"/

It looks like the first IP address has disappeared or is not set right./

To me it looks whatever code you are using is trying to us
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other
words it is not seeing the ',' as a separator for the two IP's'.

Can you provide more information about the JDBC version you are using
and the app code you are using?

/

Thanks in advance for the help.

//

--
Adrian Klaver
adrian.klaver@aklaver.com

My interpretation of that error is slightly different. I think what it is
saying is that for the first IP address, it thinks the port number is the
full string "5432,10.16.10.13:5432" and fails when it tries to convert that
into a port number. Are you positive the URL format you are trying to use
is supported by the JDBC driver your using?

On 9 March 2018 at 08:12, chris <chrisk@pgsqlrocket.com> wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby
configuration. Running postgres 9.4 on centos6.

What we are trying to accomplish is in an event of a failover, to first
try to connect to the master. If that fails to then reach out to the
standby.

I looked online and found the suggested way to do this, but it's not
working.

This is the JDBC line we have:

*jdbc.url=jdbc:postgresql://10.16.10.12:5432
<http://10.16.10.12:5432&gt;,10.16.10.13:5432/app_db
<http://10.16.10.13:5432/app_db&gt;*

Here is the error we are getting:

*2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf -
failed to set properly port number on target class
org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for
input string: "5432,10.16.10.13:5432 <http://10.16.10.13:5432&gt;&quot;*

It looks like the first IP address has disappeared or is not set right.

Thanks in advance for the help.

--
regards,

Tim

--
Tim Cross

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby
configuration. Running postgres 9.4 on centos6.

What we are trying to accomplish is in an event of a failover, to first
try to connect to the master. If that fails to then reach out to the
standby.

I looked online and found the suggested way to do this, but it's not
working.

This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf -
failed to set properly port number on target class
org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for
input string: "5432,10.16.10.13:5432"/

Lets try that again:

To me it looks like whatever code you are using is trying to use
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other
words it is not seeing the ',' as a separator for the two IP's'.

Time to clean the glasses:)

It looks like the first IP address has disappeared or is not set right./
/

Thanks in advance for the help.

//

--
Adrian Klaver
adrian.klaver@aklaver.com

Given that the syntax looks correct for the url, how would we go about
debugging that it's not seeing the comma?

On 03/08/2018 01:30 PM, chris wrote:

Given that the syntax looks correct for the url, how would we go about
debugging that it's not seeing the comma?

From previous post:

Can you provide more information about the JDBC version you are using
and the app code you are using?

On 03/08/2018 02:27 PM, Adrian Klaver wrote:

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby
configuration. Running postgres 9.4 on centos6.

What we are trying to accomplish is in an event of a failover, to
first try to connect to the master. If that fails to then reach out
to the standby.

I looked online and found the suggested way to do this, but it's not
working.

This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf -
failed to set properly port number on target class
org.postgresql.ds.PGSimpleDataSource
java.langNumberFormatException:for input string:
"5432,10.16.10.13:5432"/

Lets try that again:

To me it looks like whatever code you are using is trying to use
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other
words it is not seeing the ',' as a separator for the two IP's'.

Time to clean the glasses:)

It looks like the first IP address has disappeared or is not set right./
/

Thanks in advance for the help.

//

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Mar 8, 2018 at 2:30 PM, chris <chrisk@pgsqlrocket.com> wrote:

Given that the syntax looks correct for the url, how would we go about
debugging that it's not seeing the comma?

​First thing I'd do is ensure the version of the driver I'm using supports
the feature I'm trying to use.

David J.

You should be using the latest version of the driver. What version are you
using ?

Even though you have a 9.4 database the latest version is the correct
version to use.

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 8 March 2018 at 22:14, David G. Johnston <david.g.johnston@gmail.com>
wrote:

I'm sorry that took a few days but I am running;

Postgresql-9.4

and

jre7.jar

Thanks in advance.

On 03/13/2018 04:46 PM, chris wrote:

I'm sorry that took a few days but I am running;

Postgresql-9.4

and

jre7.jar

What we are looking for is the JDBC driver you are using?

Thanks in advance.

On 03/08/2018 02:30 PM, chris wrote:

Given that the syntax looks correct for the url, how would we go about
debugging that it's not seeing the comma?

On 03/08/2018 02:27 PM, Adrian Klaver wrote:

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot
standby configuration. Running postgres 9.4 on centos6.

What we are trying to accomplish is in an event of a failover, to
first try to connect to the master. If that fails to then reach out
to the standby.

I looked online and found the suggested way to do this, but it's not
working.

This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf -
failed to set properly port number on target class
org.postgresql.ds.PGSimpleDataSource
java.langNumberFormatException:for input string:
"5432,10.16.10.13:5432"/

Lets try that again:

To me it looks like whatever code you are using is trying to use
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other
words it is not seeing the ',' as a separator for the two IP's'.

Time to clean the glasses:)

It looks like the first IP address has disappeared or is not set
right./
/

Thanks in advance for the help.

//

--
Adrian Klaver
adrian.klaver@aklaver.com

I believe its Postgresql-9.4.1208.jre7.jar

On 03/14/2018 07:51 AM, chris wrote:

I believe its Postgresql-9.4.1208.jre7.jar

Pretty sure solving this is going to require knowing exactly what driver
is in use. The failover syntax looks to be fairly recent, so being off
by a little on the driver version can make a big difference.

Or you could do as this post suggests:

/messages/by-id/CADK3HHJgdio_TZ-fpk4rguWaA-wWZFNZrjBft_T4jLBK_E_c8w@mail.gmail.com

That is install driver version 42.2.1.

On 03/13/2018 05:48 PM, Adrian Klaver wrote:

On 03/13/2018 04:46 PM, chris wrote:

I'm sorry that took a few days but I am running;

Postgresql-9.4

and

jre7.jar

What we are looking for is the JDBC driver you are using?

Thanks in advance.

On 03/08/2018 02:30 PM, chris wrote:

Given that the syntax looks correct for the url, how would we go
about debugging that it's not seeing the comma?

On 03/08/2018 02:27 PM, Adrian Klaver wrote:

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot
standby configuration. Running postgres 9.4 on centos6.

What we are trying to accomplish is in an event of a failover, to
first try to connect to the master. If that fails to then reach
out to the standby.

I looked online and found the suggested way to do this, but it's
not working.

This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf
- failed to set properly port number on target class
org.postgresql.ds.PGSimpleDataSource
java.langNumberFormatException:for input string:
"5432,10.16.10.13:5432"/

Lets try that again:

To me it looks like whatever code you are using is trying to use
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In
other words it is not seeing the ',' as a separator for the two IP's'.

Time to clean the glasses:)

It looks like the first IP address has disappeared or is not set
right./
/

Thanks in advance for the help.

//

--
Adrian Klaver
adrian.klaver@aklaver.com

Oh sorry for getting the wrong information.

How would I get the information on what driver is currently installed?

On 03/14/2018 01:47 PM, chris wrote:

Oh sorry for getting the wrong information.

At this point not sure whether it is wrong information or not.

How would I get the information on what driver is currently installed?

I am not a Java programmer, so I am not the best person to answer this.
Still I would the think the place to start would be the connection code
itself. Another way would be examining CLASSPATH:

https://jdbc.postgresql.org/documentation/head/classpath.html

On 03/14/2018 09:44 AM, Adrian Klaver wrote:

On 03/14/2018 07:51 AM, chris wrote:

I believe its Postgresql-9.4.1208.jre7.jar

Pretty sure solving this is going to require knowing exactly what
driver is in use. The failover syntax looks to be fairly recent, so
being off by a little on the driver version can make a big difference.

--
Adrian Klaver
adrian.klaver@aklaver.com

I wasnt able to find what version we had installed so we went ahead and
reinstalled it.

we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7

We are still having the same problem.

Thanks

On 03/21/2018 01:16 PM, chris wrote:

I wasnt able to find what version we had installed so we went ahead and
reinstalled it

Maybe I am missing something, but if you could not find the version you
where using how do you know installing a new driver actually changed the
version you are using now?

we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7

We are still having the same problem.

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

I did the re install not to change versions but to now know what version
I am running

On 03/21/2018 01:56 PM, chris wrote:

I did the re install not to change versions but to now know what version
I am running

My previous question was not as clear as should have been.
So:
1) At some place in your software stack there is some sort of
configuration that links your app via JDBC to a Postgres JDBC driver. If
you know where that configuration is you should be able to find the
driver and presumably the version.
2) So when you say you did a reinstall do you mean you are now pointing
the configuration at postgresql-42.2.1.jre7.jar? FYI
postgresql-42.2.2.jre7.jar is actually the latest:
https://jdbc.postgresql.org/download.html

On 03/21/2018 02:44 PM, Adrian Klaver wrote:

On 03/21/2018 01:16 PM, chris wrote:

I wasnt able to find what version we had installed so we went ahead
and reinstalled it

Maybe I am missing something, but if you could not find the version
you where using how do you know installing a new driver actually
changed the version you are using now?

we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7

We are still having the same problem.

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

Chris,

At this point I'd write small piece of code to test if the url and failover
is working correctly. Sounds like you have a considerably sized stack
making it difficult to debug.
With a small piece of code it should become clear as to how things work or
don't as the case may be

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On 21 March 2018 at 17:13, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

I am surprised this was not answered correct yet.

I am in the same bucket as you and here is the problem. A quick look at the
code and you find the answer.

The BaseDataSource class (from which PGSimpleDataSource inherits) does NOT
support failover addresses. The underlying driver used to parse the URL does
it correctly: it returns url1,url2,url3 for URL and port1,port2,port3 for
ports. However, the datasource interprets this as a single address so it
tries to convert the "port1,port2,port3" string to an Integer....and it
crashes.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html