spring-boot - Google App Engine 中的 Spring Boot 应用程序无法连接到 Cloud SQL

Question

我不确定我还遗漏了什么，但我的应用程序一开始就失败了:

Message    : The connection attempt failed.

    at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:60) ~[flyway-core-5.2.4.jar!/:na]
    at org.flywaydb.core.internal.database.DatabaseFactory.createDatabase(DatabaseFactory.java:72) ~[flyway-core-5.2.4.jar!/:na]
    at org.flywaydb.core.Flyway.execute(Flyway.java:1670) ~[flyway-core-5.2.4.jar!/:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1356) ~[flyway-core-5.2.4.jar!/:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:65) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar!/:2.1.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837) ~[spring-beans-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774) ~[spring-beans-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    ... 26 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.6.jar!/:42.2.6]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.Driver.makeConnection(Driver.java:458) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.Driver.connect(Driver.java:260) ~[postgresql-42.2.6.jar!/:42.2.6]
    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.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:56) ~[flyway-core-5.2.4.jar!/:na]
    ... 32 common frames omitted
Caused by: java.net.SocketTimeoutException: connect timed out
    at java.base/java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:na]
    at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399) ~[na:na]
    at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242) ~[na:na]
    at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224) ~[na:na]
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403) ~[na:na]
    at java.base/java.net.Socket.connect(Socket.java:591) ~[na:na]
    at org.postgresql.core.PGStream.<init>(PGStream.java:75) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192) ~[postgresql-42.2.6.jar!/:42.2.6]
    ... 44 common frames omitted    

我的app.yaml是

runtime: java11
manual_scaling:
  instances: 1
beta_settings:
  cloud_sql_instances: recruiters-wtf:europe-west2:recruiters-wtf=tcp:5432

虽然我的 application.properties 包含:

spring.datasource.url=jdbc:postgresql://172.17.0.1:5432/${DB_NAME:recruiters_wtf}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASS}

我还缺少什么？同一应用程序的 Kubernetes 部署已成功使用该数据库。

按照 kurtisvg 的回答，我将连接字符串更改为:

spring.datasource.url=jdbc:postgresql://google/recruiters_wtf?cloudSqlInstance=recruiters-wtf:europe-west2:recruiters-wtf=tcp:5432&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=${DB_USER}&password=${DB_PASS}

我已经安装了 postgres-socket-factory。将错误更改为:

Message    : The connection attempt failed.

    at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:60) ~[flyway-core-5.2.4.jar!/:na]
    at org.flywaydb.core.internal.database.DatabaseFactory.createDatabase(DatabaseFactory.java:72) ~[flyway-core-5.2.4.jar!/:na]
    at org.flywaydb.core.Flyway.execute(Flyway.java:1670) ~[flyway-core-5.2.4.jar!/:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1356) ~[flyway-core-5.2.4.jar!/:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:65) ~[spring-boot-autoconfigure-2.1.7.RELEASE.jar!/:2.1.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837) ~[spring-beans-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774) ~[spring-beans-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    ... 26 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.6.jar!/:42.2.6]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.Driver.makeConnection(Driver.java:458) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.Driver.connect(Driver.java:260) ~[postgresql-42.2.6.jar!/:42.2.6]
    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.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:56) ~[flyway-core-5.2.4.jar!/:na]
    ... 32 common frames omitted
Caused by: java.io.IOException: No such file or directory
    at jnr.unixsocket.UnixSocketChannel.doConnect(UnixSocketChannel.java:127) ~[jnr-unixsocket-0.23.jar!/:na]
    at jnr.unixsocket.UnixSocketChannel.connect(UnixSocketChannel.java:136) ~[jnr-unixsocket-0.23.jar!/:na]
    at jnr.unixsocket.UnixSocketChannel.open(UnixSocketChannel.java:68) ~[jnr-unixsocket-0.23.jar!/:na]
    at com.google.cloud.sql.core.CoreSocketFactory.connect(CoreSocketFactory.java:180) ~[jdbc-socket-factory-core-1.0.15.jar!/:na]
    at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:71) ~[postgres-socket-factory-1.0.15.jar!/:na]
    at org.postgresql.core.PGStream.<init>(PGStream.java:67) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91) ~[postgresql-42.2.6.jar!/:42.2.6]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192) ~[postgresql-42.2.6.jar!/:42.2.6]
    ... 44 common frames omitted

Answer 1

记录在案here :

App Engine standard enviroments do not support connecting to the Cloud SQL instance using TCP. Your code should not try to access the instance using an IP address (such as 127.0.0.1 or 172.17.0.1) unless you have configured Serverless VPC Access.

首先，您需要删除 app.yaml 文件中的 beta_settings 条目，因为:

• 它是为 App Engine Flexible 而设计的(实际上是指示平台启动一个运行 CloudSQL 代理的边车容器，对于 JDBC 的 Socket Factory 不需要它)
• 您使用 =tcp:5432 的语法实际上会指示 CloudSQL 代理通过 TCP 端口连接。

所以你的 Base JDBC url 应该看起来像 jdbc:postgresql://google/recruiters_wtf

您的完整数据源 url 将是(注意删除的 =tcp:5432 部分):

jdbc:postgresql://google/recruiters_wtf?cloudSqlInstance=recruiters-wtf:europe-west2:recruiters-wtf&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=${DB_USER}&password=${DB_PASS}

最后，请注意，如我在上面提供的链接中所述:

The PostgreSQL standard requires the Unix socket to have a .s.PGSQL.5432 suffix in the socket path. Some libraries apply this suffix automatically, but others require you to specify the socket path as follows: /cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432.