Newbie looking for some help with Postgres <> Django connection

[Marc Johnson]

Hi All,

I'm looking for tips/resources for a problem I'm facing with my Django app: https://ndcportal.herokuapp.com/

I'm attempting to connect my Dockerized Django app with a PostgreSQL db. The postgres db is already populated, and I can access the data via pgadmin, but I cannot figure out the appropriate settings in my docker-compose.yml & settings.py files to make the postgres db populate my django app.

I've reviewed multiple tutorials/blog posts, including Will Vincents Docker & PostgreSQL tutorial, but I feel like I'm missing one or two final & crucial steps. 

When I change my DB settings for Postgres, & I bring up the app via Docker, I continue running into this OperationalError: 

message:django.db.utils.OperationalError: could not connect to server: Connection refused
Is the server running on host "db" (172.29.0.2) and accepting
TCP/IP connections on port 5433?

Any tips/resources/feedback would be greatly appreciated. Thanks in advance.

Best,
Marc

[David Nugent]

It's been a while since I used heroku, but iirc it just uses a formatted pg url.

Install the module dj-database-url and use this in settings instead of the default DATABASES layout, something like:

DATABASES = {

    'default': dj_database_url.config(os.environ['DATABASE_URL'], conn_max_age=1800)

}

Then set DATABASE_URL in the heroku (and your development) environment. This setting will be of the form:

DATABASE_URL=postgresql://<username>:<password>@<host>:<port>/<databasename>

Again, its been a while but I also seem to recall that the DATABASE_URL is provided to your app automagically(?) a part of the provisioning so does not need to be explicitly set there.  Using the dj_database_url module in your settings above is the key.

[Marc Johnson]

Hi David,

Thanks for the feedback! 

I'm checking out dj_database_url and have a few follow up questions I was hoping you (or someone) could help me with.

1. Since this app is dockerized, I have my existing env vars in my docker-compose-prod.yml file. Is that where I should list DATABASE_URL? Like:

db:
    image: postgres:11
    volumes:
      - postgres_data:/var/lib/postgresql/data/
environment:
- DATABASE_URL=postgresql://<username>:<password>@<host>:<port>/<databasename> - 2. Does the below formatting look correct to you from my settings.py? I am running into issues with the env var DATABASE_URL and getting this error:  Environment variable "{}" not set'

DATABASES = {
    'default': dj_database_url.config(env("DATABASE_URL"), default="postgres://postgres@db/postgres", conn_max_age=1800),
    'ENGINE': 'django.db.backends.postgresql',
}

3. I am also getting this error when running locally. Do you have any advice for troubleshooting?

web_1  | [2020-11-03 16:35:59 +0000] [7] [ERROR] Error handling request /NDCs/

web_1  | Traceback (most recent call last):

web_1  |   File "/usr/local/lib/python3.8/site-packages/gunicorn/workers/sync.py", line 134, in handle

web_1  |     self.handle_request(listener, req, client, addr)

web_1  |   File "/usr/local/lib/python3.8/site-packages/gunicorn/workers/sync.py", line 175, in handle_request

web_1  |     respiter = self.wsgi(environ, resp.start_response)

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/core/handlers/wsgi.py", line 131, in __call__

web_1  |     signals.request_started.send(sender=self.__class__, environ=environ)

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 177, in send

