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? — fbmch
– fbmch, Commented Apr 8, 2019 at 12:35
Can you try to force the following engine for your connection?: 'ENGINE': 'django.db.backends.postgresql_psycopg2' — Alex Riquelme
– Alex Riquelme, Commented Apr 8, 2019 at 14:33
Attempting with 'unix_socket' and 'unix_sock' yielded the following: psycopg2.ProgrammingError: invalid dsn: invalid connection option "unix_socket". — brystmar
– brystmar, Commented Apr 9, 2019 at 2:00
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
– brystmar, Commented Apr 12, 2019 at 21:44
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, Commented Apr 12, 2019 at 21:45

brystmar · Accepted Answer · 2019-04-16 15:40:36Z

4

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.

answered Apr 16, 2019 at 15:40

brystmar

1951 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BrainPermafrost Over a year ago

so you are not using "{}/.s.PGSQL.5432".format(unix_socket_path)? you are using 127.0.0.1:5432 to connect, even when deployed on app engine?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related