Django with postgresl on App Engine Standard

[Lukas Elsner]

I have a Django application with Google Cloud SQL Postgres Database deployed to App Engine Flex at the moment.

I am trying to migrate it from Flex to Standard.

Everything seems to work, except the Database connection. The Documentation is not quite clear about it.

For Flex I need to use:

beta_settings:
  cloud_sql_instances: eco-income:europe-west3:ecoincome-postgres

in app.yaml.

In Standard, I tried with and without, but I always get a connection refused.

Did anyone get this working?

 

--------------------------------------------------------------------------------

Flex:

runtime: python

env: flex

manual_scaling:
  instances: 1

resources:
  cpu: 0.2
  memory_gb: 0.5
  disk_size_gb: 10

health_check:
 enable_health_check: False

runtime_config:
  python_version: 3

entrypoint: web/run.sh

env_variables:
  SECRET_KEY: '___DJANGO_SECRET_KEY___'
  DEBUG: 'True' # always False for deployment
  SMTP_HOST: "___SMTP_HOST___"
  SMTP_USER: "___SMTP_USER___"
  SMTP_PASSWORD: "___SMTP_PASSWORD___"
  DB_HOST: '/cloudsql/.........................'
  DB_PORT: '5432' # PostgreSQL port
  DB_NAME: '......'
  DB_USER: '........'
  DB_PASSWORD: '___DB_PASSWORD___'
  STATIC_URL: 'https://storage.googleapis.com/................-backend-static-files/static/' # this is the url that you sync static files to

handlers:
- url: /.*
  script: auto
  secure: always

beta_settings:
  cloud_sql_instances: .....................

 

Standard:

runtime: python37

entrypoint: web/run.sh

env_variables:
  SECRET_KEY: '___DJANGO_SECRET_KEY___'
  DEBUG: 'True' # always False for deployment
  SMTP_HOST: "___SMTP_HOST___"
  SMTP_USER: "___SMTP_USER___"
  SMTP_PASSWORD: "___SMTP_PASSWORD___"
  DB_HOST: '/cloudsql/.........................'  DB_PORT: '5432' # PostgreSQL port
  DB_NAME: '......'
  DB_USER: '........'
  DB_PASSWORD: '___DB_PASSWORD___'
  STATIC_URL: 'https://storage.googleapis.com/................-backend-static-files/static/' # this is the url that you sync static files to 

beta_settings:
  cloud_sql_instances: .....................

 

[George (Cloud Platform Support)]

Hello Lukas, 

You should concentrate on your node.js code, rather than on app.yaml configuration, and beta settings. The "Using Cloud SQL for PostgreSQL" documentation page instructs on how to set environment variables, add environment variables in app.standard.yaml file, and add a Node.js PostgreSQL client library to your application's package.json. Sample code is also provided. 

[Lukas Elsner]

Hi :)
Thank you for your answer. I am actually not using Node.js.
This is a Django (Python) application.
I tried everything Google mentions in the documentation.
Still no luck with DB-Connection :(


Cheers
Lukas

> --
> You received this message because you are subscribed to the Google Groups "Google App Engine" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to google-appengi...@googlegroups.com.
> To post to this group, send email to google-a...@googlegroups.com.
> Visit this group at https://groups.google.com/group/google-appengine.
> To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine/52efb810-3a5d-4aa6-9371-d0e446e470e7%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Chen Yu]

Hi Lukas,

It is a little difficult to say for sure where your app goes wrong with the information you have provided. If possible, can you post (or contact me directly if you prefer) your `settings.py`? Thanks.

Chen

[Lukas Elsner]

