Обсуждение: problem connecting from JDBC: not over
Hello,
I read all the messages of the thread start by Ravi Kanth and sought many others in group list and over the Internet.
Yet I am unable to solve the problem despite all the efforts put into.
The problem addressed here is the connection of client application started from a host different from that of PostGre.
That is, I can run perfectly my sample program (TestServer.java) in the same machine where PostGreSQL
resides (10.10.5.41) but not in elsewhere (from 10.10.6.11 a windows client and 10.10.1.61 a linux client)
I am providing all the details that might help your diagnostic of this case.
However, I do not want to overwhelm you with so much data. They are useful indeed.
At least this proves that I tried very hard before asking for help.
The Linux distribution is RedHat 9, and PostGreSQL 7.3.4, and PostGIS 0.7.5
The pg_hba.conf has the following lines
local all all trust
host all all 127.0.0.1 255.255.255.255 trust
host all all 10.10.5.41 255.255.255.255 trust
host all all 10.10.6.11 255.255.255.255 trust
host all all 10.10.1.61 255.255.255.255 trust
I tried postgresql.conf with
tcpip_socket = 1
or
tcpip_socket = true
or
tcpip_socket = on
and port = 5432
The postmaster command line where issued as follows
/usr/local/pgsql/bin/postmaster -i -S -D /usr/local/pgsql/data -p 5432 &
I can enter in a database session with:
psql -p 5432 test
The netstat -an | grep 10.10. produces the relevant line
tcp 0 0 10.10.5.41:22 10.10.6.11:2631 ESTABLISHED
tcp 0 0 10.10.5.41:22 10.10.5.168:2382 ESTABLISHED
tcp 0 0 10.10.5.41:22 10.10.6.11:2770 ESTABLISHED
The compilation realized with the following command:
C:\work\postgis-0.7.5\jdbc>javac -classpath .;C:\work\postgis-0.7.5\jdbc\lib\pg7
2jdbc2.jar;C:\work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer.java
The execution of the test:
C:\work\postgis-0.7.5\jdbc>java -cp .;C:\work\postgis-0.7.5\jdbc\postgis.jar;C:\
work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer
The java version is j2sdk1.4.1_05.
===================================================================
The error message from 6.11 starts here: ==========================
C:\work\postgis-0.7.5\jdbc>java -cp .;C:\work\postgis-0.7.5\jdbc\postgis.jar;C
work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer
entrando: dbhost=10.10.5.41
entrando: createSQL=create table jdbc_test (geom geometry, id int4)
Creating JDBC connection...
getConnection: url=jdbc:postgresql://10.10.5.41/test
The connection attempt failed because Exception: java.net.SocketException: Con
ction reset by peer: connect
Stack Trace:
java.net.SocketException: Connection reset by peer: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:171)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:158)
at java.net.Socket.connect(Socket.java:434)
at java.net.Socket.connect(Socket.java:384)
at java.net.Socket.<init>(Socket.java:291)
at java.net.Socket.<init>(Socket.java:119)
at org.postgresql.PG_Stream.<init>(PG_Stream.java:38)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(Abstrac
dbc1Connection.java:160)
at org.postgresql.Driver.connect(Driver.java:122)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at examples.TestServer.main(TestServer.java:47)
End of Stack Trace
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(Abstrac
dbc1Connection.java:171)
at org.postgresql.Driver.connect(Driver.java:122)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at examples.TestServer.main(TestServer.java:47)
The error message from 6.11 ends here: ============================
===================================================================
///////////////////////////////////////////////////
The source code starts here: //////////////////////
package examples;
import java.sql.*;
import java.util.*;
import java.lang.*;
import org.postgis.*;
public class TestServer
{
public static void main(String[] args)
{
Connection conn;
String dbname = "test";
String dbuser = "postgres";
String dbpass = "";
String dbhost = "10.10.5.41";
String dbport = "5432";
String dbtable = "jdbc_test";
String dropSQL = "drop table " + dbtable;
String createSQL = "create table " + dbtable + " (geom geometry, id int4)";
String insertPointSQL = "insert into " + dbtable + " values ('POINT (10 10 10)',1)";
String insertPolygonSQL = "insert into " + dbtable + " values ('POLYGON ((0 0 0,0 10 0,10 10 0,10 0 0,0 0
0))',2)";
try {
System.out.println("entrando: dbhost="+dbhost);
System.out.println("entrando: createSQL="+createSQL);
System.out.println("Creating JDBC connection...");
Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://" + dbhost + ":" + dbport + "/" + dbname;
System.out.println("getConnection: url="+url);
conn = DriverManager.getConnection(url, dbuser, dbpass);
System.out.println("Adding geometric type entries...");
((org.postgresql.Connection)conn).addDataType("geometry","org.postgis.PGgeometry");
((org.postgresql.Connection)conn).addDataType("box3d","org.postgis.PGbox3d");
Statement s = conn.createStatement();
System.out.println("Creating table with geometric types...");
//table might not yet exist
System.out.println("Dropping table...");
s.execute(createSQL);
System.out.println("Inserting point...");
s.execute("create table "+dbtable+" (id int4)");
s.close();
conn.close();
}
catch( Exception e ) {
e.printStackTrace();
}
}
}
The source code ends here: //////////////////////
///////////////////////////////////////////////////
Finally I ask: is it an issue of granting access to machines and/or users?
What else I should double check either in the files, postmaster flags, or with linux command line such as netstat?
Is it an issue of network configuration? I can make ftp from point to point.
When changing the port in the postmaster -p and I change the corresponding port in the client program running in the
samehost it works properly as expected. If the port numbers do not have correspondence obvious it does not work.
I dearly thank you for your help and patience.
Carlos
-----Mensagem original-----
De: Ravi Kanth [mailto:ravi_kanth@msn.com]
Enviada em: segunda-feira, 10 de novembro de 2003 13:44
Para: pgsql-jdbc@postgresql.org
Assunto: [JDBC] problem connecting from JDBC
Hi,
I am trying to access postgresql database (7.3.2 - that came by default with
RedHat Linux 9 distro) using JDBC. I have a Linux user called "oracle" that
I have created in postgresql also and gave that user privileges to create a
database.
I tried to create a databse using createdb and it works.
The url that I am using in JDBC is: "jdbc:postgresql://localhost:5432/test",
"oracle", "". It reverts bck with error that says:
SQLException: Connection refused. Check that the hostname and port are
correct and that the postmaster is accepting TCP/IP connections.
I am using JDK 1.4.2 and NetBeans IDE.
Can anyone plz help? Thanks.
Ravi.
_________________________________________________________________
Compare high-speed Internet plans, starting at $26.95.
https://broadband.msn.com (Prices may vary by service area.)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On 18/11/2003 21:01 Carlos Ho Shih Ning wrote: > [snip] > The Linux distribution is RedHat 9, and PostGreSQL 7.3.4, and PostGIS > 0.7.5 > [snip] > The netstat -an | grep 10.10. produces the relevant line > > tcp 0 0 10.10.5.41:22 10.10.6.11:2631 > ESTABLISHED > tcp 0 0 10.10.5.41:22 10.10.5.168:2382 > ESTABLISHED > tcp 0 0 10.10.5.41:22 10.10.6.11:2770 > ESTABLISHED Those look like ssh connections to me (port 22). Try grepping for 5432. If the tcpip listener is active you should see a line something like tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN Also check that port 5432 is not blocked by a firewall. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Do you have a firewall between you and the postgres machine?
Dave
On Tue, 2003-11-18 at 16:01, Carlos Ho Shih Ning wrote:
> Hello,
>
> I read all the messages of the thread start by Ravi Kanth and sought many others in group list and over the Internet.
> Yet I am unable to solve the problem despite all the efforts put into.
>
> The problem addressed here is the connection of client application started from a host different from that of
PostGre.
> That is, I can run perfectly my sample program (TestServer.java) in the same machine where PostGreSQL
> resides (10.10.5.41) but not in elsewhere (from 10.10.6.11 a windows client and 10.10.1.61 a linux client)
>
> I am providing all the details that might help your diagnostic of this case.
> However, I do not want to overwhelm you with so much data. They are useful indeed.
> At least this proves that I tried very hard before asking for help.
>
> The Linux distribution is RedHat 9, and PostGreSQL 7.3.4, and PostGIS 0.7.5
> The pg_hba.conf has the following lines
> local all all trust
> host all all 127.0.0.1 255.255.255.255 trust
> host all all 10.10.5.41 255.255.255.255 trust
> host all all 10.10.6.11 255.255.255.255 trust
> host all all 10.10.1.61 255.255.255.255 trust
>
> I tried postgresql.conf with
> tcpip_socket = 1
> or
> tcpip_socket = true
> or
> tcpip_socket = on
>
> and port = 5432
>
> The postmaster command line where issued as follows
> /usr/local/pgsql/bin/postmaster -i -S -D /usr/local/pgsql/data -p 5432 &
>
> I can enter in a database session with:
> psql -p 5432 test
>
> The netstat -an | grep 10.10. produces the relevant line
>
> tcp 0 0 10.10.5.41:22 10.10.6.11:2631 ESTABLISHED
> tcp 0 0 10.10.5.41:22 10.10.5.168:2382 ESTABLISHED
> tcp 0 0 10.10.5.41:22 10.10.6.11:2770 ESTABLISHED
>
> The compilation realized with the following command:
> C:\work\postgis-0.7.5\jdbc>javac -classpath .;C:\work\postgis-0.7.5\jdbc\lib\pg7
> 2jdbc2.jar;C:\work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer.java
>
> The execution of the test:
> C:\work\postgis-0.7.5\jdbc>java -cp .;C:\work\postgis-0.7.5\jdbc\postgis.jar;C:\
> work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer
>
> The java version is j2sdk1.4.1_05.
>
> ===================================================================
> The error message from 6.11 starts here: ==========================
>
> C:\work\postgis-0.7.5\jdbc>java -cp .;C:\work\postgis-0.7.5\jdbc\postgis.jar;C
> work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer
> entrando: dbhost=10.10.5.41
> entrando: createSQL=create table jdbc_test (geom geometry, id int4)
> Creating JDBC connection...
> getConnection: url=jdbc:postgresql://10.10.5.41/test
> The connection attempt failed because Exception: java.net.SocketException: Con
> ction reset by peer: connect
> Stack Trace:
>
> java.net.SocketException: Connection reset by peer: connect
> at java.net.PlainSocketImpl.socketConnect(Native Method)
> at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
> at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:171)
> at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:158)
> at java.net.Socket.connect(Socket.java:434)
> at java.net.Socket.connect(Socket.java:384)
> at java.net.Socket.<init>(Socket.java:291)
> at java.net.Socket.<init>(Socket.java:119)
> at org.postgresql.PG_Stream.<init>(PG_Stream.java:38)
> at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(Abstrac
> dbc1Connection.java:160)
> at org.postgresql.Driver.connect(Driver.java:122)
> at java.sql.DriverManager.getConnection(DriverManager.java:512)
> at java.sql.DriverManager.getConnection(DriverManager.java:171)
> at examples.TestServer.main(TestServer.java:47)
> End of Stack Trace
>
> at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(Abstrac
> dbc1Connection.java:171)
> at org.postgresql.Driver.connect(Driver.java:122)
> at java.sql.DriverManager.getConnection(DriverManager.java:512)
> at java.sql.DriverManager.getConnection(DriverManager.java:171)
> at examples.TestServer.main(TestServer.java:47)
>
> The error message from 6.11 ends here: ============================
> ===================================================================
>
> ///////////////////////////////////////////////////
> The source code starts here: //////////////////////
>
> package examples;
>
> import java.sql.*;
> import java.util.*;
> import java.lang.*;
> import org.postgis.*;
>
> public class TestServer
> {
>
> public static void main(String[] args)
> {
> Connection conn;
>
> String dbname = "test";
> String dbuser = "postgres";
> String dbpass = "";
> String dbhost = "10.10.5.41";
> String dbport = "5432";
> String dbtable = "jdbc_test";
>
> String dropSQL = "drop table " + dbtable;
> String createSQL = "create table " + dbtable + " (geom geometry, id int4)";
> String insertPointSQL = "insert into " + dbtable + " values ('POINT (10 10 10)',1)";
> String insertPolygonSQL = "insert into " + dbtable + " values ('POLYGON ((0 0 0,0 10 0,10 10 0,10 0 0,0 0
0))',2)";
>
> try {
> System.out.println("entrando: dbhost="+dbhost);
> System.out.println("entrando: createSQL="+createSQL);
>
> System.out.println("Creating JDBC connection...");
> Class.forName("org.postgresql.Driver").newInstance();
> String url = "jdbc:postgresql://" + dbhost + ":" + dbport + "/" + dbname;
> System.out.println("getConnection: url="+url);
> conn = DriverManager.getConnection(url, dbuser, dbpass);
> System.out.println("Adding geometric type entries...");
> ((org.postgresql.Connection)conn).addDataType("geometry","org.postgis.PGgeometry");
> ((org.postgresql.Connection)conn).addDataType("box3d","org.postgis.PGbox3d");
> Statement s = conn.createStatement();
> System.out.println("Creating table with geometric types...");
> //table might not yet exist
>
> System.out.println("Dropping table...");
>
> s.execute(createSQL);
> System.out.println("Inserting point...");
> s.execute("create table "+dbtable+" (id int4)");
> s.close();
> conn.close();
> }
> catch( Exception e ) {
> e.printStackTrace();
> }
> }
> }
>
> The source code ends here: //////////////////////
> ///////////////////////////////////////////////////
>
>
> Finally I ask: is it an issue of granting access to machines and/or users?
> What else I should double check either in the files, postmaster flags, or with linux command line such as netstat?
> Is it an issue of network configuration? I can make ftp from point to point.
>
> When changing the port in the postmaster -p and I change the corresponding port in the client program running in the
samehost it works properly as expected. If the port numbers do not have correspondence obvious it does not work.
>
> I dearly thank you for your help and patience.
> Carlos
>
>
>
>
> -----Mensagem original-----
> De: Ravi Kanth [mailto:ravi_kanth@msn.com]
> Enviada em: segunda-feira, 10 de novembro de 2003 13:44
> Para: pgsql-jdbc@postgresql.org
> Assunto: [JDBC] problem connecting from JDBC
>
>
> Hi,
> I am trying to access postgresql database (7.3.2 - that came by default with
> RedHat Linux 9 distro) using JDBC. I have a Linux user called "oracle" that
> I have created in postgresql also and gave that user privileges to create a
> database.
> I tried to create a databse using createdb and it works.
> The url that I am using in JDBC is: "jdbc:postgresql://localhost:5432/test",
> "oracle", "". It reverts bck with error that says:
> SQLException: Connection refused. Check that the hostname and port are
> correct and that the postmaster is accepting TCP/IP connections.
> I am using JDK 1.4.2 and NetBeans IDE.
> Can anyone plz help? Thanks.
>
> Ravi.
>
> _________________________________________________________________
> Compare high-speed Internet plans, starting at $26.95.
> https://broadband.msn.com (Prices may vary by service area.)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>