Docker部署Python应用连接PostgreSQL遇连接拒绝/超时问题求助

我有一个Python脚本，功能是从API提取数据存入数据库，再从数据库视图取数并生成Excel通过邮件发送。已经写好docker-compose.yaml，能正常启动PostgreSQL和Adminer，但把Python脚本做成Docker服务后，启动容器时出现连接错误：

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "0.0.0.0", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

我的docker-compose.yaml配置 #

version: '3.8'

services:
  db:
    image: postgres:14.3
    container_name: postgres
    restart: always
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=pass
      - POSTGRES_DB=dbname
    ports:
      - 5432:5432
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
      - db_data:/var/lib/postgresql/data
  adminer:
    image: adminer:4.8.1
    container_name: adminer
    restart: always
    ports:
      - 8077:8080
    depends_on:
      - db
  app:
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      - db

volumes:
  db_data:

Dockerfile内容 #

FROM python:3.10-slim-buster

# set working directory
WORKDIR /app

# copy requirements file to work directory
COPY pyproject.toml pyproject.toml
COPY poetry.lock poetry.lock

# install poetry
RUN pip install --upgrade pip \
    && pip install poetry

# install dependencies
RUN poetry config virtualenvs.create false \
    && poetry install --no-dev --no-interaction --no-ansi

# add source code to the image
COPY src src

COPY . .

CMD [ "python", "src/main.py" ]

项目结构 #

> myproject
    > src
        > __init__.py
        > settings.py
        > main.py
        > utils.py
    > docker-compose.yaml
    > Dockerfile
    > init.sql
    > poetry.lock
    > pyproject.toml
    > README.md

我在Github Codespaces上开发，直接运行python src/main.py脚本正常，但做成Docker服务就报错，已经搜了两天解决方案没结果，求帮忙修复并理解原因。

编辑1：数据库连接代码 #

创建SQL引擎的函数：

def db_connection():
    """ Create SQL engine. """
    conn = f'postgresql://{DB_USER}:{DB_PW}@{DB_HOST}:{DB_PORT}/{DB_NAME}'
    engine = create_engine(conn)
    return engine

相关配置：

DB_HOST = "0.0.0.0"
DB_PORT = "5432"

编辑2：修改后仍报错 #

应用建议修改后，出现连接超时错误：

app_1      | Traceback (most recent call last):
app_1      |   File "/app/src/main.py", line 60, in <module>
app_1      |     main()
app_1      |   File "/app/src/main.py", line 54, in main
app_1      |     csv_data = get_filtered_data(engine, '...')
app_1      |   File "/app/src/utils.py", line 45, in get_filtered_data
app_1      |     with engine.connect() as conn:
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3251, in connect
app_1      |     return self._connection_cls(self)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
app_1      |     Connection._handle_dbapi_exception_noconnection(
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2413, in _handle_dbapi_exception_noconnection
app_1      |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
app_1      |     self._dbapi_connection = engine.raw_connection()
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3275, in raw_connection
app_1      |     return self.pool.connect()
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 455, in connect
app_1      |     return _ConnectionFairy._checkout(self)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1271, in _checkout
app_1      |     fairy = _ConnectionRecord.checkout(pool)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 719, in checkout
app_1      |     rec = pool._do_get()
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
app_1      |     with util.safe_reraise():
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
app_1      |     raise exc_value.with_traceback(exc_tb)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
app_1      |     return self._create_connection()
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 396, in _create_connection
app_1      |     return _ConnectionRecord(self)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 681, in __init__
app_1      |     self.__connect()
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 905, in __connect
app_1      |     with util.safe_reraise():
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
app_1      |     raise exc_value.with_traceback(exc_tb)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 901, in __connect
app_1      |     self.dbapi_connection = connection = pool._invoke_creator(self)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 636, in connect
app_1      |     return dialect.connect(*cargs, **cparams)
app_1      |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 580, in connect
app_1      |     return self.loaded_dbapi.connect(*cargs, **cparams)
app_1      |   File "/usr/local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
app_1      |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
app_1      | sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "172.23.0.2", port 5432 failed: Connection timed out
app_1      |    Is the server running on that host and accepting TCP/IP connections?
app_1      | 
app_1      | (Background on this error at: https://sqlalche.me/e/20/e3q8)

解决方案 #

1. 修正数据库主机地址 #

Docker Compose中，服务之间通过服务名互相访问，所以你的Python脚本里的DB_HOST应该改成db（对应docker-compose里的服务名），而不是0.0.0.0或容器IP：

DB_HOST = "db"
DB_PORT = "5432"

0.0.0.0在容器内部指的是自身容器，不是宿主机或其他容器，用服务名是Docker网络内的标准访问方式。

2. 处理数据库启动延迟问题 #

depends_on只保证容器启动顺序，不保证PostgreSQL服务完全就绪（比如初始化数据库、执行init.sql需要时间）。脚本启动时数据库可能还没准备好，导致连接超时，两种解决方式：

• 方案一：在脚本中添加重试逻辑
  修改数据库连接代码，增加重试机制直到连接成功：

  from sqlalchemy import create_engine
  import time
  
  def db_connection():
      """ Create SQL engine with retry. """
      conn_str = f'postgresql://{DB_USER}:{DB_PW}@{DB_HOST}:{DB_PORT}/{DB_NAME}'
      max_retries = 10
      retry_delay = 5
      for attempt in range(max_retries):
          try:
              engine = create_engine(conn_str)
              # 测试连接有效性
              with engine.connect():
                  print("数据库连接成功")
                  return engine
          except Exception as e:
              print(f"连接失败，重试 {attempt+1}/{max_retries}: {e}")
              time.sleep(retry_delay)
      raise Exception("数据库连接重试多次失败")
  

• 方案二：使用等待工具
  在Dockerfile中安装wait-for-it脚本，先等待数据库端口就绪再启动脚本：
  修改Dockerfile：

  # 安装wait-for-it
  RUN apt-get update && apt-get install -y wget
  RUN wget -O /usr/local/bin/wait-for-it https://raw.githubusercontent.com/vishnubob/wait-for-it/master/wait-for-it.sh
  RUN chmod +x /usr/local/bin/wait-for-it
  

  修改docker-compose.yaml中app服务的启动命令：

  app:
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      - db
    command: ["/usr/local/bin/wait-for-it", "db:5432", "--", "python", "src/main.py"]
  

3. 验证配置一致性 #

确保脚本中的DB_USER、DB_PW、DB_NAME和docker-compose中PostgreSQL的环境变量完全一致：

• POSTGRES_USER=user → DB_USER="user"
• POSTGRES_PASSWORD=pass → DB_PW="pass"
• POSTGRES_DB=dbname → DB_NAME="dbname"

4. 检查PostgreSQL访问权限 #

默认PostgreSQL镜像允许Docker网络内的连接，但如果你的init.sql修改过pg_hba.conf，要确保允许Docker默认网络段访问，可在init.sql中添加：

ALTER USER user WITH PASSWORD 'pass';
GRANT ALL PRIVILEGES ON DATABASE dbname TO user;
-- 允许Docker默认网络访问
host    all             all             172.17.0.0/16            scram-sha-256

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