For some reason, my last Post got lost. :( Trying again. Here my settings.py

import os

import sys

web_dir = (os.path.abspath(os.path.join(os.path.dirname(os.path.abspath(__file__)), os.pardir)))

src_dir = (os.path.abspath(os.path.join(web_dir, os.pardir)))

lib_dir = os.path.join(src_dir, "lib")

sys.path.append(lib_dir)

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

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

# Quick-start development settings - unsuitable for production

# See https://docs.djangoproject.com/en/1.11/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!

SECRET_KEY = os.environ['SECRET_KEY']

ACME_CHALLENGE_URL_SLUG = ""

ACME_CHALLENGE_TEMPLATE_CONTENT = "" 

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

DEBUG = os.environ['DEBUG'] == 'True'

PROJECT_DIR = os.path.dirname(os.path.abspath(__file__))

ALLOWED_HOSTS = ['*']

# Application definition

INSTALLED_APPS = [

    'django.contrib.admin',

    'django.contrib.auth',

    'django.contrib.contenttypes',

    'django.contrib.sessions',

    'django.contrib.messages',

    'django.contrib.staticfiles',

    'rest_framework',

    'acme_challenge',

    'rest_framework.authtoken',

    'corsheaders',

    'django_countries',

    'rest_registration',

    'apps.users',

    'apps.api_lib',

]

MIDDLEWARE = [

    'corsheaders.middleware.CorsMiddleware',

    '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',

]

CORS_ORIGIN_WHITELIST = (

    '...........',

    'localhost:8000',

    'localhost:4200'

)

ROOT_URLCONF = 'app.urls'

TEMPLATES = [

    {

        'BACKEND': 'django.template.backends.django.DjangoTemplates',

        'DIRS': [],

        '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 = 'app.wsgi.application'

DATABASES = {

    'default': {

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

        'HOST': os.environ['DB_HOST'],

        'PORT': os.environ['DB_PORT'],

        'NAME': os.environ['DB_NAME'],

        'USER': os.environ['DB_USER'],

        'PASSWORD': os.environ['DB_PASSWORD'] or ''

    }

}

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',

    },

]

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True

# Static files (CSS, JavaScript, Images)

# https://docs.djangoproject.com/en/1.11/howto/static-files/

STATIC_URL = os.environ['STATIC_URL']  # /static/ if DEBUG else Google Cloud bucket url

# collectstatic directory (located OUTSIDE the base directory)

# TODO: configure the name and path to your static bucket directory (where collectstatic will copy to)

STATIC_ROOT = os.path.join(os.path.dirname(BASE_DIR), '..................')

STATICFILES_DIRS = [

    # TODO: configure the name and path to your development static directory

    os.path.join(BASE_DIR, 'static'),  # static directory (in the top level directory) for local testing

]

PASSWORD_HASHERS = [

    'django.contrib.auth.hashers.PBKDF2PasswordHasher',

    'django.contrib.auth.hashers.PBKDF2SHA1PasswordHasher',

    'django.contrib.auth.hashers.Argon2PasswordHasher',

    'django.contrib.auth.hashers.BCryptSHA256PasswordHasher',

]

REST_FRAMEWORK = {

    'DEFAULT_AUTHENTICATION_CLASSES': (

        'rest_framework.authentication.TokenAuthentication',

    ),

}

REST_FRAMEWORK_TOKEN_EXPIRE_HOURS = 24

AUTH_USER_MODEL = "users.user"

REST_REGISTRATION = {

    'REGISTER_VERIFICATION_URL': 'https://www............/verify-user/',

    'RESET_PASSWORD_VERIFICATION_URL': 'https://www.........../reset-password/',

    'REGISTER_EMAIL_VERIFICATION_URL': 'https://www............/verify-email/',

    'VERIFICATION_FROM_EMAIL': 'no-reply@...........',

}

EMAIL_HOST = os.environ['SMTP_HOST']

EMAIL_HOST_USER = os.environ['SMTP_USER']

EMAIL_HOST_PASSWORD = os.environ['SMTP_PASSWORD']

APPEND_SLASH = True

[Lukas Elsner]

web_dir = (os.path.abspath(os.path.join(os.path.dirname(os.path.abspath(__file__)), os.pardir)))
src_dir = (os.path.abspath(os.path.join(web_dir, os.pardir)))
lib_dir = os.path.join(src_dir, "lib")
"""
"""
sys.path.append(lib_dir)

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

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

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = os.environ['SECRET_KEY']

ACME_CHALLENGE_URL_SLUG = ""

ACME_CHALLENGE_TEMPLATE_CONTENT = "" \
                                  "."

    '......',
    '......',

    'localhost:8000',
    'localhost:4200'
)

ROOT_URLCONF = 'app.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        '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 = 'app.wsgi.application'

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

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': os.environ['DB_HOST'],
        'PORT': os.environ['DB_PORT'],
        'NAME': os.environ['DB_NAME'],
        'USER': os.environ['DB_USER'],
        'PASSWORD': os.environ['DB_PASSWORD'] or ''
    }
}

# Password validation
# https://docs.djangoproject.com/en/1.11/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/1.11/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True

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

STATIC_URL = os.environ['STATIC_URL']  # /static/ if DEBUG else Google Cloud bucket url

# collectstatic directory (located OUTSIDE the base directory)
# TODO: configure the name and path to your static bucket directory (where collectstatic will copy to)

STATIC_ROOT = os.path.join(os.path.dirname(BASE_DIR), '......')

