Docker-Compose环境下FastAPI无法连接PostgreSQL求助

问题概述 #

在 Windows 11 环境下使用 Docker-Compose 部署 FastAPI 和 PostgreSQL 服务，FastAPI 连接数据库时触发以下错误：

psycopg2.OperationalError: connection to server at "172.29.0.2", port 5432 failed: Connection refused

已确认数据库权限、postgresql.conf 配置无问题，需排查连接失败的核心原因。

容器日志信息 #

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

核心问题与修复步骤 #

1. 硬编码容器IP导致连接失效 #

Docker 容器重启后内部 IP 会发生变化，且在 Compose 自定义网络中，应使用服务名作为数据库主机地址，而非固定IP。

修改 docker-compose.yml（FastAPI 服务环境变量）

在 fastapi 服务的 environment 段补充数据库连接所需的环境变量：

services:
  fastapi:
    ...
    environment:
      SQLALCHEMY_DATABASE_URL: postgresql://myuser:mypassword@postgres:5432/my_db
      POSTGRES_HOST: postgres
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: my_db
    ...

修改 config.py 中的数据库主机配置

替换硬编码的IP为从环境变量获取服务名：

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 = os.environ.get("POSTGRES_HOST")  # 启用这行
# DB_HOST = "172.29.0.2"  # 注释掉硬编码的IP

2. Dockerfile 多 CMD 指令冲突 #

Dockerfile 中只能有一个生效的 CMD 指令，当前配置中第一个 CMD ["python", "./load_and_qa/config.py"] 会被最后一个 CMD 覆盖。由于 config.py 是被 app_fastapi.py 导入执行的，需确保启动命令正确：

修正 Dockerfile 的 CMD 指令

保留最终的启动命令，删除重复的 CMD：

# 移除这行：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"]

3. 验证健康检查有效性 #

虽然已配置 PostgreSQL 健康检查，但可手动验证：

• 进入 PostgreSQL 容器：docker exec -it postgres-1 bash
• 执行健康检查命令：pg_isready -U myuser
• 若返回 accepting connections 则健康检查正常，否则需排查数据库启动问题。

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