Unable to connect Google App Engine to CloudSQL Postgres (via SQLAlchemy) in cloud shell

Question

Running a small web app on Google App Engine (Flexible) using Python 3, Flask, SQLAlchemy, the psycopg2-binary package, and CloudSQL Postgres. The app connects to CloudSQL properly in my local dev environment (I use the proxy), but it won't seem to connect when deployed in the cloud.

Worked fine the first time I deployed it last week. After pushing an update and re-deploying, the app no longer connects to CloudSQL in the cloud shell or when deployed -- even after rolling back to that previous commit. But it connects just fine on my local dev machine using the proxy.

I've verified through debugging that the necessary credentials are being properly inserted in my SQLALCHEMY_DATABASE_URI, which is: postgres+psycopg2://[user]:[pw]@/[db_name]?host=/cloudsql/breadsheet:us-west1:breadsheet.

Only change I've made on the platform since the initial deploy was upgrading from Cloud Datastore to Firestore, which is where I'm pulling my environment variables from to build the URI. No data ever came from Datastore, so that shouldn't matter.

I'm following the GAE Postgres connection guide for Unix. Here's my app.yaml:

runtime: python
env: flex

instance_class: F1

entrypoint: gunicorn -w 1 breadsheet:breadapp

error_handlers:
  - file: app/templates/errors/default_error.html

  - error_code: over_quota
    file: app/templates/errors/over_quota.html

beta_settings:
  cloud_sql_instances: breadsheet:us-west1:breadsheet

Here's the error when I run gunicorn from the cloud shell:

(venv) [me]@cloudshell:~/breadsheet (breadsheet)$ gunicorn -w 1 breadsheet:breadapp
[2019-04-07 10:23:16 -0700] [471] [INFO] Starting gunicorn 19.9.0
[2019-04-07 10:23:16 -0700] [471] [INFO] Listening at: http://127.0.0.1:8000 (471)
[2019-04-07 10:23:16 -0700] [471] [INFO] Using worker: sync
[2019-04-07 10:23:16 -0700] [474] [INFO] Booting worker with pid: 474

2019-04-07 09:40:08,838 Exception on / [GET]
Traceback (most recent call last):
[...]
  File "/home/[me]/breadsheet/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 437, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/[me]/breadsheet/venv/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/cloudsql/breadsheet:us-west1:breadsheet/.s.PGSQL.5432"?


The above exception was the direct cause of the following exception:
[line of code with the first database call in my app]
[...]
  File "/home/[me]/breadsheet/venv/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/cloudsql/breadsheet:us-west1:breadsheet/.s.PGSQL.5432"?

Tried the following (and more!) to no avail:

Whitelisting various IPs in CloudSQL management
Adjusting IAM permissions
Using nano to comment out the Firestore code and hard-code the URI
Using nano to hard-code the SQLALCHEMY_DATABASE_URI under env_variables in my app.yaml

What to try next?

Shouldn't the SQLALCHEMY_DATABASE_URI have 'unix_socket' rather than 'host' in it? — fbraga
Can you try to force the following engine for your connection?: 'ENGINE': 'django.db.backends.postgresql_psycopg2' — Alex Riquelme
Attempting with 'unix_socket' and 'unix_sock' yielded the following: psycopg2.ProgrammingError: invalid dsn: invalid connection option "unix_socket". — brystmar
Followed that guide again -- to a T this time -- and was successful. Thank you! For those reading this via search in the future, I had skipped the export SQLALCHEMY_DATABASE_URI=postgresql+psycopg2://[USER]:[PW]@127.0.0.1:5432/[DB_NAME] previously because I didn't particularly care about the cloud shell environment and assumed it wouldn't make a difference for deployment. (Btw: setting SQLALCHEMY_DATABASE_URI under env_variables: in the app.yaml is not required, provided you set that value elsewhere in your code. I store my app & db keys in Firestore and build them via code.) — brystmar
Another comment for posterity. Later, when troubleshooting the db connection in cloud shell, I found myself skipping the sudo mkdir /cloudsql; sudo chmod 777 /cloudsql command because I had already created the /cloudsql folder -- ignoring the second part of that command. The db connection doesn't work without that sudo chmod 777 /cloudsql command :) — brystmar

brystmar brystmar · Accepted Answer · 2019-04-16T15:40:36

For those reading this via search in the future, I had previously skipped the export SQLALCHEMY_DATABASE_URI=postgresql+psycopg2://[USER]:[PW]@127.0.0.1:5432/[DB_NAME] because I didn't particularly care about the cloud shell environment and assumed it wouldn't make a difference for deployment. Apparently it does.

Later, when troubleshooting the db connection in cloud shell, I found myself skipping the first part (sudo mkdir /cloudsql;) of this two-step command sudo mkdir /cloudsql; sudo chmod 777 /cloudsql because I had already created the /cloudsql folder. I'm still not sure why both parts of the command are needed -- especially when the folder already exists -- but the db connection didn't work for me without them both.

One more note: setting SQLALCHEMY_DATABASE_URI under env_variables: in the app.yaml is not required, provided you set that value elsewhere in the app. Adding to the env_variables: simply makes the value available as an easily-accessible variable.

Unable to connect Google App Engine to CloudSQL Postgres (via SQLAlchemy) in cloud shell

1 Answers