STATICFILES_DIRS = [
    # TODO: configure the name and path to your development static directory
    os.path.join(BASE_DIR, 'static'),  # static directory (in the top level directory) for local testing
]

PASSWORD_HASHERS = [
    'django.contrib.auth.hashers.PBKDF2PasswordHasher',
    'django.contrib.auth.hashers.PBKDF2SHA1PasswordHasher',
    'django.contrib.auth.hashers.Argon2PasswordHasher',
    'django.contrib.auth.hashers.BCryptSHA256PasswordHasher',
]

REST_FRAMEWORK = {
    'DEFAULT_AUTHENTICATION_CLASSES': (
        'rest_framework.authentication.TokenAuthentication',
    ),
}

REST_FRAMEWORK_TOKEN_EXPIRE_HOURS = 24

AUTH_USER_MODEL = "users.user"

REST_REGISTRATION = {

    'REGISTER_VERIFICATION_URL': 'https://www.....verify-user/',
    'RESET_PASSWORD_VERIFICATION_URL': 'https://www......./reset-password/',
    'REGISTER_EMAIL_VERIFICATION_URL': 'https://www....../verify-email/',
    'VERIFICATION_FROM_EMAIL': 'no-reply@......',

}

EMAIL_HOST = os.environ['SMTP_HOST']
EMAIL_HOST_USER = os.environ['SMTP_USER']
EMAIL_HOST_PASSWORD = os.environ['SMTP_PASSWORD']

APPEND_SLASH = True

On Friday, 12 October 2018 13:50:11 UTC+2, Lukas Elsner wrote:

I have a Django application with Google Cloud SQL Postgres Database deployed to App Engine Flex at the moment.

I am trying to migrate it from Flex to Standard.

Everything seems to work, except the Database connection. The Documentation is not quite clear about it.

For Flex I need to use:

beta_settings:
  cloud_sql_instances: .......

[Attila-Mihaly Balazs]

Hi,

AFAIK PostgreSQL is not available under AppEngine standard if you're using Python 2.7 since the driver is not / can not be installed (Psycopg2). If you're using AppEngine standard "second generation" runtime with Python 3 you should be able to get it working by making sure the right packages are specified in the requirements.txt and Django is configured to connect using Unix Domain Sockets rather than TCP. So your Django settings should contain something like:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2', # note the _psycopg2 postfix
        'HOST': os.environ['DB_HOST'], # this should be set to something like "/cloudsql/..."
        # 'PORT': os.environ['DB_PORT'], - this is NOT needed

        'NAME': os.environ['DB_NAME'],
        'USER': os.environ['DB_USER'],
        'PASSWORD': os.environ['DB_PASSWORD'] or ''
    }
}

ie. you should try connecting to it using unix domain sockets rather than TCP. This seems to be also some relevant discussion: https://github.com/GoogleCloudPlatform/python-docs-samples/issues/870

Good luck!

Attila

[Chen Yu]

Hi Lukas,

Thanks fo getting back to us! The settings.py file looks alright; if possible, would you mind double-checking the DB_HOST environment variable in app.yaml? It should be of the `/cloudsql/YOUR-CONNECTION-NAME` format (e.g. /cloudsql/my-project:us-central1:my-sql-instance). Additionally, for PostgreSQL instances, a s.PGSQL.5432 suffix is required on the local socket path; some packages, such as psycopg2, add this automatically, however, if you are using other connectors, you may have to add this manually.

Also, if you do not mind me asking, what is the error message/code you receive when trying to access your Django App Engine application?

Chen

[Lukas Elsner]

@Attila-Mihaly Balazs

In https://github.com/GoogleCloudPlatform/python-docs-samples/issues/870 they are talking about Flex, but I have a problem with Standard in particular

my app.yaml:

runtime: python37
entrypoint: web/run.sh
env_variables:
  SECRET_KEY: '___DJANGO_SECRET_KEY___'
  DEBUG: 'True'

  SPECTRE_APP_ID: "___SPECTRE_APP_ID___"
  SPECTRE_APP_SECRET: "___SPECTRE_APP_SECRET___"
  MAILCHIMP_KEY: "___MAILCHIMP_KEY___"

  SMTP_HOST: "___SMTP_HOST___"
  SMTP_USER: "___SMTP_USER___"
  SMTP_PASSWORD: "___SMTP_PASSWORD___"

  DB_HOST: '/cloudsql/<project>:<region>:<db>'
  DB_NAME: '....'
  DB_USER: '....'
  DB_PASSWORD: '___DB_PASSWORD___'
  STATIC_URL: 'https://storage.googleapis.com/.....-backend-static-files/static/' 
