django cloud sql sample for postgresql

[KushGoyal]

Please add sample for django postgresql settings.

I tried the instructions at: https://cloud.google.com/appengine/docs/flexible/python/using-cloud-sql-postgres

But they are not working. I am getting unix socket error. More details:
http://stackoverflow.com/questions/42950418/postgres-settings-for-django-on-google-app-engine-flexi

[theacodes]

/cc @waprin

[agmathew]

I am also experiencing a similar issue. See here: http://stackoverflow.com/questions/42836927/error-connecting-django-to-google-cloudsql-postgres-database-in-google-app-engin

[ryanmats]

@KushGoyal @agmathew thanks for the feedback. i'll take a look at this today.

[ryanmats]

@KushGoyal @agmathew So I was able to get it to work when deployed, and wasn't able to reproduce your error message :/

I would recommend doing the Django on App Engine Flexible tutorial (https://cloud.google.com/python/django/flexible-environment) from the beginning step-by-step with these PostgreSQL specific changes:

1. When you get to 'Create a Cloud SQL Instance' step 1, use https://cloud.google.com/sql/docs/postgres/create-instance and make sure you create a PostgreSQL instance instead of a Cloud SQL instance. When you create the instance, you'll be asked to create a password. This password is for the 'postgres' default user for this PostgreSQL instance.

2. Skip step 2 of 'Create a Cloud SQL Instance,' since you set the password when creating the instance.

3. In 'Initialize your Cloud SQL instance' step 1, use this command to use port 5432 instead of 3306: "./cloud_sql_proxy -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:5432"

4. In 'Configure the Database Settings,' when you are editing mysite/settings.py, also change 'ENGINE': 'django.db.backends.mysql' to 'ENGINE':'django.db.backends.postgresql'

5. While you are configuring database settings here, also edit your requirements.txt file and add psycopg2==2.7.1

If you do this and still have difficulties, let us know along with more details about where you got stuck and what error messages you got!

[KushGoyal]

@ryanmats it worked! Although I didn't change a thing 😲
Thanks for the help

[agmathew]

@ryanmats Same for me, it's working now, but I haven't touched anything. Do you know if any changes were made to GAE or CloudSQL Postgres in the last few days? Is there somewhere where we can track changes like this? We're currently evaluating whether to use GAE for a new product and I'm a little concerned about a service "fixing" itself.

[ryanmats]

@KushGoyal @agmathew Great to hear that it works! I just filed a bug for the Cloud SQL / Postgres team to ask them about this - will let you know what they say.

@jonparrott do you know if there are any resources for customers to track product changes? (see @agmathew 's comment above)

[theacodes]

@agmathew @KushGoyal
Postgres for Cloud SQL is in beta, so it's expected that things will be changing rapidly as we approach GA, especially things around reliability. I highly recommend joining the user group so that you can provide us with feedback on the beta.

For Cloud SQL, we have release notes published here:
https://cloud.google.com/sql/docs/release-notes

For GAE flexible, we have release notes published here: https://cloud.google.com/appengine/docs/flexible/python/release-notes

[theacodes]

(Closing as this appears to be resolved, but feel free to comment if you have any more questions/issues).

[KushGoyal]

The issue is repeating again on a new project. I have followed the exact steps which are:

1. create a postgresql instance

2. create a db

3. get name of connection using command: gcloud sql instances describe <instance-name>

4. add connection name to app.yaml

beta_settings:
  cloud_sql_instances: <connection-name>

5. use host as /cloudsql/<connection-name>

[KushGoyal]

I tried to deploy the project again and this time it worked without changing anything.

I don't know what changed. Perhaps this issue is related to app engine not detecting the presence of postgresql db.

[ryanmats]

@KushGoyal we'll ping the CloudSQL engineering team about this. thanks for the feedback.

[ankushagarwal]

@KushGoyal :

1. What was the error message that you received?
2. What was the format of the SQLALCHEMY_DATABASE_URI that you used (eg: postgresql+psycopg2://username:password@/databasename?host=connection-name)
3. Did you try connecting to the instance from your local machine by using cloud-sql-proxy? (look at the Setting up your local environment instructions here : https://cloud.google.com/appengine/docs/flexible/python/using-cloud-sql-postgres)

[KushGoyal]

@ankushagarwal

1. error message:

could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/cloudsql/instanse-connection-name/.s.PGSQL.5432"?

2. I am using django.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.getenv('DB_NAME'),
        'USER': os.getenv('DB_USERNAME'),
        'PASSWORD': os.getenv('DB_PASSWORD'),
        'HOST': '/cloudsql/<connection-name>'
    },
}

3. I connected using static ip.

[ryanmats]

So we've determined that this is an issue with new App Engine deployments that use CloudSQL (not existing deployments that have already worked) -> we've assigned this bug to someone on the App Engine Infrastructure team to fix.

If you deploy your app and it works, it should continue to work. If one of your new deployments isn't working, you should be able to try again later and it will work.

[KushGoyal]

@ryanmats thanks for the update. Is it possible to know when will this be fixed?

[KushGoyal]

@ryanmats Is there any pattern to this error? I have deployed my project 5-6 times now but the error is still there.

[ryanmats]

@KushGoyal It looked like the problem had gone away for a while but I'll take a look again this afternoon. Make sure to double-check that you have the right app.yaml and settings.py settings based on your project.

You are using Django with Postgres on App Engine Flex, correct?

[ryanmats]

@KushGoyal I just tested a deployment of a Django App Engine Flex App with Postgres and it works. Can you please go through this tutorial https://cloud.google.com/python/django/flexible-environment (which now has complete Postgres instructions), making sure you set the correct app.yaml / settings.py settings and try deploying again? (One time recently I deployed with the wrong instance connection string and this messed things up)

The problem should be resolved but this weekend I will continually re-deploy this app to see if the problem arises again at any point.

[KushGoyal]

@ryanmats Yes, I am using django with postgres on app engine flex.

I have checked my settings and they are exactly as described in the docs. I am using the exact same code in my other project and it is working. Even in that project it didn't work the first time. But after redeploying it started working. But this new project is not working after redeploying several times.

I am posting my db settings and app yaml below:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.getenv('DB_NAME'),
        'USER': os.getenv('DB_USERNAME'),
        'PASSWORD': os.getenv('DB_PASSWORD'),
        'HOST': os.getenv('DB_HOST')
    },
}

