Docker环境下Python无法连接PostgreSQL数据库求助

问题现象 #

执行docker-compose up时，print_data服务日志出现连接拒绝错误：

test_assigned-print_data-1  |   File "/project/print_data.py", line 3, in 
test_assigned-print_data-1  |     connect = psycopg2.connect(
test_assigned-print_data-1  |               ^^^^^^^^^^^^^^^^^
test_assigned-print_data-1  |   File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
test_assigned-print_data-1  |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
test_assigned-print_data-1  |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
test_assigned-print_data-1  | psycopg2.OperationalError: 连接到"localhost"（127.0.0.1）端口5432的服务器失败：连接被拒绝
test_assigned-print_data-1  |   服务器是否在该主机上运行并接受TCP/IP连接？
test_assigned-print_data-1  | 连接到"localhost"（::1）端口5432的服务器失败：无法分配请求的地址
test_assigned-print_data-1  |   服务器是否在该主机上运行并接受TCP/IP连接？

当前配置文件 #

docker-compose.yml #

version: '3.9'

services:
  db:
    container_name: db
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_HOST: localhost


  get_data:
    build: .
    command: python3 get_data.py
    depends_on:
      - db

  print_data:
    build: .
    command: python3 print_data.py
    depends_on:
      - db

Python连接代码（print_data.py） #

import psycopg2

connect = psycopg2.connect(
    dbname="postgres",
    user="postgres",
    password="postgres",
    host="localhost",
    port="5432"
)

cur = connect.cursor()

# Execute an SQL query to aggregate the data by region
cur.execute("""
    SELECT region,
        SUM(population) AS total_population,
        MAX(country) FILTER (WHERE population = max_pop) AS largest_country,
        MAX(population) AS largest_population,
        MIN(country) FILTER (WHERE population = min_pop) AS smallest_country,
        MIN(population) AS smallest_population
    FROM (
        SELECT region, country, population,
            MAX(population) OVER (PARTITION BY region) AS max_pop,
            MIN(population) OVER (PARTITION BY region) AS min_pop
        FROM population_data
    ) subquery
    GROUP BY region
""")

# Fetch the results and print
results = cur.fetchall()
for row in results:
    region, total_population, largest_country, largest_population, smallest_country, smallest_population = row
    print(f"Region name{region}")
    print(f"Total population: {total_population:,}")
    print(f"Largest country: {largest_country:,}")
    print(f"Population of a largest country{largest_population:,}")
    print(f"Smallest country: {smallest_country:,}")
    print(f"Population of a smallest country{smallest_population:,}")

问题原因及修复方案 #

核心问题 #

1. 容器网络寻址错误：Python服务中host="localhost"指向的是当前容器自身，而非PostgreSQL容器。Docker Compose默认创建的网络中，服务名（db）可直接作为域名解析到对应容器IP。
2. 无效环境变量干扰：POSTGRES_HOST不是PostgreSQL官方镜像的有效环境变量，会导致数据库监听配置异常。
3. 启动顺序不代表就绪：depends_on仅保证db服务先启动，但不等待数据库完成初始化并接受连接。

修复步骤 #

1. 修改Python连接代码

将host="localhost"改为host="db"：

connect = psycopg2.connect(
    dbname="postgres",
    user="postgres",
    password="postgres",
    host="db",  # 替换为docker-compose中的服务名
    port="5432"
)

2. 修正docker-compose.yml

删除无效的POSTGRES_HOST变量，添加健康检查确保数据库就绪后再启动Python服务：

version: '3.9'

services:
  db:
    container_name: db
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

  get_data:
    build: .
    command: python3 get_data.py
    depends_on:
      db:
        condition: service_healthy

  print_data:
    build: .
    command: python3 print_data.py
    depends_on:
      db:
        condition: service_healthy

3. 验证修复

执行以下命令重新部署：

docker-compose down
docker-compose up --build

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