Docker部署PostgreSQL+SQLAlchemy密码认证失败求助

问题背景 #

尝试通过SQLAlchemy编写的Python脚本将数据导入Docker部署的PostgreSQL（采用Docker Volume存储数据），执行导入命令时持续收到password authentication failed for user "postgres"错误。已完成以下排查：

• 验证docker-compose文件正确性
• 检查Python脚本语法
• 搜索相关错误信息
• 确认容器内postgres用户存在
• 查阅Stack Overflow类似问题
• 在Slack群组求助

执行的导入命令 #

python ingestData.py \
  --user=postgres \
  --password=postgres \
  --host=localhost \
  --port=5432 \
  --db=ny_taxi \
  --table_name=yellow_taxi \
  --url=https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz

收到的错误信息 #

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

blackitalian@blackitalian:/mnt/d/ds_projects/de_zoomcamp/docker_sql$ docker exec -it postgres bash
2037c7983657:/# id postgres
uid=70(postgres) gid=70(postgres) groups=70(postgres),70(postgres)

docker-compose配置 #

services:
  pgdatabase:
    image: postgres:15-alpine
    container_name: postgres
    user: "0:0"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=ny_taxi
    volumes:
      - "pg-data:/var/lib/postgresql/data"
    ports:
      - "5432:5432"
    networks:
      - pg-network

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    user: "${UID}:${GID}"
    environment:
      - PGADMIN_DEFAULT_EMAIL=blackitalian@gmail.com
      - PGADMIN_DEFAULT_PASSWORD=root
    volumes:
      - "pg-admin:/var/lib/pgadmin"
    ports:
      - "8080:80"
    networks:
      - pg-network

networks:
  pg-network:
    name: pg-network

volumes:
  pg-data: 
    name: ingest_pgdata
  pg-admin:
    name: ingest_pgadmin

ingestData.py代码 #

#!/usr/bin/env python
# coding: utf-8

import os
import argparse
from time import time
import pandas as pd
from sqlalchemy import create_engine


def main(params):
    """Import data to Postgres"""
    user = params.user
    password = params.password
    host = params.host
    port = params.port
    db = params.db
    table_name = params.table_name
    url = params.url

    # 处理压缩文件后缀
    if url.endswith('.csv.gz'):
        csv_name = 'output.csv.gz'
    else:
        csv_name = 'output.csv'

    os.system(f"wget {url} -O {csv_name}")

    engine = create_engine(f'postgresql://{user}:{password}@{host}:{port}/{db}')

    df_iter = pd.read_csv(csv_name, iterator=True, chunksize=100000)

    df = next(df_iter)

    df.tpep_pickup_datetime = pd.to_datetime(df.tpep_pickup_datetime)
    df.tpep_dropoff_datetime = pd.to_datetime(df.tpep_dropoff_datetime)

    df.head(n=0).to_sql(name=table_name, con=engine, if_exists='replace')
    df.to_sql(name=table_name, con=engine, if_exists='append')

    while True:
        try:
            t_start = time()
            df = next(df_iter)
            df.tpep_pickup_datetime = pd.to_datetime(df.tpep_pickup_datetime)
            df.tpep_dropoff_datetime = pd.to_datetime(df.tpep_dropoff_datetime)
            df.to_sql(name=table_name, con=engine, if_exists='append')
            t_end = time()
            print(f'inserted another chunk, took {t_end - t_start} second')
        except StopIteration:
            print("Finished ingesting data into the postgres database")
            break

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='Ingest CSV data to Postgres')
    parser.add_argument('--user', required=True, help='user name for postgres')
    parser.add_argument('--password', required=True, help='password for postgres')
    parser.add_argument('--host', required=True, help='host for postgres')
    parser.add_argument('--port', required=True, help='port for postgres')
    parser.add_argument('--db', required=True, help='database name for postgres')
    parser.add_argument('--table_name', required=True, help='name of the table to write results to')
    parser.add_argument('--url', required=True, help='url of the csv file')
    args = parser.parse_args()
    main(args)

解决步骤 #

1. 清理旧Docker Volume数据 #

Postgres的初始化环境变量（POSTGRES_USER、POSTGRES_PASSWORD等）仅在首次启动容器且数据目录为空时生效。如果之前创建过ingest_pgdata卷，可能保留了旧的用户配置，导致新密码不生效。

# 停止并删除容器
docker-compose down
# 删除数据卷
docker volume rm ingest_pgdata
# 重新启动容器
docker-compose up -d

2. 移除root用户启动配置 #

容器设置user: "0:0"（root用户）启动可能导致Postgres初始化时配置文件权限异常，或用户密码未正确写入。

• 修改docker-compose.yml，注释或删除user: "0:0"行
• 执行以下命令重启容器：

docker-compose down
docker volume rm ingest_pgdata
docker-compose up -d

验证正常后，可通过容器内chown命令调整数据目录权限，而非直接用root启动。

3. 验证连接字符串正确性 #

在Python脚本的engine = create_engine(...)前添加打印语句，确认连接URL是否正确拼接：

print(f"Connection URL: postgresql://{user}:{password}@{host}:{port}/{db}")

执行脚本后查看输出，确认密码是否正确传递。

4. 检查Postgres认证配置 #

进入容器查看pg_hba.conf，确保本地连接允许密码认证：

docker exec -it postgres bash
# 查看配置文件
cat /var/lib/postgresql/data/pg_hba.conf

确保本地连接（如host all all 127.0.0.1/32 scram-sha-256或md5）的认证方式为需要密码的类型，且连接来源被允许。修改后可重载配置：

psql -U postgres -c "SELECT pg_reload_conf();"

5. 直接用psql测试连接 #

跳过Python脚本，直接测试Postgres连接，定位问题是否出在脚本：

psql -U postgres -h localhost -p 5432 -d ny_taxi

输入密码postgres，若成功连接则问题在Python脚本；若失败则为Postgres服务端配置问题。

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