使用 Docker Desktop 中的 PGAdmin 扩展来连接 PostgreSQL 镜像时出现连接问题。

huangapple
go评论96阅读模式
英文:

Using PGAdmin Extension in Docker Desktop for PostgreSQL Image: can't connect

问题

这是一个关于Docker和PGAdmin连接到PostgreSQL容器的问题。用户已经创建了一个PostgreSQL容器，并且容器内部的PostgreSQL数据库已经启动并且能够在终端中成功连接。然而，用户无法使用PGAdmin连接到这个PostgreSQL实例。

用户尝试了多种连接方式，包括使用本地IP地址、172.17.0.3 IP地址以及容器名称（pgserver）作为主机，但都遇到了连接被拒绝或连接超时的错误。

用户还提到他们已经安装了PGAdmin4 Docker Desktop扩展，并且确保SSL模式设置为"prefer"。

最后，用户提供了docker ps命令的输出，显示容器已经在端口54320上映射到PostgreSQL的5432端口。

用户想知道他们是否做错了什么，导致无法通过PGAdmin连接到PostgreSQL容器。

请注意，这是一个技术支持问题，需要进一步的调查和分析才能找到解决方法。

英文:

Question seems identical to https://stackoverflow.com/questions/31798160/can-not-connect-to-postgres-container-from-pgadmin but none of those solutions seem to be working, and I'm using slightly different tools... so maybe different problem?

In my Docker Desktop (running Windows with a Linux Docker), standard install, I've installed the postgres:latest image and created a container from it. I used the following settings:

使用 Docker Desktop 中的 PGAdmin 扩展来连接 PostgreSQL 镜像时出现连接问题。

The log in the container shows success:

  1. 2023-05-28 14:56:30 2023-05-28 18:56:30.313 UTC [1] LOG:  starting PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
  2. 2023-05-28 14:56:30 2023-05-28 18:56:30.313 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
  3. 2023-05-28 14:56:30 2023-05-28 18:56:30.313 UTC [1] LOG:  listening on IPv6 address "::", port 5432
  4. 2023-05-28 14:56:30 2023-05-28 18:56:30.319 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
  5. 2023-05-28 14:56:30 2023-05-28 18:56:30.325 UTC [62] LOG:  database system was shut down at 2023-05-28 18:56:30 UTC
  6. 2023-05-28 14:56:30 2023-05-28 18:56:30.329 UTC [1] LOG:  database system is ready to accept connections

On the terminal tab, I'm able to connect to the local instance:

  1. # psql -p 5432 -U postgres
  2. psql (15.3 (Debian 15.3-1.pgdg110+1))
  3. Type "help" for help.
  5. postgres=# \l
  6.                                                 List of databases
  7.    Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
  8. -----------+----------+----------+------------+------------+------------+-----------------+-----------------------
  9.  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
  10.  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
  11.            |          |          |            |            |            |                 | postgres=CTc/postgres
  12.  template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
  13.            |          |          |            |            |            |                 | postgres=CTc/postgres
  14. (3 rows)
  16. postgres=#

I did a docker inspect pgserver and saw the network settings as such:

  1. "NetworkSettings": {
  2.             "Bridge": "",
  3.             "SandboxID": "7752ebc73909a6a14039b287472724b48fb8138d4466a41eb368fd91057dbcbc",
  4.             "HairpinMode": false,
  5.             "LinkLocalIPv6Address": "",
  6.             "LinkLocalIPv6PrefixLen": 0,
  7.             "Ports": {
  8.                 "5432/tcp": [
  9.                     {
  10.                         "HostIp": "0.0.0.0",
  11.                         "HostPort": "54320"
  12.                     }
  13.                 ]
  14.             },
  15.             "SandboxKey": "/var/run/docker/netns/7752ebc73909",
  16.             "SecondaryIPAddresses": null,
  17.             "SecondaryIPv6Addresses": null,
  18.             "EndpointID": "a00a861f13fb77a019123b42affeed42f11a8cac8ab00f609b6b2cbfc76e4ee6",
  19.             "Gateway": "172.17.0.1",
  20.             "GlobalIPv6Address": "",
  21.             "GlobalIPv6PrefixLen": 0,
  22.             "IPAddress": "172.17.0.3",
  23.             "IPPrefixLen": 16,
  24.             "IPv6Gateway": "",
  25.             "MacAddress": "02:42:ac:11:00:03",
  26.             "Networks": {
  27.                 "bridge": {
  28.                     "IPAMConfig": null,
  29.                     "Links": null,
  30.                     "Aliases": null,
  31.                     "NetworkID": "a28aecb0b5fd55718069e079194fcf39c84a380041a82f0ec6de8f7b3565dd6a",
  32.                     "EndpointID": "a00a861f13fb77a019123b42affeed42f11a8cac8ab00f609b6b2cbfc76e4ee6",
  33.                     "Gateway": "172.17.0.1",
  34.                     "IPAddress": "172.17.0.3",
  35.                     "IPPrefixLen": 16,
  36.                     "IPv6Gateway": "",
  37.                     "GlobalIPv6Address": "",
  38.                     "GlobalIPv6PrefixLen": 0,
  39.                     "MacAddress": "02:42:ac:11:00:03",
  40.                     "DriverOpts": null
  41.                 }
  42.             }
  43.         }

I have also installed the PGAdmin4 Docker Desktop extension, but I cannot configure it to connect to my postgres instance.

  • I have tried to connect to 127.0.0.1 on ports 5432 and 54320.
  • I have tried to connect to 172.17.0.3 on the same ports.
  • I have tried to connect to pgserver on the same ports.
  • I have tried to connect to localhost on the same ports.
  • I have ensured that SSL Mode is set to "prefer".

Depending on what I use, I get an error message that the connection is refused, or a connection timeout error.

What am I doing wrong?

Per request in comments, results of docker ps are below.

  1. docker ps
  2. CONTAINER ID   IMAGE             COMMAND                  CREATED        STATUS        PORTS                     NAMES
  3. 0ed9f3f293fd   postgres:latest   "docker-entrypoint.s…"   20 hours ago   Up 20 hours   0.0.0.0:54320->5432/tcp   pgserver

答案1

得分: 1

我有一种感觉，问题不在于Docker或端口配置
而是特别是PostgreSQL的配置，其中定义了允许的连接位于

  1. /var/lib/pgsql/12/data/pg_hba.conf

你可能需要像这样配置它

  1. host    all             all             0.0.0.0/0            md5

你需要以一种通过卷保持持久性的方式来执行此操作。

英文:

I have a feeling that the problem is not docker or the port configuration
But the configuration of postgress in particular what connection are allowed defined in the

  1. /var/lib/pgsql/12/data/pg_hba.conf

you may need to configure it like

  1. host    all             all             0.0.0.0/0            md5

you need to do this in way the is persistent via volumes

huangapple
  • 本文由 发表于 2023年5月29日 03:08:54
  • 转载请务必保留本文链接：https://go.coder-hub.com/76353186.html
  • docker
  • pgadmin
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定