部署Django至Google App Engine遇PostgreSQL连接错误求助

问题背景 #

将Django Web应用部署到Google Cloud App Engine，完成main.py、入口点、app.yaml等配置后，出现数据库连接错误。已确认Cloud SQL上的PostgreSQL数据库正常运行，但连接仍被阻止。

错误截图 #

相关配置文件 #

app.yaml #

# [START django_app]
runtime: python38

handlers:
# This configures Google App Engine to serve the files in the app's static
# directory.
- url: /static
  static_dir: static/

# This handler routes all requests not caught above to your main app. It is
# required when static routes are defined, but can be omitted (along with
# the entire handlers section) when there are no static files defined.
- url: /.*
  script: django_blog.wsgi.application

env_variables:
    APPENGINE_URL: https://engineblog.wn.r.appspot.com

#entrypoint: gunicorn -b :$PORT django_project.wsgi:main

inbound_services:
- warmup

# Only pure Python libraries can be vendored
# Python libraries that use C extensions can
# only be included if they are part of the App Engine SDK 
# Using Third Party Libraries: https://cloud.google.com/appengine/docs/python/tools/using-libraries-python-27
# libraries:
# - name: MySQLdb
#   version: 1.2.5
# [END django_app].

home.html #

{% extends "base.html" %}

{% block content %}
{% for post in post_list %}
<div class="post-entry">
    <h2>
        <a href="">{{ post.title }}</a>
        <p>{{ post.body }}</p>
    </h2>
</div>
{% endfor %}
{% endblock content %}

settings.py #

"""
Django settings for django_project project.

Generated by 'django-admin startproject' using Django 4.0.6.

For more information on this file, see
https://docs.djangoproject.com/en/4.0/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/4.0/ref/settings/
"""

from pathlib import Path
import os

BASE_DIR = Path(__file__).resolve().parent.parent

# Build paths inside the project like this: BASE_DIR / 'subdir'.


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/4.0/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = "this is secret"

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = ["*", "https://engineblog.wn.r.appspot.com"]


# Application definition

INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "blog.apps.BlogConfig",
]

MIDDLEWARE = [
    "django.middleware.security.SecurityMiddleware",
    "django.contrib.sessions.middleware.SessionMiddleware",
    "django.middleware.common.CommonMiddleware",
    "django.middleware.csrf.CsrfViewMiddleware",
    "django.contrib.auth.middleware.AuthenticationMiddleware",
    "django.contrib.messages.middleware.MessageMiddleware",
    "django.middleware.clickjacking.XFrameOptionsMiddleware",
]

ROOT_URLCONF = "django_project.urls"

TEMPLATES = [
    {
        "BACKEND": "django.template.backends.django.DjangoTemplates",
        "DIRS": [BASE_DIR / "templates"],
        "APP_DIRS": True,
        "OPTIONS": {
            "context_processors": [
                "django.template.context_processors.debug",
                "django.template.context_processors.request",
                "django.contrib.auth.context_processors.auth",
                "django.contrib.messages.context_processors.messages",
            ],
        },
    },
]

WSGI_APPLICATION = "django_project.wsgi.application"

EMAIL_BACKEND = "django.core.mail.backends.console.EmailBackend"


# Database
# https://docs.djangoproject.com/en/4.0/ref/settings/#databases

if os.getenv("TRAMPOLINE_CI", None):
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.sqlite3",
            "NAME": os.path.join(BASE_DIR, "db.sqlite3"),
        }
    }
WSGI_APPLICATION = "django_project.wsgi.application"

import pymysql

pymysql.install_as_MySQLdb()

# [START db_setup]
if os.getenv("SERVER_SOFTWARE", "").startswith("Google App Engine"):
    # Running on production App Engine, so connect to Google Cloud SQL using
    # the unix socket at /cloudsql/<your-cloudsql-connection string>
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.postgresql_psycopg2",
            "HOST": "/cloudsql/connectionstring",
            "NAME": "dbname",
            "USER": "dbuser",
            "PASSWORD": "dbpassword",
        }
    }

else:
    # Running locally so connect to either a local MySQL instance or connect to
    # Cloud SQL via the proxy. To start the proxy via command line:
    #
    #     $ cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:3306
    #
    # See https://cloud.google.com/sql/docs/mysql-connect-proxy
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.postgresql_psycopg2",
            "HOST": "127.0.0.1",
            "PORT": "5432",
            "NAME": "dbname",
            "USER": "dbuser",
            "PASSWORD": "db_password",
        }
    }

# [END db_setup]
# Password validation
# https://docs.djangoproject.com/en/4.0/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        "NAME": "django.contrib.auth.password_validation.UserAttributeSimilarityValidator",
    },
    {
        "NAME": "django.contrib.auth.password_validation.MinimumLengthValidator",
    },
    {
        "NAME": "django.contrib.auth.password_validation.CommonPasswordValidator",
    },
    {
        "NAME": "django.contrib.auth.password_validation.NumericPasswordValidator",
    },
]


# Internationalization
# https://docs.djangoproject.com/en/4.0/howto/deployment/checklist/

LANGUAGE_CODE = "en-us"

TIME_ZONE = "UTC"

USE_I18N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/4.0/howto/static-files/
STATIC_ROOT = "static"

STATIC_URL = "/static/"
# Default primary key field type
# https://docs.djangoproject.com/en/4.0/ref/settings/#default-auto-field

DEFAULT_AUTO_FIELD = "django.db.models.BigAutoField"

排查与解决方案 #

1. 修正Cloud SQL连接字符串 #

settings.py中生产环境的HOST配置/cloudsql/connectionstring必须替换为实际的Cloud SQL实例连接名，格式为项目ID:区域:实例ID。例如实例属于项目my-project、区域us-central1、实例名my-postgres，则HOST应为/cloudsql/my-project:us-central1:my-postgres。

2. 配置App Engine服务账号权限 #

• 进入Google Cloud控制台的Cloud SQL页面，找到目标实例，切换到权限标签页。
• 确认App Engine默认服务账号（格式为项目ID@appspot.gserviceaccount.com）已拥有Cloud SQL Client角色权限，若无则添加该角色。

3. 验证PostgreSQL用户权限 #

• 确认settings.py中配置的dbuser在Cloud SQL实例中存在，且拥有对dbname数据库的读写权限。
• 可通过Cloud SQL控制台的数据库标签页检查，或执行SQL命令：

  GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
  

4. 移除冗余MySQL依赖 #

项目使用PostgreSQL，但settings.py中引入pymysql并设置为MySQLdb替代，属于冗余配置，建议删除以下代码：

import pymysql
pymysql.install_as_MySQLdb()

5. 修正app.yaml中的WSGI路径错误 #

app.yaml中的script配置为django_blog.wsgi.application，但settings.py定义的WSGI_APPLICATION是django_project.wsgi.application，两者不一致，需修改app.yaml对应行：

script: django_project.wsgi.application

6. 确认psycopg2依赖存在 #

确保requirements.txt中包含PostgreSQL驱动：

psycopg2-binary>=2.9.0

7. 关联App Engine与Cloud SQL实例 #

• 进入Google Cloud控制台的App Engine -> 服务页面，编辑目标服务的部署配置。
• 在云SQL连接部分添加你的PostgreSQL实例，确保App Engine可通过Unix socket访问数据库。

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