java jdbc 链接pg_jdbc连接pg数据库timeout

最新推荐文章于 2025-05-02 15:46:52 发布

时维教育顾老师

最新推荐文章于 2025-05-02 15:46:52 发布

阅读量1.9k

点赞数

文章标签： java jdbc 链接pg

本文链接：https://blog.youkuaiyun.com/weixin_26949673/article/details/112027989

版权

本文讨论了当使用Java JDBC连接到PostgreSQL数据库时遇到的连接超时异常。异常信息显示数据库不可用时启动应用会报错。问题涉及到Tomcat连接池、Hibernate和Spring框架。解决方案可能包括检查数据库状态、配置连接超时参数（如connectTimeout和socketTimeout）以及调整数据库连接池设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

异常

数据库挂的时候启动报错(The connection attempt failed)

org.postgresql.util.PSQLException: 尝试连线已失败。

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:233) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:144) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:29) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:21) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:31) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:24) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.Driver.makeConnection(Driver.java:410) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.Driver.connect(Driver.java:280) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) ~[tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) ~[tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718) [tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:650) [tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468) [tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.ConnectionPool.(ConnectionPool.java:143) [tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118) [tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107) [tomcat-jdbc-8.5.6.jar:na]

at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131) [tomcat-jdbc-8.5.6.jar:na]

at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:254) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:228) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:207) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:207) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:847) [hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]

at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:874) [hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]

at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1648) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1585) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:554) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]

at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]

at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]

at com.xixicat.DatasourcePgTimeoutDemoApplication.main(DatasourcePgTimeoutDemoApplication.java:10) [classes/:na]

Caused by: java.net.SocketException: Operation timed out

at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_71]

at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[na:1.8.0_71]

at java.net.SocketInputStream.read(SocketInputStream.java:170) ~[na:1.8.0_71]

at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[na:1.8.0_71]

at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:282) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:386) ~[postgresql-9.3-1102-jdbc4.jar:na]

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:181) ~[postgresql-9.3-1102-jdbc4.jar:na]

... 54 common frames omitted

SocketInputStream#socketRead0

/Library/Java/JavaVirtualMachines/jdk1.8.0_71.jdk/Contents/Home/src.zip!/java/net/SocketInputStream.java

/**

* Reads into an array of bytes at the specified offset using

* the received socket primitive.

* @param fd the FileDescriptor

* @param b the buffer into which the data is read

* @param off the start offset of the data

* @param len the maximum number of bytes read

* @param timeout the read timeout in ms

* @return the actual number of bytes read, -1 is

* returned when the end of the stream is reached.

* @exception IOException If an I/O error has occurred.

private native int socketRead0(FileDescriptor fd,

byte b[], int off, int len,

int timeout)

throws IOException;

soTimeout

socket timeout没有设置的话，为0，理论上是无限阻塞等待，但是实际是由java native根据操作系统的实现来决定的。

To summarize, timeout for blocking sockets is implemented by calling poll on Linux (and select on Windows) to determine that data is available before calling recv. However, at least on Linux, both methods can spuriously indicate that data is available when it is not, leading to recv blocking indefinitely.

正确设置pg的timeout

背景信息

org.postgresql

postgresql

9.3-1102-jdbc4

ConnectionFactoryImpl

/Users/xixicat/.m2/repository/org/postgresql/postgresql/9.3-1102-jdbc4/postgresql-9.3-1102-jdbc4-sources.jar!/org/postgresql/core/v3/ConnectionFactoryImpl.java

public ProtocolConnection openConnectionImpl(HostSpec[] hostSpecs, String user, String database, Properties info, Logger logger) throws SQLException {

// Extract interesting values from the info properties:

// - the SSL setting

boolean requireSSL;

boolean trySSL;

String sslmode = info.getProperty("sslmode");

if (sslmode==null)

{ //Fall back to the ssl property

requireSSL = trySSL = (info.getProperty("ssl") != null);

} else {

if ("disable".equals(sslmode))

{

requireSSL = trySSL = false;

}

//allow and prefer are not handled yet

/*else if ("allow".equals(sslmode) || "prefer".equals(sslmode))

{

//XXX Allow and prefer are treated the same way

requireSSL = false;

trySSL = true;

}*/

else if ("require".equals(sslmode) || "verify-ca".equals(sslmode) || "verify-full".equals(sslmode))

{

requireSSL = trySSL = true;

} else {

throw new PSQLException (GT.tr("Invalid sslmode value: {0}", sslmode), PSQLState.CONNECTION_UNABLE_TO_CONNECT);

}

// - the TCP keep alive setting

boolean requireTCPKeepAlive = (Boolean.valueOf(info.getProperty("tcpKeepAlive")).booleanValue());

// NOTE: To simplify this code, it is assumed that if we are

