Error while performing Cloud Build and connecting to Cloud SQL

Question

I am trying to automate the process of building a Django app on Google Cloud Build. This app has to communicate with a PostgreSQL DB hosted on Cloud SQL and there're three stages I would like to complete:

Building the image with a Dockerfile
Pushing the image to Artifact Registry
Running Django migrations python manage.py migrate by connecting to Cloud PostgreSQL through Cloud SQL Proxy

I have successfully made the first two stages work with these configuration files:

cloudbuild.yaml

steps:
  # Build the container image
  - id: "build image"
    name: "gcr.io/cloud-builders/docker"
    args: ["build", "-t", "${_IMAGE_TAG}", "."]
  # Push the container image to Artifact Registry
  - id: "push image"
    name: "gcr.io/cloud-builders/docker"
    args: ["push", "${_IMAGE_TAG}"]
  # Apply Django migrations
  - id: "apply migrations"
    # https://github.com/GoogleCloudPlatform/ruby-docker/tree/master/app-engine-exec-wrapper
    name: "gcr.io/google-appengine/exec-wrapper"
    # Args: image tag, Cloud SQL instance, environment variables, command
    args:
      ["-i", "${_IMAGE_TAG}",
       "-s", "${PROJECT_ID}:${_DB_REGION}:${_DB_INSTANCE_NAME}=tcp:127.0.0.1:3306",
       "-e", "DJANGO_SECRET_ID=${_DJANGO_SECRET_ID}",
       "--", "python", "manage.py", "migrate"]

# Substitutions (more substitutions within the trigger on Google Cloud Build)
substitutions:
  _IMAGE_TAG: ${_REPOSITORY_REGION}-docker.pkg.dev/${PROJECT_ID}/${_REPOSITORY}/${_IMAGE_NAME}:${COMMIT_SHA}

# Display the image in the build results
# https://cloud.google.com/build/docs/building/build-containers#store-images
images:
  - '${_IMAGE_TAG}'

Dockerfile

FROM python:3.7-slim

# Add new /app directory to the base image
ENV APP_HOME /app
WORKDIR $APP_HOME

# Removes output stream buffering, allowing for more efficient logging
ENV PYTHONUNBUFFERED 1

# Copy requirements.txt to WORKDIR and install dependencies
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# Copy local code to the container image.
COPY . .

# Run the web service on container startup. Here we use the gunicorn
# webserver, with one worker process and 8 threads.
# For environments with multiple CPU cores, increase the number of workers
# to be equal to the cores available.
# Timeout is set to 0 to disable the timeouts of the workers to allow Cloud Run to handle instance scaling.
# PORT is automatically added to the running container and shouldn't be set by us
# https://cloud.google.com/run/docs/reference/container-contract#env-vars
CMD exec gunicorn --bind 0.0.0.0:$PORT --workers 1 --threads 8 --timeout 0 main_project.wsgi:application

settings.py

import os
import io

import environ
import google.auth
from google.cloud import secretmanager


# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

# Load environment variables
env = environ.Env(DEBUG=(bool, False))
env_file = os.path.join(BASE_DIR, ".env")

# ...from file
if os.path.exists(env_file):
    env.read_env(env_file)
# ...from Secret manager
else:
    # Get Google project ID
    _, os.environ["GOOGLE_CLOUD_PROJECT"] = google.auth.default()
    g_project_id = os.environ.get("GOOGLE_CLOUD_PROJECT")

    # Pull secrets
    sm_client = secretmanager.SecretManagerServiceClient()
    django_secret_id = os.environ.get("DJANGO_SECRET_ID")
    name = f"projects/{g_project_id}/secrets/{django_secret_id}/versions/latest"
    payload = sm_client.access_secret_version(name=name).payload.data.decode("UTF-8")

    # Load secrets
    env.read_env(io.StringIO(payload))

...

DATABASES = {
    'default': env.db()
}

SecretManager / env.py

DATABASE_URL=postgres://username:user_password@127.0.0.1:3306/db_name
SECRET_KEY=50 characters
DEBUG=True

For some reason, however, I am having an issue with reaching my Cloud SQL instance through the Cloud SQL Proxy:

Starting Step #2 - "apply migrations"
Step #2 - "apply migrations": Pulling image: gcr.io/google-appengine/exec-wrapper
Step #2 - "apply migrations": Using default tag: latest
Step #2 - "apply migrations": latest: Pulling from google-appengine/exec-wrapper
Step #2 - "apply migrations": 75f546e73d8b: Already exists
Step #2 - "apply migrations": 0f3bb76fc390: Already exists
Step #2 - "apply migrations": 3c2cba919283: Already exists
Step #2 - "apply migrations": ca8b528f3beb: Pulling fs layer
Step #2 - "apply migrations": 9192e910d340: Pulling fs layer
Step #2 - "apply migrations": 8d727c8f3915: Pulling fs layer
Step #2 - "apply migrations": 8d727c8f3915: Download complete
Step #2 - "apply migrations": 9192e910d340: Verifying Checksum
Step #2 - "apply migrations": 9192e910d340: Download complete
Step #2 - "apply migrations": ca8b528f3beb: Verifying Checksum
Step #2 - "apply migrations": ca8b528f3beb: Download complete
Step #2 - "apply migrations": ca8b528f3beb: Pull complete
Step #2 - "apply migrations": 9192e910d340: Pull complete
Step #2 - "apply migrations": 8d727c8f3915: Pull complete
Step #2 - "apply migrations": Digest: sha256:2ed781e6546168ea45a0c7483b725d4a159b0d88770445ababb5420a8fb5b5b4
Step #2 - "apply migrations": Status: Downloaded newer image for gcr.io/google-appengine/exec-wrapper:latest
Step #2 - "apply migrations": gcr.io/google-appengine/exec-wrapper:latest
Step #2 - "apply migrations": 
Step #2 - "apply migrations": ---------- INSTALL IMAGE ----------
Step #2 - "apply migrations": f77d0fc9799de606907614381a65d904bf75c89d: Pulling from my-google-project-id/rep-backend-staging/image-backend-staging
Step #2 - "apply migrations": Digest: sha256:0be33db09badd30dcd22d7b9d1b711276e67a35bb5b19ae337ee2af63a480448
Step #2 - "apply migrations": Status: Image is up to date for europe-west1-docker.pkg.dev/my-google-project-id/rep-backend-staging/image-backend-staging:f77d0fc9799de606907614381a65d904bf75c89d
Step #2 - "apply migrations": europe-west1-docker.pkg.dev/my-google-project-id/rep-backend-staging/image-backend-staging:f77d0fc9799de606907614381a65d904bf75c89d
Step #2 - "apply migrations": 
Step #2 - "apply migrations": ---------- CONNECT CLOUDSQL ----------
Step #2 - "apply migrations": cloud_sql_proxy is running.
Step #2 - "apply migrations": Connections: my-google-project-id:europe-west1:my-cloudsql-instance=tcp:127.0.0.1:3306.
Step #2 - "apply migrations": 
Step #2 - "apply migrations": ---------- EXECUTE COMMAND ----------
Step #2 - "apply migrations": python manage.py migrate
Step #2 - "apply migrations": Traceback (most recent call last):
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
Step #2 - "apply migrations":     self.connect()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2 - "apply migrations":     return func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 197, in connect
Step #2 - "apply migrations":     self.connection = self.get_new_connection(conn_params)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2 - "apply migrations":     return func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/postgresql/base.py", line 185, in get_new_connection
Step #2 - "apply migrations":     connection = Database.connect(**conn_params)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 127, in connect
Step #2 - "apply migrations":     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
Step #2 - "apply migrations": psycopg2.OperationalError: could not connect to server: Connection refused
Step #2 - "apply migrations":   Is the server running on host "127.0.0.1" and accepting
Step #2 - "apply migrations":   TCP/IP connections on port 3306?
Step #2 - "apply migrations": 
Step #2 - "apply migrations": 
Step #2 - "apply migrations": The above exception was the direct cause of the following exception:
Step #2 - "apply migrations": 
Step #2 - "apply migrations": Traceback (most recent call last):
Step #2 - "apply migrations":   File "manage.py", line 20, in 
Step #2 - "apply migrations":     main()
Step #2 - "apply migrations":   File "manage.py", line 16, in main
Step #2 - "apply migrations":     execute_from_command_line(sys.argv)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
Step #2 - "apply migrations":     utility.execute()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/core/management/__init__.py", line 395, in execute
Step #2 - "apply migrations":     self.fetch_command(subcommand).run_from_argv(self.argv)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 328, in run_from_argv
Step #2 - "apply migrations":     self.execute(*args, **cmd_options)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 369, in execute
Step #2 - "apply migrations":     output = self.handle(*args, **options)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 83, in wrapped
Step #2 - "apply migrations":     res = handle_func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 86, in handle
Step #2 - "apply migrations":     executor = MigrationExecutor(connection, self.migration_progress_callback)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/executor.py", line 18, in __init__
Step #2 - "apply migrations":     self.loader = MigrationLoader(self.connection)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/loader.py", line 49, in __init__
Step #2 - "apply migrations":     self.build_graph()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/loader.py", line 212, in build_graph
Step #2 - "apply migrations":     self.applied_migrations = recorder.applied_migrations()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/recorder.py", line 76, in applied_migrations
Step #2 - "apply migrations":     if self.has_table():
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/recorder.py", line 56, in has_table
Step #2 - "apply migrations":     return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2 - "apply migrations":     return func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 260, in cursor
Step #2 - "apply migrations":     return self._cursor()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 236, in _cursor
Step #2 - "apply migrations":     self.ensure_connection()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2 - "apply migrations":     return func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
Step #2 - "apply migrations":     self.connect()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
Step #2 - "apply migrations":     raise dj_exc_value.with_traceback(traceback) from exc_value
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
Step #2 - "apply migrations":     self.connect()
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2 - "apply migrations":     return func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 197, in connect
Step #2 - "apply migrations":     self.connection = self.get_new_connection(conn_params)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2 - "apply migrations":     return func(*args, **kwargs)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/django/db/backends/postgresql/base.py", line 185, in get_new_connection
Step #2 - "apply migrations":     connection = Database.connect(**conn_params)
Step #2 - "apply migrations":   File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 127, in connect
Step #2 - "apply migrations":     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
Step #2 - "apply migrations": django.db.utils.OperationalError: could not connect to server: Connection refused
Step #2 - "apply migrations":   Is the server running on host "127.0.0.1" and accepting
Step #2 - "apply migrations":   TCP/IP connections on port 3306?

This problem does not occur when I try to run the Django migrations locally. The connection is established perfectly with ./cloud_sql_proxy -instances=my-google-project-id:europe-west1:my-cloudsql-instance=tcp:127.0.0.1:3306

user_11372593 · Answer

According to the documentation, the exec-wrapper simulate an App Engine flex environment.

Therefore, it expects only a Cloud SQL connection ID and not more (not the tcp:127.0.0.1:3306 at the end as you can put in Cloud SQL proxy). That also means it create a unix socket and not a TCP port to connect to the database.

I recommend you to review your script and to use unix socket connection and to give it another try.

Error while performing Cloud Build and connecting to Cloud SQL

1 Answers