Unable to connect with postgres in fastapi using docker compose

Question

I am unable to connect with PostgreSQL in fastapi. I am using docker compose to connect both. I am using the exact credentials as shown below. So its not dummy one. I am also confuded whether the port should be 5433 or 5433 in psycopg2 code.

This is docker compose file.

version: '3.5'

services:
  smart_api:
    image: smart_api:latest
    build:
      context: ./smart_api
    env_file:
      - ./env_file.env
    container_name: smart_api
    ports:
      - 8000:8000
    networks:
      - smart_net

  postgres:
      image: postgres:alpine
      container_name: postgres
      environment:
        POSTGRES_DB: sparkdb
        POSTGRES_USER: talha
        POSTGRES_PASSWORD: mypass
      volumes:
        - sqldb:/var/lib/postgresql/data
      ports:
        - "5433:5432"
      networks:
        - smart_net
networks:
  smart_net:
    driver: bridge
    name: smart_net

volumes:
  sqldb:

This is my fastapi code where I want to connect with database

@app.on_event("startup")
def on_startup():
    connection = psycopg2.connect(
                dbname='sparkdb',
                user='talha',
                password='mypass',
                host='postgres',
                port='5433'
            )
    connection.autocommit = True
    cursor = connection.cursor()
    cursor.execute("CREATE DATABASE sparkdb")

But I am getting this error

smartclinic_api  |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 677, in lifespan
smartclinic_api  |     async with self.lifespan_context(app) as maybe_state:
smartclinic_api  |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 566, in __aenter__
smartclinic_api  |     await self._router.startup()
smartclinic_api  |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 656, in startup
smartclinic_api  |     handler()
smartclinic_api  |   File "/app/app/app.py", line 25, in on_startup
smartclinic_api  |     connection = psycopg2.connect(
smartclinic_api  |   File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
smartclinic_api  |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
smartclinic_api  | psycopg2.OperationalError: connection to server at "postgres" (172.18.0.3), port 5433 failed: Connection refused

Thanks in advance

EDIT: If i change port in psycopg2 to 5432 i got this error

password authentication failed for user "talha"

Answer 1

Change port to 5432. 5432 is Postgres port inside container and use it to connect between containers by compose network. Port 5433 is to connect from your host.