使用Docker Compose部署FastAPI无法连接PostgreSQL的问题求助

在使用Docker Compose部署FastAPI和PostgreSQL时，遇到数据库连接异常。已确认使用的凭据真实有效，但不确定psycopg2代码中应指定5433还是5432端口，两种端口配置均出现错误。

Docker Compose配置文件 #

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:

FastAPI数据库连接代码 #

@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")

错误信息 #

端口设为5433时的错误 #

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

端口设为5432时的错误 #

password authentication failed for user "talha"

解决方案 #

1. 正确选择端口
   Docker Compose的ports: "5433:5432"是将主机的5433端口映射到PostgreSQL容器的内部5432端口。FastAPI容器和PostgreSQL容器在同一个Docker网络smart_net内，通信时需要使用PostgreSQL容器的内部端口5432，而非主机映射的5433端口。

2. 解决凭据认证失败问题

   • 可能是数据卷sqldb中已存在旧的数据库初始化数据，导致新设置的POSTGRES_USER和POSTGRES_PASSWORD不生效。执行以下命令删除数据卷并重新部署：

     docker-compose down -v
     docker-compose up --build
     

   • 检查env_file.env文件，确保其中没有覆盖PostgreSQL凭据的环境变量，保证代码中使用的user、password与PostgreSQL容器的POSTGRES_USER、POSTGRES_PASSWORD完全一致。

3. 移除重复创建数据库的代码
   Docker Compose中已设置POSTGRES_DB: sparkdb，PostgreSQL容器初始化时会自动创建该数据库。代码中执行CREATE DATABASE sparkdb会导致"数据库已存在"的错误，建议移除这行代码。

内容的提问来源于stack exchange，提问作者Talha Anwar