runtime: python
env: flex
entrypoint: gunicorn -b :$PORT myproject.wsgi

runtime_config:
  python_version: 3

env_variables:
  DB_NAME: 'djangodb'
  DB_USERNAME: 'postgres'
  DB_PASSWORD: 'mypassword'
  DB_HOST: '/cloudsql/project-id:us-central1:db-instance-name'

beta_settings:
  cloud_sql_instances: project-id:us-central1:db-instance-name'

the error:

could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/cloudsql/<connection-name>/.s.PGSQL.5432"?

the connection name is exactly matching from the command:
gcloud beta sql instances describe <db-instance-name>

[ryanmats]

@KushGoyal Can you confirm that your instance connection name (used twice in your app.yaml) has the SQL instance name (not the database name) as the last part?

Also, can you confirm that your connection string is the right one for the project pointed to by gcloud? (run gcloud config list project)

While you confirm this, I'll be looking into other potential sources of the problem. (Just asking because these are mistakes I've made in the past).

[KushGoyal]

@ryanmats I have double checked all the values. The last part is my db instance name. I am using the correct project id. The connection name is as is from the describe command.

[ryanmats]

@KushGoyal I just tried another deployment and it worked again :/

Hmm can you check to make sure the Google Cloud SQL API is enabled for your project? (See https://cloud.google.com/python/django/flexible-environment and click on 'Enable API') Also make sure you have 'psycopg2==2.7.1' in your requirements.txt.

Another potential thing to try is to change your mysite/settings.py to use the new username/password (not the default postgres username/password) that our tutorial (https://cloud.google.com/python/django/flexible-environment) says to create in 'Initialize your Cloud SQL Instance' step #2.

Another thing we might want to try is to have you share your project (that does not work when deployed) with a Google engineer or SRE to see if they can dig in a bit deeper. But only if you're comfortable and this is just a sample project without sensitive data.

[KushGoyal]

@ryanmats The project is working now. I had not enabled the google cloud sql api. I saw that Google Cloud SQL api is enabled so I skipped the step. I found the correct api when I clicked on enable api.

There are 2 APIs in the api manager dashboard one is called Google Cloud SQL API which is the sqladmin.googleapis.com api and the other is called Google Cloud SQL which is sql-component.googleapis.com.

I apologise for the trouble caused. Thank you for being patient and helping me out.

Some feedback from the experience:
The documentation has provided the name of the api as Cloud SQL Administration but it does not match the actual api names. Suggested names for the current apis: Google Cloud SQL Admin API and Google Cloud SQL Component API

Although I still think that there might be a bug in postgres deployment which needs to be examined. I got the same error in my other project and it worked on redeploying without changing anything, even the sql api.

[ryanmats]

@KushGoyal no problem. Our job here is to help developers, collect feedback, and work to improve the developer experience.

Thanks for feedback about the API in the tutorial. We will fix the step to say "Enable the Google Cloud SQL API." We'll also look into clarifying our API names to be more user friendly and avoid confusion.

Regarding postgres deployment, there was definitely a problem before but from my experience it looks like it has been working well the past week. Now that I have a 100% verified correct project, I'll keep re-deploying it at regular intervals for a while to monitor for potential problems.

Thanks for your patience during our beta period. Let us know if you have any more problems or questions.

[placz]

This seems to still be an issue. I'm still getting this exact error and it hasn't resolved itself after waiting and re-deploying.
When I ssh to the instances, the /cloudsql directory is empty which seems like a problem.

patrick@<redacted>:~$ ls -al /cloudsql/
total 8
drwxr-xr-x  2 root root 4096 Jun  9 16:20 .
drwxr-xr-x 26 root root 4096 Jun  9 16:20 ..

I'm noticing that the directory doesn't have write permissions opened up as described here.

update: in the logs for the instance, I noticed this error:
googleapi: Error 403: Access Not Configured. Cloud SQL Administration API has not been used in project ### before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=### then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry., accessNotConfigured

That URL didn't actually work, if I recall correctly, but I noticed the 'Enable the API' button on one of the tutorials that I skipped because I thought I had already followed those directions. https://console.cloud.google.com/flows/enableapi?apiid=sqladmin

After enabling that, the log message has gone away and the /cloudsql directory has a file representing the connection. That looks like it has fixed the issue.. so hopefully this comment helps someone in the future!

[Ypurek]

I've got same issue now with django 2.0 app and gcloud PostgreSQL 9.6.
SQL APIs - enabled
yaml:

beta_settings:
    cloud_sql_instances: <my-instance>

settings.py:
DATABASES = {

    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'my-db-name',
        'USER': os.environ.get('DB_USER'),
        'PASSWORD': os.environ.get('DB_PASSWORD'),
        'PORT': '5432',
        'HOST': '/cloudsql/<my-instance>
    }

Error on app engine:
Exception Value: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/cloudsql/<my-instance>/.s.PGSQL.5432"?

Does anyone know how to fix it?

[Ypurek]

Figured out how to fix issue. Guide advises to use:

'ENGINE': 'django.db.backends.postgresql'

but it works with

'ENGINE': 'django.db.backends.postgresql_psycopg2',

[avibash]

After deploying a new version (no changes relating to DB) we started to get the connections on Unix domain socket "/cloudsql/<my-instance>/.s.PGSQL.5432 error. I have tried @Ypurek solution but we still get the error. Any suggestions?

[Ypurek]

Well, after dozen tries to run my app on App Engine without random issues :) I've set up Compute Engine VM from Launcher - https://console.cloud.google.com/launcher/details/bitnami-launchpad/djangostack?project=circus-ticket-app
It already has Python and Postgres withing and guide how to run your app. And FTP access to make deployment faster and easier

[victorhooi]

I seem to be randomly hitting this error as well.

Trying to follow the tutorial at:

https://cloud.google.com/python/django/flexible-environment

OperationalError at /admin/
could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/cloudsql/project_name:australia-southeast1:project_name/.s.PGSQL.5432"?

Is there a categorical answer on whether this is a configuration error, or just some intermittent failure in GAE?

[DanielJoyce]

Encountered this bug as well, with a ruby app

[DaskJeeves]

Still getting this issue. What is the solution here?

[japhiaolson]

Still getting this issue here as well.

[ClubOfRome]

MacOS 10.13.4
Django 1.11.10
Postgres 9.6.3

I have not been able to connect to cloud_sql_proxy at other than port 5432.
So, I have to stop my local postgres server to free the port.

[DaskJeeves]

Strangely enough, my cloud_sql_proxy connects on 5434, while my local db connects at 5433. And when I deploy I have it set to 5432. Quite the system

[ClubOfRome]

Makes sense to me. I have to shut down my local postgres to use the proxy.

…

On Thu, May 17, 2018, 7:33 PM David Soth-Kimmel ***@***.***> wrote: Strangely enough, my cloud_sql_proxy connects on 5434, while my local db connects at 5433. And when I deploy I have it set to 5432. Quite the system — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#870 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AafMSx8TCdx83jopK86f_ozz12Knpdkzks5tzgjDgaJpZM4MlIt3> .

[AngelosP]

@ryanmats I ran into the exact same problem as @KushGoyal because of a bug on this Google Cloud Console page:

Link to page

In this page it's impossible to unselect Woo-Server. Because I didn't want to enable the SQL API on Woo-Server I cancelled out of this dialog and tried to enable it through the API library:

Now, I'm not 100% sure what happened next but somehow I had the API enabled (it was listed as enabled), but as far as App Engine was concerned it was disabled ... hence the "could not connect to server: No such file or directory" error message.

After reading @ryanmats reply I remembered cancelling out of the first dialog in Google Cloud Console. So I went back to the tutorial, used it despite the bug of not allowing me to unselect Woo-Server and now it works fine. So for everyone running into the same issue, just make sure you enable the Google SQL API this way.

[kurtisvg]

You should be able to customize the port the proxy connects on by editing the instance connection string: <INSTANCE_CONNECTION_NAME>=tcp:<PORT>.

There are probably differences in the connection string needed depending on using TCP sockets or using Unix sockets, but these will depend on the library being used.

[Dean-Christian-Armada]

In my case, it was solved when I put the configuration below in app.yml

beta_settings:
  cloud_sql_instances: "<connection-name>"

[Dean-Christian-Armada]

Another tip is to check the logs of the container of your cloud_sql when you are in a flex environment like

docker logs --tail=100 -f 810ae3237e94
2018/09/28 10:40:29 Ready for new connections
2018/09/28 10:40:30 errors parsing config:
        googleapi: Error 403: Access Not Configured. Cloud SQL Admin API has not been used in project 636840238029 
before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin.googleapis.
com/overview?project=636840238029 then retry. If you enabled this API recently, wait a few minutes for the action t
o propagate to our systems and retry., accessNotConfigured

[ibci]

I'm still getting this error... I've tried everything I've seen so far:

• enabling SQL APIs (admin and component) + credentials
• psycopg2==2.8.4
• set 'ENGINE': 'django.db.backends.postgresql_psycopg2',
• set beta_settings at the end of the app.yaml
• checked 'docker logs --tail' and just seeing "Ready for new connections" without any error

I can connect to the DB if I run cloud_sql_proxy locally, and it works perfectly.
My django version is: Django-3.0.
I'm desperate... if somebody could help... :)

[ibci]

Solved, the problem was that in my HOST i used the region as "eu-west6" instead of "europe-west6"