Sqlalchemy does not see Postgresql db

August 3, 2022

I develop a web-app using Flask under Python3. I have a problem with connecting to postgresql via sqlalchemy.

Dockerfile:

FROM ubuntu:20.04
ENV LANG en_US.UTF-8

RUN apt update
RUN apt install -y python3-pip python3.8-dev
RUN apt install default-libmysqlclient-dev -y
RUN apt install libpq-dev -y


WORKDIR /app
ADD ./requirements.txt /app/requirements.txt
RUN pip3 install psycopg2
RUN --mount=type=cache,target=/root/.cache/pip pip3 install -r requirements.txt
RUN pip3 install -r requirements.txt
COPY . /app



ENV PYTHONPATH "${PYTHONPATH}:/app/src"
CMD ["gunicorn", "-c", "python:config.gunicorn", "--worker-class", "aiohttp.worker.GunicornWebWorker", "--pythonpath", "/app/src/", "src.main:app"]

docker-compose.yml:

services:
  backend:
    environment:
      TZ: 'UTC'
    volumes:
      - ./src:/app/src
    build:
      context: .
    restart: always
    ports:
      - '80:5000'
    depends_on:
      - postgres
    links:
      - postgres:postgres

  postgres:
    restart: always
    image: postgres:latest
    volumes:
      - /var/lib/postgresql
    environment:
      POSTGRES_PASSWORD: 1234
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_INITDB_ARGS: "-A md5"
    ports:
      - "5432:5432"

Postgresql logs:

2022-08-03 14:21:46.773 UTC [1] LOG:  starting PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-08-03 14:21:46.776 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-08-03 14:21:46.776 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-08-03 14:21:46.781 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-08-03 14:21:46.787 UTC [27] LOG:  database system was shut down at 2022-08-03 14:20:52 UTC
2022-08-03 14:21:46.794 UTC [1] LOG:  database system is ready to accept connections
2022-08-03 14:21:50.342 UTC [1] LOG:  received fast shutdown request
2022-08-03 14:21:50.344 UTC [1] LOG:  aborting any active transactions
2022-08-03 14:21:50.347 UTC [1] LOG:  background worker "logical replication launcher" (PID 33) exited with exit code 1
2022-08-03 14:21:50.347 UTC [28] LOG:  shutting down
2022-08-03 14:21:50.365 UTC [1] LOG:  database system is shut down

Python app logs:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
        Is the server running on host "0.0.0.0" and accepting
        TCP/IP connections on port 5432?

As you can see postgresql is running on 0.0.0.0:5432 and sqlalchemy tries to connect to 0.0.0.0:5432. Therefore, it is not very clear what it does not like.

>Solution :

If I remember correctly, when connecting to another service within a docker compose network, you have to connect using the service name as the host name – so postgres:5432 in your case.