Spring Boot应用通过JDBC连接Azure PostgreSQL失败求助

配置信息 #

• application-uat.yml配置：

spring:
  datasource:
    username: "${DATABASE_USER}"
    password: "${DATABASE_PASS}"
    platform: postgres
    initialization-mode: always

• application.yml配置：

spring.profiles.active: uat
spring:
    datasource:
        url: jdbc:postgresql://test-invoicing-db.postgres.database.azure.com:5432/postgres?sslmode=require&sslfactory=org.postgresql.ssl.NonValidatingFactory
server:

报错信息 #

Caused by: org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.postgresql.util.PSQLException: The connection attempt failed.
        at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:58) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:210) ~[spring-boot-autoconfigure-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.createSchema(DataSourceInitializer.java:104) ~[spring-boot-autoconfigure-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.afterPropertiesSet(DataSourceInitializerInvoker.java:64) ~[spring-boot-autoconfigure-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1821) ~[spring-beans-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1758) ~[spring-beans-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        ... 162 common frames omitted
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.postgresql.util.PSQLException: The connection attempt failed.
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:46) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        ... 167 common frames omitted
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:292) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.Driver.makeConnection(Driver.java:454) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.Driver.connect(Driver.java:256) ~[postgresql-42.2.5.jar!/:42.2.5]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136) ~[HikariCP-3.2.0.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369) ~[HikariCP-3.2.0.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198) ~[HikariCP-3.2.0.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467) ~[HikariCP-3.2.0.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541) ~[HikariCP-3.2.0.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-3.2.0.jar!/:na]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-3.2.0.jar!/:na]
        at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:157) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78) ~[spring-jdbc-5.1.5.RELEASE.jar!/:5.1.5.RELEASE]
        ... 168 common frames omitted
Caused by: java.net.UnknownHostException: test-invoicing-db.postgres.database.azure.com
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:184) ~[na:1.8.0_342]
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[na:1.8.0_342]
        at java.net.Socket.connect(Socket.java:607) ~[na:1.8.0_342]
        at org.postgresql.core.PGStream.<init>(PGStream.java:70) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192) ~[postgresql-42.2.5.jar!/:42.2.5]
        ... 182 common frames omitted

问题描述 #

应用之前运行正常，未做任何修改突然无法连接。已尝试添加/移除@hostname，依然报错。本地可正常连接该数据库，本地JDBC连接也没问题，但服务器及其他部署环境的应用均出现相同连接失败问题。

解决方案 #

从报错根因java.net.UnknownHostException可以看出，核心问题是服务器环境无法解析Azure PostgreSQL的主机名，按以下步骤排查：

1. 检查服务器DNS配置

   • 在服务器上执行nslookup test-invoicing-db.postgres.database.azure.com或ping test-invoicing-db.postgres.database.azure.com，确认是否能解析出IP地址。如果解析失败，说明服务器DNS设置异常，需调整DNS服务器地址（比如更换为公共DNS如8.8.8.8、1.1.1.1）。

2. 检查网络防火墙/安全组

   • 确认服务器所在网络的防火墙、安全组是否允许出站访问5432端口（PostgreSQL默认端口）。另外，Azure PostgreSQL的防火墙规则是否允许服务器的公网IP访问——如果之前正常，可能是服务器IP发生了变化，或者Azure侧防火墙规则被修改。

3. 验证JDBC URL完整性

   • 原配置中的URL存在截断（NonValidat$），需确认完整的URL是否正确，正确的SSL工厂参数应为org.postgresql.ssl.NonValidatingFactory，补全后重新测试。

4. 检查Azure PostgreSQL服务状态

   • 登录Azure控制台，查看该PostgreSQL实例的运行状态，是否有服务中断、维护等情况，确认服务是否正常可用。

5. 测试服务器网络连通性

   • 使用telnet test-invoicing-db.postgres.database.azure.com 5432或nc -zv test-invoicing-db.postgres.database.azure.com 5432测试端口连通性，确认是否能建立连接。

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