// using the V3 protocol, then the database is at least 7.4. That

// eliminates the need to check database versions and maintain

// backward-compatible code here.

// Change by Chris Smith

for (int whichHost = 0; whichHost < hostSpecs.length; ++whichHost) {

HostSpec hostSpec = hostSpecs[whichHost];

if (logger.logDebug())

logger.debug("Trying to establish a protocol version 3 connection to " + hostSpec);

// Establish a connection.

int connectTimeout = 0;

String connectTimeoutProperty = info.getProperty("connectTimeout", "0");

try {

connectTimeout = Integer.parseInt(connectTimeoutProperty) * 1000;

} catch (NumberFormatException nfe) {

logger.info("Couldn't parse connectTimeout value:" + connectTimeoutProperty);

}

PGStream newStream = null;

try

{

newStream = new PGStream(hostSpec, connectTimeout);

// Construct and send an ssl startup packet if requested.

if (trySSL)

newStream = enableSSL(newStream, requireSSL, info, logger, connectTimeout);

// Set the socket timeout if the "socketTimeout" property has been set.

String socketTimeoutProperty = info.getProperty("socketTimeout", "0");

try {

int socketTimeout = Integer.parseInt(socketTimeoutProperty);

if (socketTimeout > 0) {

newStream.getSocket().setSoTimeout(socketTimeout*1000);

}

} catch (NumberFormatException nfe) {

logger.info("Couldn't parse socketTimeout value:" + socketTimeoutProperty);

}

// Enable TCP keep-alive probe if required.

newStream.getSocket().setKeepAlive(requireTCPKeepAlive);

// Try to set SO_SNDBUF and SO_RECVBUF socket options, if requested.

// If receiveBufferSize and send_buffer_size are set to a value greater

// than 0, adjust. -1 means use the system default, 0 is ignored since not

// supported.

// Set SO_RECVBUF read buffer size

String receiveBufferSizeProperty = info.getProperty("receiveBufferSize", "-1");

try {

int receiveBufferSize = Integer.parseInt(receiveBufferSizeProperty);

if (receiveBufferSize > -1) {

// value of 0 not a valid buffer size value

if (receiveBufferSize > 0) {

newStream.getSocket().setReceiveBufferSize(receiveBufferSize);

} else {

logger.info("Ignore invalid value for receiveBufferSize: " + receiveBufferSize);

}

} catch (NumberFormatException nfe) {

logger.info("Couldn't parse receiveBufferSize value: " + receiveBufferSizeProperty);

}

// Set SO_SNDBUF write buffer size

String sendBufferSizeProperty = info.getProperty("sendBufferSize", "-1");

try {

int sendBufferSize = Integer.parseInt(sendBufferSizeProperty);

if (sendBufferSize > -1) {

if (sendBufferSize > 0) {

newStream.getSocket().setSendBufferSize(sendBufferSize);

} else {

logger.info("Ignore invalid value for sendBufferSize: " + sendBufferSize);

}

} catch (NumberFormatException nfe) {

logger.info("Couldn't parse sendBufferSize value: " + sendBufferSizeProperty);

}

logger.info("Receive Buffer Size is " + newStream.getSocket().getReceiveBufferSize());

logger.info("Send Buffer Size is " + newStream.getSocket().getSendBufferSize());

// Construct and send a startup packet.

String[][] params = {

{ "user", user },

{ "database", database },

{ "client_encoding", "UTF8" },

{ "DateStyle", "ISO" },

{ "extra_float_digits", "2" },

{ "TimeZone", createPostgresTimeZone() },

};

sendStartupPacket(newStream, params, logger);

// Do authentication (until AuthenticationOk).

doAuthentication(newStream, hostSpec.getHost(), user, info, logger);

// Do final startup.

ProtocolConnectionImpl protoConnection = new ProtocolConnectionImpl(newStream, user, database, info, logger, connectTimeout);

readStartupMessages(newStream, protoConnection, logger);

runInitialQueries(protoConnection, info, logger);

// And we're done.

return protoConnection;

}

catch (UnsupportedProtocolException upe)

{

// Swallow this and return null so ConnectionFactory tries the next protocol.

if (logger.logDebug())

logger.debug("Protocol not supported, abandoning connection.");

try

{

newStream.close();

}

catch (IOException e)

{

}

return null;

}

catch (ConnectException cex)

{

// Added by Peter Mount

// ConnectException is thrown when the connection cannot be made.

// we trap this an return a more meaningful message for the end user

if (whichHost + 1 < hostSpecs.length) {

// still more addresses to try

continue;

}

throw new PSQLException (GT.tr("Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections."), PSQLState.CONNECTION_UNABLE_TO_CONNECT, cex);

}

catch (IOException ioe)

{

if (newStream != null)

{

try

{

newStream.close();

}

catch (IOException e)

{

}