Обсуждение: Intermittent Postgres Connection refused Errors (JDBC)
Hi
I am managing an application that uses the Postgres backend. Of late we have started getting connection refused errors every now and then. We have not been able to identify the cause of this. Any hints or known bugs that I should be aware of? Thanks for any help extended!
Regards
Srinivas
Here is the environment I work in:
Application is a Java app residing on a web server and is running on Tomcat.
Application is a Java app residing on a web server and is running on Tomcat.
Versions:
jdk: 1.5.0_02
Tomcat 5.5.23
Web server: httpd-2.0.52-28.ent.centos4
Kernel: Linux version 2.6.9-42.0.10.ELsmp (mockbuild@builder5.centos.org) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-3)) #1 SMP Tue Feb 27 09:40:21 EST 2007
Postgres: 8.1.8 (64 bit version) -
Postgres: 8.1.8 (64 bit version) -
postgresql-libs-8.1.8-1PGDG
postgresql-8.1.8-1PGDG
postgresql-server-8.1.8-1PGDG
postgresql-contrib-8.1.8-1PGDG
postgresql-8.1.8-1PGDG
postgresql-server-8.1.8-1PGDG
postgresql-contrib-8.1.8-1PGDG
We use the JDBC2 driver that comes with Postgres.
The specific error we get is:
CDBConnectionPool.newConnection() : SQLException= org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
Our CDBConnectionPool class as below (this is a class that we have implemented to establish BD connections):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;
class CDBConnectionPool
{
public synchronized Connection getConnection(String stIPAddress,String stPort,String stDataBaseName, String stUser,String stPassword,String stDBType)
{
Connection con = null;
try
{
con = newConnection(stIPAddress,stPort,stDataBaseName,stDBType,stUser,stPassword);
}
catch(Exception e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.getConnection:Exception="+e);
}
return con;
}
{
public synchronized Connection getConnection(String stIPAddress,String stPort,String stDataBaseName, String stUser,String stPassword,String stDBType)
{
Connection con = null;
try
{
con = newConnection(stIPAddress,stPort,stDataBaseName,stDBType,stUser,stPassword);
}
catch(Exception e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.getConnection:Exception="+e);
}
return con;
}
private Connection newConnection(String stIPAddress,String stPort,String stDataBaseName,String stDBType,String stUser,String stPassword)
{
Connection con = null;
try
{
String stURL = "";
String stClassName = "";
{
Connection con = null;
try
{
String stURL = "";
String stClassName = "";
if (stDBType.equals("ORACLE"))
{
stURL = "jdbc:oracle:thin:@"+ stIPAddress + ":" + stPort + ":" + stDataBaseName ;
stClassName = "oracle.jdbc.driver.OracleDriver";
}
else if(stDBType.equals("POSTGRES"))
{
stURL = "jdbc:postgresql://"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "org.postgresql.Driver";
}
else if(stDBType.equals("MS-SQL2000"))
{
stURL = "jdbc:microsoft:sqlserver://"+stIPAddress+":"+stPort+";DatabaseName="+stDataBaseName;
stClassName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
}
else if(stDBType.equals("SYBASE"))
{
stURL = "jdbc:sybase:Tds:"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "com.sybase.jdbc2.jdbc.SybDriver";
}
else if(stDBType.equals("DB2"))
{
stURL = "jdbc:db2://"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "COM.ibm.db2.jdbc.net.DB2Driver";
}
{
stURL = "jdbc:oracle:thin:@"+ stIPAddress + ":" + stPort + ":" + stDataBaseName ;
stClassName = "oracle.jdbc.driver.OracleDriver";
}
else if(stDBType.equals("POSTGRES"))
{
stURL = "jdbc:postgresql://"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "org.postgresql.Driver";
}
else if(stDBType.equals("MS-SQL2000"))
{
stURL = "jdbc:microsoft:sqlserver://"+stIPAddress+":"+stPort+";DatabaseName="+stDataBaseName;
stClassName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
}
else if(stDBType.equals("SYBASE"))
{
stURL = "jdbc:sybase:Tds:"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "com.sybase.jdbc2.jdbc.SybDriver";
}
else if(stDBType.equals("DB2"))
{
stURL = "jdbc:db2://"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "COM.ibm.db2.jdbc.net.DB2Driver";
}
Class.forName(stClassName);
con = DriverManager.getConnection(stURL,stUser,stPassword);
}
catch(SQLException sqle)
{
if(sqle.toString().indexOf("too many clients")>=0)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : Maxmimum connections Reached. Waiting for connections to freeup.");
con = newConnection(stIPAddress,stPort,stDataBaseName,stDBType,stUser,stPassword);
}
else
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : SQLException= " + sqle);
}
}
catch(ClassNotFoundException e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : ClassNotFoundException = " + e.getCause());
}
catch(Exception e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : Exception= " + e);
}
return con;
}
}
catch(SQLException sqle)
{
if(sqle.toString().indexOf("too many clients")>=0)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : Maxmimum connections Reached. Waiting for connections to freeup.");
con = newConnection(stIPAddress,stPort,stDataBaseName,stDBType,stUser,stPassword);
}
else
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : SQLException= " + sqle);
}
}
catch(ClassNotFoundException e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : ClassNotFoundException = " + e.getCause());
}
catch(Exception e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.newConnection() : Exception= " + e);
}
return con;
}
public void freeConnection(Connection con,String stDataBaseName)
{
try
{
con.close();
{
try
{
con.close();
}catch(Exception e){Logger.toLog("CONNECTION",stDataBaseName,"CDBConnectionPool.freeConnection : Exception= " + e);}
}
}
}
}
On 5/1/07, Srinivas Kotapally <ksrinivas@bisil.com> wrote:
Are you exceeding the maximum allowed connections in PostgreSQL? I would start by checking the PostgreSQL logs. If nothing shows up, it may be a network or application configuration issue.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
HiI am managing an application that uses the Postgres backend. Of late we have started getting connection refused errors every now and then. We have not been able to identify the cause of this. Any hints or known bugs that I should be aware of? Thanks for any help extended!Regards
SrinivasHere is the environment I work in:
Application is a Java app residing on a web server and is running on Tomcat.Versions:jdk: 1.5.0_02Tomcat 5.5.23Web server: httpd-2.0.52-28.ent.centos4Kernel: Linux version 2.6.9-42.0.10.ELsmp (mockbuild@builder5.centos.org ) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-3)) #1 SMP Tue Feb 27 09:40:21 EST 2007
Postgres: 8.1.8 (64 bit version) -postgresql-libs-8.1.8-1PGDG
postgresql-8.1.8-1PGDG
postgresql-server-8.1.8-1PGDG
postgresql-contrib-8.1.8-1PGDGWe use the JDBC2 driver that comes with Postgres.The specific error we get is:CDBConnectionPool.newConnection() : SQLException= org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
Are you exceeding the maximum allowed connections in PostgreSQL? I would start by checking the PostgreSQL logs. If nothing shows up, it may be a network or application configuration issue.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
On 5/1/07, Srinivas Kotapally <ksrinivas@coriendo.com> wrote:
Are any errors showing up in the PostgreSQL logs?
Also, are do you have any of your already established connections in your pool being that are begin disconnected? If so, you may be having intermittent network problems.
The error you are getting implies that you are having connectivity problems between servers. I am tomcat and PostgreSQL are running on separate machines. Have you tried replicating this problem by running tomcat and PostgreSQL on the same machine and connecting to localhost?
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Thanks for the response. No, I am not exceeding the max limit. I tried a test which would exceed the max connections (which is 100 - set in postgresql.conf). The error at that point isorg.postgresql.util.PSQLException: FATAL: sorry, too many clients already
Are any errors showing up in the PostgreSQL logs?
Also, are do you have any of your already established connections in your pool being that are begin disconnected? If so, you may be having intermittent network problems.
The error you are getting implies that you are having connectivity problems between servers. I am tomcat and PostgreSQL are running on separate machines. Have you tried replicating this problem by running tomcat and PostgreSQL on the same machine and connecting to localhost?
From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent: Tuesday, May 01, 2007 5:59 PM
To: ksrinivas@coriendo.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Intermittent Postgres Connection refused Errors (JDBC)On 5/1/07, Srinivas Kotapally <ksrinivas@bisil.com> wrote:HiI am managing an application that uses the Postgres backend. Of late we have started getting connection refused errors every now and then. We have not been able to identify the cause of this. Any hints or known bugs that I should be aware of? Thanks for any help extended!Regards
SrinivasHere is the environment I work in:
Application is a Java app residing on a web server and is running on Tomcat.Versions:jdk: 1.5.0_02Tomcat 5.5.23Web server: httpd-2.0.52-28.ent.centos4Kernel: Linux version 2.6.9-42.0.10.ELsmp (mockbuild@builder5.centos.org ) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-3)) #1 SMP Tue Feb 27 09:40:21 EST 2007
Postgres: 8.1.8 (64 bit version) -postgresql-libs-8.1.8-1PGDG
postgresql-8.1.8-1PGDG
postgresql-server-8.1.8-1PGDG
postgresql-contrib-8.1.8-1PGDGWe use the JDBC2 driver that comes with Postgres.The specific error we get is:CDBConnectionPool.newConnection() : SQLException= org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
Are you exceeding the maximum allowed connections in PostgreSQL? I would start by checking the PostgreSQL logs. If nothing shows up, it may be a network or application configuration issue.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Thanks for the response. No, I am not exceeding the max limit. I tried a test which would exceed the max connections (which is 100 - set in postgresql.conf). The error at that point is
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent: Tuesday, May 01, 2007 5:59 PM
To: ksrinivas@coriendo.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Intermittent Postgres Connection refused Errors (JDBC)
HiI am managing an application that uses the Postgres backend. Of late we have started getting connection refused errors every now and then. We have not been able to identify the cause of this. Any hints or known bugs that I should be aware of? Thanks for any help extended!Regards
SrinivasHere is the environment I work in:
Application is a Java app residing on a web server and is running on Tomcat.Versions:jdk: 1.5.0_02Tomcat 5.5.23Web server: httpd-2.0.52-28.ent.centos4Kernel: Linux version 2.6.9-42.0.10.ELsmp (mockbuild@builder5.centos.org ) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-3)) #1 SMP Tue Feb 27 09:40:21 EST 2007
Postgres: 8.1.8 (64 bit version) -postgresql-libs-8.1.8-1PGDG
postgresql-8.1.8-1PGDG
postgresql-server-8.1.8-1PGDG
postgresql-contrib-8.1.8-1PGDGWe use the JDBC2 driver that comes with Postgres.The specific error we get is:CDBConnectionPool.newConnection() : SQLException= org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
Are you exceeding the maximum allowed connections in PostgreSQL? I would start by checking the PostgreSQL logs. If nothing shows up, it may be a network or application configuration issue.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================