Windows本地无法连接PostgreSQL Docker容器问题求助

问题背景 #

我自行查阅了以下相关资料尝试解决问题，但仍未成功：

• Docker环境下NodeJS连接PostgreSQL出现ECONNREFUSED错误
• 因不支持认证类型10无法连接PostgreSQL数据库
• 无法从外部连接Docker中的PostgreSQL

尝试了多种步骤后，还是无法通过Python脚本连接Docker容器中的PostgreSQL。

Docker Compose配置 #

version: "3.8"

services:

  postgres:
    image: postgres:14
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=root
      - POSTGRES_PASSWORD=root
      - POSTGRES_DB=project1

  pgadmin:
    image: dpage/pgadmin4
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=adminadmin
    ports:
      - "5050:80"
    restart: always
    depends_on:
      - postgres

执行命令启动容器：

docker-compose up

Python脚本代码 #

import psycopg2

conn = psycopg2.connect(
    host="host.docker.internal",
    database="project1",
    user="root",
    password="root",
    port=5432
)

print(conn)

# 创建游标
cur = conn.cursor()

# 执行查询语句
print('PostgreSQL数据库版本:')
result = cur.execute('SELECT version()')
print(result)

更换Host参数后的错误信息 #

使用host.docker.internal #

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "host.docker.internal" (192.168.1.145), port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.1.145", user "root", database "project1", SSL off

使用localhost #

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "root"

使用0.0.0.0 #

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "0.0.0.0", port 5432 failed: Cannot assign requested address (0x00002741/10049)
        Is the server running on that host and accepting TCP/IP connections?

补充说明 #

Docker服务运行正常，我可以正常连接PGAdmin：

请问有什么解决思路吗？

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