SEO

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.
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) -
postgresql-libs-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;

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,st DBType,stUser,stPassword);
}
catch(Exception e)
{

Logger.toLog("CONNECTION",stDataBaseName,"CDBConne ctionPool.getConnection:Except
ion="+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 = "";

if (stDBType.equals("ORACLE"))
{
stURL = "jdbcracle:thin:@"+ stIPAddress + ":" + stPort + ":" +
stDataBaseName ;
stClassName = "oracle.jdbc.driver.OracleDriver";
}
else if(stDBType.equals("POSTGRES"))
{
stURL = "jdbcostgresql://"+stIPAddress+":"+stPort+"/"+stDataBaseName;
stClassName = "org.postgresql.Driver";
}
else if(stDBType.equals("MS-SQL2000"))
{
stURL =
"jdbc:microsoft:sqlserver://"+stIPAddress+":"+stPort+";DatabaseName="+stDataBa se
Name;
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,stPasswor d);
}
catch(SQLException sqle)
{
if(sqle.toString().indexOf("too many clients")>=0)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConne ctionPool.newConnection()
: Maxmimum connections Reached. Waiting for connections to freeup.");
con =
newConnection(stIPAddress,stPort,stDataBaseName,st DBType,stUser,stPassword);
}
else
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConne ctionPool.newConnection()
: SQLException= " + sqle);
}
}
catch(ClassNotFoundException e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConne ctionPool.newConnection() :
ClassNotFoundException = " + e.getCause());
}
catch(Exception e)
{
Logger.toLog("CONNECTION",stDataBaseName,"CDBConne ctionPool.newConnection() :
Exception= " + e);
}
return con;
}

public void freeConnection(Connection con,String stDataBaseName)
{
try
{
con.close();

}catch(Exception
e){Logger.toLog("CONNECTION",stDataBaseName,"CDBCo nnectionPool.freeConnection :
Exception= " + e);}
}
}

"Srinivas Kotapally" <ksrinivas@coriendo.com> writes:
> 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!

"Connection refused" means it was refused at the kernel level; if the
postmaster had rejected the connection the message would be different.
So you need to be looking for OS-level issues. Given that you seem to
be running a rather hoary kernel (2.6.9 is a ways back no?), maybe an
update there is indicated. Firewall problems are a possibility too.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Hi Tom

I installed CentOS 4.4 final late last year. The kernel version was the latest
about a year back. I have been running yum updates and applying patches
regularly. Can you give me any ideas on how to troubleshoot (and establish that
this issue is due to a kernel bug) the kernel packet drops? I am beginning to
think it is a firewall related issue. But I have been unable to replicate the
issue!

I have been monitoring the firewall logs (could not find much there). Maybe I
will also sifting through the messages log on the servers.

Will post if I find anything!

Thanks

Srini

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 01, 2007 6:52 PM
To: ksrinivas@coriendo.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Intermittent Postgres Connection refused Errors (JDBC)

"Srinivas Kotapally" <ksrinivas@coriendo.com> writes:
> 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!

"Connection refused" means it was refused at the kernel level; if the postmaster
had rejected the connection the message would be different.
So you need to be looking for OS-level issues. Given that you seem to be
running a rather hoary kernel (2.6.9 is a ways back no?), maybe an update there
is indicated. Firewall problems are a possibility too.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hi Tom

I installed CentOS 4.4 final late last year. The kernel version was the latest
about a year back. I have been running yum updates and applying patches
regularly. Can you give me any ideas on how to troubleshoot (and establish that
this issue is due to a kernel bug) the kernel packet drops? I am beginning to
think it is a firewall related issue. But I have been unable to replicate the
issue!

I have been monitoring the firewall logs (could not find much there). Maybe I
will also sifting through the messages log on the servers.

Will post if I find anything!

Thanks

Srini

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 01, 2007 6:52 PM
To: ksrinivas@coriendo.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Intermittent Postgres Connection refused Errors (JDBC)

"Srinivas Kotapally" <ksrinivas@coriendo.com> writes:
> 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!

"Connection refused" means it was refused at the kernel level; if the postmaster
had rejected the connection the message would be different.
So you need to be looking for OS-level issues. Given that you seem to be
running a rather hoary kernel (2.6.9 is a ways back no?), maybe an update there
is indicated. Firewall problems are a possibility too.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings