Google Cloud Functions第二代运行Python+PostgreSQL报文件不存在错误

我用Google Cloud Functions第二代部署应用，部署命令如下：

gcloud functions deploy postgresql-python-examples 
--gen2 
--runtime=python311 
--region=europe-west1 
--source=. 
--entry-point=main 
--trigger-http
--timeout=540
--verbosity=info

运行时提示/layers/google.python.pip/pip/lib/python3.11/site-packages/sqlalchemy/engine/base.py不存在，怀疑是依赖问题，但实际错误输出显示连接相关异常。以下是相关文件和错误信息：

requirements.txt #

Flask
pg8000
SQLAlchemy
cloud-sql-python-connector
google-cloud-secret-manager
gunicorn

main.py #

import os
import sqlalchemy


def main(request=None):
    """ 初始化Postgres Cloud SQL实例的Unix套接字连接池 """
    # 注意：将凭证存在环境变量中虽方便，但不够安全——建议用更安全的方案如Secret Manager保管密钥
    db_user = os.environ["DB_USER"]  # 示例：'my-database-user'
    db_pass = os.environ["DB_PASS"]  # 示例：'my-database-password'
    db_name = os.environ["DB_NAME"]  # 示例：'my-database'
    unix_socket_path = os.environ["INSTANCE_UNIX_SOCKET"]  # 示例：'/cloudsql/project:region:instance'

    pool = sqlalchemy.create_engine(
        # 等效URL：
        # postgresql+pg8000://<db_user>:<db_pass>@/<db_name>
        #                         ?unix_sock=<INSTANCE_UNIX_SOCKET>/.s.PGSQL.5432
        # 注意：部分驱动要求`unix_sock`查询参数使用不同的键，比如psycopg2需要将路径设为host才能成功连接
        sqlalchemy.engine.url.URL.create(
            drivername="postgresql+pg8000",
            username=db_user,
            password=db_pass,
            database=db_name,
            query={"unix_sock": "{}/.s.PGSQL.5432".format(unix_socket_path)},
        ),
        # 连接池配置
        pool_size=5,
        max_overflow=2,
        pool_timeout=30,  # 30秒
        pool_recycle=1800,  # 30分钟
    )
    print("Pool OK with unix_socket")

    # 创建表
    with pool.connect() as conn:
        conn.execute(sqlalchemy.text(
            "CREATE TABLE IF NOT EXISTS votes "
            "( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
            "candidate VARCHAR(6) NOT NULL, PRIMARY KEY (vote_id) );"
        ))
        conn.commit()

    return "OK"


if __name__ == '__main__':
    main()

错误输出 #

Pool OK with unix_socket

[2023-04-12 10:53:43,301] ERROR in app: Exception on / [POST]

Traceback (most recent call last): File "/layers/google.python.pip/pip/lib/python3.11/site-packages/pg8000/core.py", line 231, in __init__ self._usock.connect(unix_sock) FileNotFoundError: [Errno 2] No such file or directory

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/layers/google.python.pip/pip/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__ self._dbapi_connection = engine.raw_connection()

^^^^^^^^^^^^^^^^^^^^^^^

File "/layers/google.python.pip/pip/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3275, in raw_connection

sqlalchemy.exc.InterfaceError: (pg8000.exceptions.InterfaceError) communication error

1. 修复Unix Socket连接问题 #

错误核心是找不到Unix Socket文件，执行以下检查：

• 确认INSTANCE_UNIX_SOCKET环境变量值为/cloudsql/[PROJECT_ID]:[REGION]:[INSTANCE_ID]，不要包含.s.PGSQL.5432（代码中已拼接该后缀）。
• 确保Cloud SQL实例与Cloud Functions处于同一区域，且实例已启用私有IP。
• 给Cloud Functions的服务账号添加Cloud SQL Client角色，确保有权限访问Cloud SQL实例。

2. 改用Cloud SQL Connector简化连接 #

你引入了cloud-sql-python-connector但未使用，改用连接器可自动处理连接细节，避免路径错误：
修改main.py的连接逻辑：

from google.cloud.sql.connector import Connector
import sqlalchemy

# 替换原有的create_engine部分
def getconn():
    connector = Connector()
    conn = connector.connect(
        os.environ["INSTANCE_CONNECTION_NAME"],  # 格式：project:region:instance
        "pg8000",
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASS"],
        db=os.environ["DB_NAME"]
    )
    return conn

pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
    pool_size=5,
    max_overflow=2,
    pool_timeout=30,
    pool_recycle=1800
)

3. 锁定依赖版本避免冲突 #

在requirements.txt中指定具体版本，防止依赖兼容问题：

Flask==2.3.3
pg8000==1.30.3
SQLAlchemy==2.0.23
cloud-sql-python-connector==1.4.2
google-cloud-secret-manager==2.18.4
gunicorn==21.2.0

4. 本地验证逻辑 #

本地运行时用Cloud SQL Proxy模拟Unix Socket，设置正确环境变量，确认代码能正常连接数据库后再部署。

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