web_1  |     return [

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 178, in <listcomp>

web_1  |     (receiver, receiver(signal=self, sender=sender, **named))

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/__init__.py", line 46, in reset_queries

web_1  |     for conn in connections.all():

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 229, in all

web_1  |     return [self[alias] for alias in self]

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 229, in <listcomp>

web_1  |     return [self[alias] for alias in self]

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 211, in __getitem__

web_1  |     self.ensure_defaults(alias)

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 176, in ensure_defaults

web_1  |     conn.setdefault('ATOMIC_REQUESTS', False)

web_1  | AttributeError: 'str' object has no attribute 'setdefault'

Many thanks in advance for any insights.

Best,
Marc

--

M: +1-646-541-2108

W: marcjohnson.info

[David Nugent]

On 4 Nov 2020, at 03:45, Marc Johnson <marcjoh...@gmail.com> wrote:

...

1. Since this app is dockerized, I have my existing env vars in my docker-compose-prod.yml file. Is that where I should list DATABASE_URL? Like:

db:
    image: postgres:11
    volumes:
      - postgres_data:/var/lib/postgresql/data/
environment:
- DATABASE_URL=postgresql://<username>:<password>@<host>:<port>/<databasename>

Looks good, but to be certain jump into your container (exec or run) and verify that the DATABASE_URL is set correctly. It should be.

2. Does the below formatting look correct to you from my settings.py? I am running into issues with the env var DATABASE_URL and getting this error:  Environment variable "{}" not set'

DATABASES = {
    'default': dj_database_url.config(env("DATABASE_URL"), default="postgres://postgres@db/postgres", conn_max_age=1800),
    'ENGINE': 'django.db.backends.postgresql',
}

There was a mistake in my previous response, so looks like that led you astray.

First, omit "ENGINE" here - unnecessary as postgres (or postgresql) scheme in the URL will drive that.

Second, you don't need to specify "DATABASE_URL" directly, that is the default variable used if you don't override it, or you can specify 'DATABASE_URL' there, or use env='DATABASE_URL'. Your choice.

If you do extract the url prior calling .config(), then use the  default= keyword, even though DATABASE_URL will override it. If you pass a positional argument to .config it will be used as the environment variable, not the value. If you have an already extracted value, then alternatively you can use .parse() instead of .config() which takes the url as the first positional arg.

dj_database_url is a pretty simple single module package. Check out the source code, it isn't too hard to comprehend.

DATABASES = {

    'default': dj_database_url.config(conn_max_age=1800)

}

should do the trick on heroku.

3. I am also getting this error when running locally. Do you have any advice for troubleshooting?

web_1  | [2020-11-03 16:35:59 +0000] [7] [ERROR] Error handling request /NDCs/

web_1  | Traceback (most recent call last):

web_1  |   File "/usr/local/lib/python3.8/site-packages/gunicorn/workers/sync.py", line 134, in handle

web_1  |     self.handle_request(listener, req, client, addr)

web_1  |   File "/usr/local/lib/python3.8/site-packages/gunicorn/workers/sync.py", line 175, in handle_request

web_1  |     respiter = self.wsgi(environ, resp.start_response)

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/core/handlers/wsgi.py", line 131, in __call__

web_1  |     signals.request_started.send(sender=self.__class__, environ=environ)

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 177, in send

web_1  |     return [

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 178, in <listcomp>

web_1  |     (receiver, receiver(signal=self, sender=sender, **named))

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/__init__.py", line 46, in reset_queries

web_1  |     for conn in connections.all():

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 229, in all

web_1  |     return [self[alias] for alias in self]

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 229, in <listcomp>

web_1  |     return [self[alias] for alias in self]

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 211, in __getitem__

web_1  |     self.ensure_defaults(alias)

web_1  |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 176, in ensure_defaults

web_1  |     conn.setdefault('ATOMIC_REQUESTS', False)

web_1  | AttributeError: 'str' object has no attribute 'setdefault'

I think this is the fallout from above, passing the url as the first positional instead of an environment variable name or just omitting it.

[Marc Johnson]

Hi David,

Thanks again for the feedback. When I remove the 'ENGINE' variable I get the error saying settings.DATABASES is improperly configured, as shown in the snapshot attached below.

But when I add the ENGINE variable, like listed below, I get an 'Internal Server Error':

DATABASES = {
    'default': dj_database_url.config(env='DATABASE_URL', conn_max_age=1800),
    'ENGINE': 'django.db.backends.postgresql',
}

My db settings in my docker-compose.yml file are also provided below:

  db:

    image: postgres:11
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:

      - "DATABASE_URL=postgresql://postgres:P#ssw0rd@postgres:5432/ndc_data"
      - "POSTGRES_HOST_AUTH_METHOD=trust"
      - "POSTGRES_PASSWORD=P#ssw0rd"
      - "POSTGRES_USER=postgres"
      - "POSTGRES_DB=ndc_data"
      - "POSTGRES_HOST=postgres"
    networks:
      - default

How am I screwing this up so royally!? I did not expect this connection to involve so much troubleshooting.

Thanks again for all of your insights.

Best,
Marc

[David Nugent]

On 5 Nov 2020, at 04:11, Marc Johnson <marcjoh...@gmail.com> wrote:

Hi David,

Thanks again for the feedback. When I remove the 'ENGINE' variable I get the error saying settings.DATABASES is improperly configured, as shown in the snapshot attached below.

But when I add the ENGINE variable, like listed below, I get an 'Internal Server Error':

DATABASES = {
    'default': dj_database_url.config(env='DATABASE_URL', conn_max_age=1800),
    'ENGINE': 'django.db.backends.postgresql',
}

My db settings in my docker-compose.yml file are also provided below:

  db:
    image: postgres:11
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:
      - "DATABASE_URL=postgresql://postgres:P#ssw0rd@postgres:5432/ndc_data"
      - "POSTGRES_HOST_AUTH_METHOD=trust"
      - "POSTGRES_PASSWORD=P#ssw0rd"
      - "POSTGRES_USER=postgres"
      - "POSTGRES_DB=ndc_data"
      - "POSTGRES_HOST=postgres"
    networks:
      - default

How am I screwing this up so royally!? I did not expect this connection to involve so much troubleshooting.

It shouldn't. Note that env='DATABASE_URL' is entirely redundant and can be omitted.

In any case, check the source code for dj_database_url.config to troubleshoot this, but ENGINE definitely isn't needed here. As you'll see from the source, the url scheme determines the engine used.

Did you validate that DATABASE_URL is set correctly in the container environment? 

I suspect this may be the issue here, although the docker-compose.yml looks fine.

Regards,

/d

[Marc Johnson]

Hi David,

Thanks again for the input. I havent quite figured it out yet but I appreciate the help!

Best,
Marc