Debian – FastAPI cannot see PostgreSQL with docker-compose

I’m trying to attach a db to my python API. I use Windows 11, docker-compose, fastAPI.
Not sure what is missing.
In the logs I see the following:

docker-compose logs -f
time="2024-05-17T16:01:30+04:00" level=warning msg="\code\docker-compose.yml: `version` is obsolete"
postgres-1  |
postgres-1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres-1  |
postgres-1  | 2024-05-17 12:01:15.238 UTC [1] LOG:  starting PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres-1  | 2024-05-17 12:01:15.241 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-1  | 2024-05-17 12:01:15.241 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-1  | 2024-05-17 12:01:15.407 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-1  | 2024-05-17 12:01:15.791 UTC [31] LOG:  database system was shut down at 2024-05-17 11:54:26 UTC
postgres-1  | 2024-05-17 12:01:16.170 UTC [1] LOG:  database system is ready to accept connections
fastapi-1   | main folder created at: /app
fastapi-1   | files folder created at: /app/pickle_files
fastapi-1   | pdfs folder created at: /app/test_upload_pdfs
fastapi-1   | persist folder created at: /app/vector_db_for_sim/chroma
fastapi-1   | cwd folder created at: /app/load_and_qa
fastapi-1   | Traceback (most recent call last):
fastapi-1   |   File "/usr/local/lib/python3.9/site-packages/tenacity/__init__.py", line 470, in __call__
fastapi-1   |     result = fn(*args, **kwargs)
fastapi-1   |   File "/app/load_and_qa/config.py", line 69, in connect_to_postgres
fastapi-1   |     raise e
fastapi-1   |   File "/app/load_and_qa/config.py", line 60, in connect_to_postgres
fastapi-1   |     conn = psycopg2.connect(
fastapi-1   |   File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
fastapi-1   |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
fastapi-1   | psycopg2.OperationalError: connection to server at "172.29.0.2", port 5432 failed: Connection refused

Here’s the Dockerfile:

# Use an appropriate base image
FROM python:3.9

COPY ./load_and_qa/requirements.txt /app/requirements.txt

RUN pip install --no-cache-dir  -r /app/requirements.txt

# Install SQLite3 version 3.35.0 or higher
RUN pip install pysqlite3-binary
RUN pip install python-dotenv

WORKDIR /app
COPY ./user_simulation_data /app/user_simulation_data
COPY ./vector_db_for_sim /app/vector_db_for_sim
COPY ./pickle_files /app/pickle_files
COPY ./test_upload_pdfs /app/test_upload_pdfs

COPY ./load_and_qa /app/load_and_qa

CMD ["python", "./load_and_qa/config.py"]

# Expose the port your application runs on
EXPOSE 8000

# Wait for PostgreSQL to be ready
CMD ["sh", "-c", "sleep 10 && cd ./load_and_qa && uvicorn app_fastapi:app --host 0.0.0.0 --port 8000"]

The docker-compose.yml:

version: '3.8'

services:
  fastapi:
    build: .
    ports:
      - "8000:8000"
    volumes:
      - ./load_and_qa:/app/load_and_qa
    depends_on:
      postgres:
        condition: service_healthy
    links:
      - postgres:postgres_db
    networks:
      - my_network
    environment:
      SQLALCHEMY_DATABASE_URL: postgresql://myuser:mypassword@postgres:5432/my_db

  postgres:
    image: postgres:latest
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: my_db
      POSTGRES_INITDB_ARGS: "-EnableNextPgSocket"
    networks:
      - my_network
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
      - ./create-db.sql:/docker-entrypoint-initdb.d/create_database.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser"]
      interval: 10s
      timeout: 5s
      retries: 5


networks:
  my_network:

I try to connect to the db in config.py as follows:

load_dotenv()

DB_USER = os.environ.get("POSTGRES_USER")
DB_PASSWORD = os.environ.get("POSTGRES_PASSWORD")
DB_NAME = os.environ.get("POSTGRES_DB")
DB_HOST = "172.29.0.2"
# DB_HOST = os.environ.get("POSTGRES_HOST")



@retry(
    stop=stop_after_delay(120),  # Stop retrying after 30 seconds
    wait=wait_fixed(1)  # Wait 1 second between retries
)
def connect_to_postgres():
    try:
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST
        )
        return conn
    except psycopg2.OperationalError as e:
        # Raise an exception to trigger a retry
        raise e

conn = connect_to_postgres()
db_cursor = conn.cursor()

Then I import db_cursor in app_fastapi.py and try to register users and their data in the database.

Permissions are fine
postgresql.conf is fine