无法将 pgadmin 连接到在从 docker-compose 初始化的 docker 容器内运行的 postgresql

Can't connect pgadmin to postgresql running inside docker container initialized from docker-compose

提问人：Luca Esposito 提问时间：10/30/2023 最后编辑：Luca Esposito 更新时间：10/31/2023 访问量：95

问：

我无法从容器外部访问 postgresql 服务器，尽管我可以从容器内部访问。

我在compose.yaml文件中定义了以下变量：

services:
  postgres:
    container_name: postgredb
    image: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: "PostGres2023!"
      POSTGRES_DB: potato
      POSTGRES_INITDB_ARGS: --auth=scram-sha-256
    ports:
      - 5432:5432

我包括POSTGRES_INITDB_ARGS：--auth=scram-sha-256，因为我意识到我能够在没有密码的情况下从容器内部登录服务器。

C:\Users\lucab>docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS         PORTS                    NAMES
db276a10e549   postgres   "docker-entrypoint.s…"   7 seconds ago   Up 2 seconds   0.0.0.0:5432->5432/tcp   postgredb

C:\Users\lucab>docker exec -it db276a10e549 bash
root@db276a10e549:/# psql -h localhost -U postgres
psql (16.0 (Debian 16.0-1.pgdg120+1))
Type "help" for help.

同时，我尝试在本地计算机中从pgadmin登录，这产生了一个烦人的密码错误：

PG-ADMIN 服务器注册提示

在“POSTGRES_INITDB_ARGS： --auth=scram-sha-256”行之后，我被要求从容器内部输入密码，并设法使用 compose.yaml 文件上定义的值登录（请注意“用户 postgres 的密码：”行）：

C:\Users\lucab>docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED          STATUS          PORTS                    NAMES
5566cb874560   postgres   "docker-entrypoint.s…"   20 minutes ago   Up 20 minutes   0.0.0.0:5432->5432/tcp   postgredb

C:\Users\lucab>docker exec -it 5566cb874560 bash
root@5566cb874560:/# psql -h localhost -U postgres
Password for user postgres:
psql (16.0 (Debian 16.0-1.pgdg120+1))
Type "help" for help.

postgres=#

我认为这可以解决我的问题，因为 pgadmins 错误可能来自不需要密码的服务器，除非提供密码，否则 pgadmin 不会连接。显然情况并非如此。对于任何密码值，我在pgadmin中输入的错误都是相同的。如果我将密码提示留空，它会返回：

PG-ADMIN 服务器注册提示

如需进一步调试，下面是容器日志：

2023-10-29 18:56:53 The files belonging to this database system will be owned by user "postgres".
2023-10-29 18:56:53 This user must also own the server process.
2023-10-29 18:56:53 
2023-10-29 18:56:53 The database cluster will be initialized with locale "en_US.utf8".
2023-10-29 18:56:53 The default database encoding has accordingly been set to "UTF8".
2023-10-29 18:56:53 The default text search configuration will be set to "english".
2023-10-29 18:56:53 
2023-10-29 18:56:53 Data page checksums are disabled.
2023-10-29 18:56:53 
2023-10-29 18:56:53 fixing permissions on existing directory /var/lib/postgresql/data ... ok
2023-10-29 18:56:53 creating subdirectories ... ok
2023-10-29 18:56:53 selecting dynamic shared memory implementation ... posix
2023-10-29 18:56:53 selecting default max_connections ... 100
2023-10-29 18:56:53 selecting default shared_buffers ... 128MB
2023-10-29 18:56:53 selecting default time zone ... Etc/UTC
2023-10-29 18:56:53 creating configuration files ... ok
2023-10-29 18:56:53 running bootstrap script ... ok
2023-10-29 18:56:53 performing post-bootstrap initialization ... ok
2023-10-29 18:56:54 syncing data to disk ... ok
2023-10-29 18:56:54 
2023-10-29 18:56:54 Success. You can now start the database server using:
2023-10-29 18:56:54 
2023-10-29 18:56:54     pg_ctl -D /var/lib/postgresql/data -l logfile start
2023-10-29 18:56:54 
2023-10-29 18:56:54 waiting for server to start....2023-10-29 21:56:54.105 UTC [48] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-10-29 18:56:54 2023-10-29 21:56:54.108 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-10-29 18:56:54 2023-10-29 21:56:54.115 UTC [51] LOG:  database system was shut down at 2023-10-29 21:56:53 UTC
2023-10-29 18:56:54 2023-10-29 21:56:54.119 UTC [48] LOG:  database system is ready to accept connections
2023-10-29 18:56:54  done
2023-10-29 18:56:54 server started
2023-10-29 18:56:54 CREATE DATABASE
2023-10-29 18:56:54 
2023-10-29 18:56:54 
2023-10-29 18:56:54 /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2023-10-29 18:56:54 
2023-10-29 18:56:54 waiting for server to shut down....2023-10-29 21:56:54.307 UTC [48] LOG:  received fast shutdown request
2023-10-29 18:56:54 2023-10-29 21:56:54.310 UTC [48] LOG:  aborting any active transactions
2023-10-29 18:56:54 2023-10-29 21:56:54.311 UTC [48] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
2023-10-29 18:56:54 2023-10-29 21:56:54.311 UTC [49] LOG:  shutting down
2023-10-29 18:56:54 2023-10-29 21:56:54.314 UTC [49] LOG:  checkpoint starting: shutdown immediate
2023-10-29 18:56:54 2023-10-29 21:56:54.388 UTC [49] LOG:  checkpoint complete: wrote 923 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.014 s, sync=0.054 s, total=0.077 s; sync files=301, longest=0.015 s, average=0.001 s; distance=4257 kB, estimate=4257 kB; lsn=0/19130D0, redo lsn=0/19130D0
2023-10-29 18:56:54 2023-10-29 21:56:54.393 UTC [48] LOG:  database system is shut down
2023-10-29 18:56:54  done
2023-10-29 18:56:54 server stopped
2023-10-29 18:56:54 
2023-10-29 18:56:54 PostgreSQL init process complete; ready for start up.
2023-10-29 18:56:54 
2023-10-29 18:56:54 2023-10-29 21:56:54.428 UTC [1] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-10-29 18:56:54 2023-10-29 21:56:54.428 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-10-29 18:56:54 2023-10-29 21:56:54.428 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-10-29 18:56:54 2023-10-29 21:56:54.433 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-10-29 18:56:54 2023-10-29 21:56:54.439 UTC [64] LOG:  database system was shut down at 2023-10-29 21:56:54 UTC
2023-10-29 18:56:54 2023-10-29 21:56:54.444 UTC [1] LOG:  database system is ready to accept connections
2023-10-29 18:57:46 2023-10-29 21:57:46.709 UTC [77] FATAL:  password authentication failed for user "postgres"
2023-10-29 18:57:46 2023-10-29 21:57:46.709 UTC [77] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 115: "host    all             all             127.0.0.1/32            scram-sha-256"
2023-10-29 18:58:01 2023-10-29 21:58:01.424 UTC [81] FATAL:  password authentication failed for user "postgres"
2023-10-29 18:58:01 2023-10-29 21:58:01.424 UTC [81] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 115: "host    all             all             127.0.0.1/32            scram-sha-256"
2023-10-29 19:01:54 2023-10-29 22:01:54.538 UTC [62] LOG:  checkpoint starting: time
2023-10-29 19:01:58 2023-10-29 22:01:58.789 UTC [62] LOG:  checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.217 s, sync=0.022 s, total=4.251 s; sync files=12, longest=0.008 s, average=0.002 s; distance=261 kB, estimate=261 kB; lsn=0/1954540, redo lsn=0/1954508
PostgreSQL docker-compose pgadmin

