flask can not connect postgresql with docker-compose

Question

I use docker-compose built a flask app with postgresql. But flask can not connect postgresql. Error message:

     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
    Is the server running on host "postgres-db" (192.168.16.2) and accepting
    TCP/IP connections on port 5432?

When I go inside the docker container, and ping postgres-db, it works well. And I start the flask app, it works well too. It only can not connect when I use docker-compose up

my docker-compose file:

version: '3.7'

services:
  postgres-db:
    restart: always
    image: postgres:11.1-alpine   
    privileged: true  
    ports:
      - 5432:5432  
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: Aa123456 
      PGDATA: /var/lib/postgresql/data/pgdata 
    volumes:
      - ./db/postgres/data:/var/lib/postgresql/data/pgdata

  api:
    build:
      context: ./api
      dockerfile: Dockerfile
    volumes:
      - './api:/usr/src/app'
    ports:
      - 5002:5000
    environment:
      - FLASK_CONFIG=development
      - FLASK_ENV=development
      - APP_SETTINGS=project.config.DevelopmentConfig
      - DATABASE_URL=postgres://postgres:Aa123456@postgres-db:5432/cms
      - DATABASE_TEST_URL=postgres://postgres:Aa123456@postgres-db:5432/cms_test
      - SECRET_KEY=ZQbn05PDeA7v11
    depends_on:
      - postgres-db
    container_name: api
    links: 
      - postgres-db:postgres-db

  nginx:
    build:
      context: ./nginx
      dockerfile: Dockerfile
    restart: unless-stopped
    ports:
      - 8080:8080
    depends_on:
      - api
      - client

  client:
    build:
      context: ./client
      dockerfile: Dockerfile
    volumes:
      - './client:/usr/src/app'
      - '/usr/src/app/node_modules'
    ports:
      - 3008:3000
    environment:
      - NODE_ENV=development
      - REACT_APP_SERVICE_URL=http://localhost:8080
      - CHOKIDAR_USEPOLLING=true
    depends_on:
      - api

If you wait a minute and try docker-compose up -d again, does it work? (A frequent problem is applications starting before databases are fully initialized.) links: aren't necessary and are a potential source of complication, and I'd suggest just removing that. — David Maze
– David Maze, Commented Dec 31, 2019 at 15:13

larsks · Accepted Answer · 2019-12-31 15:59:21Z

5

You are experiencing a race condition: the application is trying to connect to the database before the database is available. This isn't a problem that's unique to Docker; if your application depends on an external resource (like a database server), then you need to write your code to handle situations like this.

A simple solution is to block the startup of your application until the database is available. Running something like SELECT 1 in a loop and waiting for that to succeed is sufficient. An example shell script that does this would look something like this:

while ! psql -h postgresb-db -c 'select 1'; do
  sleep 1
done

If you're using sqlachemy in your flask application, you could do something similar:

from sqlalchemy import create_engine
from sqlalchemy import exc
import time


while 1:
    try:
        e = create_engine('postgres://lars@localhost/sandbox')
        e.execute('select 1')
    except exc.OperationalError:
        print('Waiting for database...')
        time.sleep(1)
    else:
        break

print('Connected!')

The above works fine in terms of solving the startup race condition, but it doesn't handle the situation in which the database server restarts while your application is running. In this case, you need to gracefully handle disconnects/reconnects. This requires a little more work in terms of the design of your code and is beyond the scope of this answer. See this answer for some discussions around this topic.

answered Dec 31, 2019 at 15:59

larsks

318k49 gold badges474 silver badges482 bronze badges

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

2 Comments

Yang Wang Over a year ago

there is depends_on in my docker-compose file. Is this not means it will wait database ready and then run the docker? depends_on: - postgres-db

larsks Over a year ago

Docker doesn't know anything about applications running inside containers. It has no way to tell if the database server is ready to service connections. depends_on just means "wait until the named container has started", which is in practice almost never useful.

Collectives™ on Stack Overflow

flask can not connect postgresql with docker-compose

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related