Cloud SQL 实例连接在本地工作，但不在 App Engine 上工作

问题描述

我正在尝试在 Google Cloud App Engine 上托管作为 Node.js 应用程序实现的 API.API 使用的数据库是 PostgreSQL 9.6 数据库，该数据库由 Cloud SQL 实例托管.数据库通过Knex 连接到Node.js API.

I am trying to host an API implemented as a Node.js app on Google Cloud App Engine. The database that the API utilizes is a PostgreSQL 9.6 database which is hosted with a Cloud SQL instance. The database is connected to the Node.js API through Knex.

当托管在 App Engine 上时，不需要与数据库有任何联系的 API 端点可以正常工作.但是当需要联系数据库完成API调用时，日志中出现如下错误:

When hosted on App Engine, the API endpoints that do not require any contact with the database work fine. However, when the database needs to be contacted to complete the API call, the following error appears in the logs:

未处理的拒绝超时错误:Knex:超时获取联系.游泳池可能已经满了.你是否缺少一个.transacting(trx) 调用?

但是，如果我使用 Knex 在本地连接到 Cloud SQL 实例并通过 localhost:3000 寻址 API，则需要数据库的 API 调用完全可以正常完成.这验证了 Cloud SQL 上的 PostgreSQL 数据库实例是否正常工作，并且 App Engine 上托管的 Node.js 应用程序导致与 Cloud SQL 实例的连接失败.

However, if I use Knex to connect to the Cloud SQL instance locally and address the API through localhost:3000, an API call requiring the database completes perfectly fine. This verifies that the PostgreSQL database instance on Cloud SQL is working fine and that something about hte Node.js app being hosted on App Engine is making a connection to the Cloud SQL instance fail.

我像这样连接到数据库:

I connect to the database like so:

module.exports = require('knex')({
        client: 'pg',
        debug:  true,
        connection: {
          host : '35.194.32.254',
          user : 'postgres',
          password : 'mypassword',
          database : 'mydatabase'
        },
      });

同样，本地连接成功，App Engine 连接失败.

Again, local connections succeed, App Engine connections fail.

我的app.yaml文件如下:

runtime: nodejs
env: flex
beta_settings:
  cloud_sql_instances: my-project-12345:us-central1:mydatabase

为确保这不是权限问题，我向 App Engine 服务帐户 [email protected] 授予了 Cloud SQL 客户端权限.我还验证了 Cloud SQL 实例与 App Engine 上的 Node.js 应用部署在同一区域.

To ensure this is not a permission issue, I granted the App Engine service account [email protected] the Cloud SQL Client permission. I've also verified that the Cloud SQL instance is deployed in the same region as the Node.js app on App Engine.

我的问题是:如果可以在本地连接到 Cloud SQL 实例，为什么 App Engine 上的 Node.js 不能连接到它?

My question is this: Why can't Node.js on the App Engine connect to the Cloud SQL instance if it can connect to it locally?

推荐答案

根据 Vadim 的评论，必须使用 UNIX 套接字从 App Engine 连接到 Cloud SQL，否则必须涉及 IP 白名单，关于如何操作的指南很少要做.

As per Vadim's comment, UNIX sockets must be used to connect from App Engine to Cloud SQL otherwise IP whitelisting must be involved, something which there is little guides on how to do.

为此，我们只需将 host 从 35.194.32.254 更改为 UNIX 套接字 /cloudsql/my-project-12345:us-central1:我的数据库:

To accomplish this, we simply change the host from 35.194.32.254 to the UNIX socket /cloudsql/my-project-12345:us-central1:mydatabase:

module.exports = require('knex')({
    client: 'pg',
    debug:  true,
    connection: {
        host : '/cloudsql/esp-mobile-182605:us-central1:esp-db', // 127.0.0.1 for local testing, 35.194.32.254 to locally use hosted db on Cloud SQL
        user: 'postgres',
        password: 'seniordesign',
        database: 'esp_db'
    },
});

这篇关于Cloud SQL 实例连接在本地工作，但不在 App Engine 上工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！