云环境应用遇PostgreSQL CST时区无效及Hikari连接池超时问题

应用在云环境运行时触发以下错误，本地环境无此问题：

Caused by: java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30000ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:695)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:108)
    ... 146 more
Caused by: org.postgresql.util.PSQLException: FATAL: invalid value for parameter "TimeZone": "CST"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2825)
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:175)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:313)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:263)
    at org.postgresql.Driver.makeConnection(Driver.java:443)
    at org.postgresql.Driver.connect(Driver.java:297)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477)
    at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:725)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:711)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    ... 1 more

已尝试的无效操作 #

• 设置JDBC连接串为jdbc:postgres://localhost:3306/test?serverTimezone=UTC
• 通过SHOW TIMEZONE确认PostgreSQL当前时区为UTC
• 执行SET TIMEZONE 'UTC'手动设置会话时区
• 查阅PostgreSQL官方文档，未找到有效解决路径

针对性解决方法 #

1. 修正JDBC连接串参数 #

PostgreSQL JDBC驱动不支持serverTimezone参数（这是MySQL驱动的专属参数），正确的时区参数是timeZone；同时注意PostgreSQL默认端口为5432（你之前写的3306是MySQL端口，这可能是额外问题），修正后的连接串：

jdbc:postgresql://localhost:5432/test?timeZone=UTC

2. 强制JVM使用UTC时区 #

云环境JVM可能默认时区为CST，导致驱动自动将该时区传递给PostgreSQL。可以通过两种方式设置：

• JVM启动参数：添加-Duser.timezone=UTC
• 代码内设置：在应用初始化阶段执行

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

3. 调整云服务器系统时区 #

部分云服务器默认时区为CST，驱动可能读取系统时区作为连接参数。登录服务器执行以下命令修改：

# 查看当前时区
timedatectl
# 设置为UTC时区
timedatectl set-timezone UTC

4. 配置PostgreSQL全局时区 #

修改PostgreSQL配置文件postgresql.conf，设置全局时区为UTC：

timezone = 'UTC'
log_timezone = 'UTC'

修改完成后重启PostgreSQL服务生效。

内容的提问来源于stack exchange，提问作者SarangRN