beta_settings:
  cloud_sql_instances: <project>:<region>:<db>

The Error Message is the following:

could not connect to server: Connection refused
	Is the server running locally and accepting
	connections on Unix domain socket "/cloudsql/<project>:<region>:<db>/.s.PGSQL.5432"?

When using the same settings with "Flex"-Engine. It works fine. Is this really supposed to work? Can anyone confirm, this is working? Also Is Postgres in Google Cloud still in Beta stage? (Since more than one year now?)

Is anyone using this for production? I am a little bit concerned, that this might not be the best idea...

[Chen Yu]

Hi Lukas,

The local socket (/cloudsql/CONNECTION-NAME) is supposed to work; we will update the documentation soon and you will see similar instructions there as well. In fact, the sample for using Cloud SQL with App Engine Python 3.7 runtime is already pushed to our GitHub repo and our Django tutorial is recently updated to run on the same runtime. I just double-checked the Django tutorial with a PostgreSQL instance and cannot find a problem. I would like to check one more thing though, Lukas: is your PostgreSQL instance running in the same GCP Project as your app engine deployment?

Chen

[Lukas Elsner]

Hi Chen

yes it is the same Project (and same region). If I change my deployment to Flex, I have no problems with the connection.

Thus, I suppose, it has something to do with Standard Environment. :(

Thank you

Lukas

[Olu]

Attempt to replicate the issue was ineffective. We tested our Django for App Engine Python 3.7 sample and found no issues. Our recommendation would be to either create an issue on the Issue tracker or better still, contact GAE Support for further evaluation. Project IDs and other details might be required to review the Stackdriver logs and evaluate the issue. Otherwise, you consider creating a new project. 

[Attila-Mihaly Balazs]

The same here. I deployed this test project with App Engine Standard + Python 3.7 + Postgres 9.6 (from CloudSQL): https://github.com/cdman/google-appengine-django-postgres-test

Perhaps looking at settings.py will give you some idea: https://github.com/cdman/google-appengine-django-postgres-test/blob/master/mysite/settings.py#L138

Attila

[Lukas Elsner]

Can you confirm, that you deployed a Django application and are able establish a database connection? (e.g. login).

My App loads fine and all methods not needing the database are running. However, when trying to log into admin panel for example, I am getting the Error.

I created an issue as well:

https://issuetracker.google.com/issues/117804657


Since this problem is (probably) either some issue in Google Cloud, or lack of documentation, I am not willing to pay for any support and my free-trial support request got rejected. (For whatever reason) :(

[Lukas Elsner]

Maybe the problem is europe-west?

[Lukas Elsner]

Maybe the problem ist europe-west?

Sent from a mobile

--

You received this message because you are subscribed to the Google Groups "Google App Engine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-appengi...@googlegroups.com.
To post to this group, send email to google-a...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-appengine.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine/dfe47469-a29f-4382-b54a-c730bb1cde12%40googlegroups.com.

[Attila-Mihaly Balazs]

Yes, I can confirm that I can deploy the app from the linked GitHub repo and it can establish a database connection fine (this is the view I'm testing with: https://github.com/cdman/google-appengine-django-postgres-test/blob/master/mysite/urls.py#L29 - which does a "User.objects.count()").

Also, I don't think the region (europe-west) has anything to do with it since I deployed / tested it myself in the europe-west region.

Perhaps you could try printing out (ie. logging or returning it in a view) the values from os.listdir('/cloudsql') *in production* to see if your postgres instance shows up there.

Attila

[Lukas Elsner]

This code:

 

class Variables(View):
    def get(self, request):
        data = {}
        try:
            data["cloudsql"] = os.listdir('/cloudsql')
        except Exception as e:
            data["cloudsql"] = str(e)
        return JsonResponse(data)

Leads to the following output:

{"cloudsql": []}

I haven't tested the sample app, yet. But how can the application itself affect the existence of the socket? Shouldn't it be always there? 

[Attila-Mihaly Balazs]

Lukas - sorry, I don't have any further ideas. The CloudSQL socket should be present there. I would suggest at this point to create a ticket in the issue tracker (https://issuetracker.google.com/) or contact Google Support.

Sorry I can't be of more help,

Attila

[Mohammad I (Cloud Platform Support)]

Hello, I can see that you have already created an Issue Tracker (117804657) for this issue and that has been sent to Cloud SQL Engineering team for further investigation and they will update the progress on the issue tracker[1]. Please kindly note that, we cannot offer an ETA for a resolution at this time. [1] https://issuetracker.google.com/117804657