评论

0赞 jjanes 10/30/2023
当我运行您的 docker-compose 文件时，一切都适用于我通过 pgAdmin、主机上的命令行或通过 docker exec 登录。但是，我得到的是 v15.1，而不是 v16.0。我不是一个 docker 用户，知道为什么我们在运行同一个文件时没有得到相同的版本吗？
0赞 Luca Esposito 10/30/2023
也许当您运行 docker compose 时，它使用的是 postgres 的 15.1 版，因为您已经在 docker images 上下载了具有该版本的映像。由于我没有任何图像，因此它正在拉取最新版本。当你执行docker compose时，它是否说它正在拉postgres？
0赞 jjanes 10/31/2023
啊，我以为它会自动检测较新的可用图像并下载它。删除所有映像（并重新安装 docker desktop，不知何故搞砸了）后，我现在得到了 16.0，它可以工作了。我没有看到任何可能导致该错误消息的问题的可能候选者。我想我会更改密码，这样它就没有感叹号，看看它是否有效（但我使用了与您相同的密码，所以我不明白为什么这对您而不是我来说是个问题）
0赞 Luca Esposito 10/31/2023
您是否从 linux 操作系统运行 docker？我忘了提到我使用的是 Windows 11 操作系统。我在这里读过：docs.docker.com/network/network-tutorial-host 主机网络只能在 linux 上运行。也许这意味着端口映射到本地主机也不应该起作用？

答：

0赞 Luca Esposito 10/31/2023 #1

默认情况下，Postgres 配置为侦听端口 5432。这意味着，在端口配置中，如果要将本地计算机连接到 postgres docker 容器，则本地计算机将通过 localhost 访问的 postgres docker 容器的端口必须为 5432。除非更改了 docker postgres 中的端口配置，否则这仍然适用。本地计算机中的可用端口可以是任何端口。这导致我更改了compose.yaml中的端口行：

services:
  postgres:
    container_name: postgredb
    image: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: "PostGres2023!"
      POSTGRES_DB: potato
    ports:
      - 8039:5432

这导致在 psql 中成功连接：

Server [localhost]:
Database [postgres]:
Port [5432]: 8039
Username [postgres]:
Password for user postgres:
psql (16.0)
WARNING: Console code page (437) differs from Windows code page (1252)
     8-bit characters might not work correctly. See psql reference
     page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

评论

0赞 jjanes 11/2/2023
这是我的第一个想法，但由于您的日志文件显示连接尝试失败，因此人们一定能够通过，所以我拒绝了这个理论。也许那些失败的尝试来自你这样做的时候？在 Linux 上，当您尝试从主机端口设置映射时，该端口已在使用中，docker 将拒绝启动容器。但是在这种情况下，在窗口中，它只是忽略尝试的映射并继续进行。更糟糕的是，实际上显示映射是否有效，而实际上它没有生效。docker execdocker ps

上一个：pgAdmin 4 运行时环境导入_greenlet时出错

下一个：使用转储文件时出现 PostgreSQL 语法错误