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

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

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:

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
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
2023-05-28 14:56:30 2023-05-28 18:56:30.313 UTC [1] LOG:  listening on IPv6 address "::", port 5432
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"
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
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:

# psql -p 5432 -U postgres
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)

postgres=# 

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

"NetworkSettings": {
            "Bridge": "",
            "SandboxID": "7752ebc73909a6a14039b287472724b48fb8138d4466a41eb368fd91057dbcbc",
            "HairpinMode": false,
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "Ports": {
                "5432/tcp": [
                    {
                        "HostIp": "0.0.0.0",
                        "HostPort": "54320"
                    }
                ]
            },
            "SandboxKey": "/var/run/docker/netns/7752ebc73909",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "EndpointID": "a00a861f13fb77a019123b42affeed42f11a8cac8ab00f609b6b2cbfc76e4ee6",
            "Gateway": "172.17.0.1",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.3",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
            "MacAddress": "02:42:ac:11:00:03",
            "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "NetworkID": "a28aecb0b5fd55718069e079194fcf39c84a380041a82f0ec6de8f7b3565dd6a",
                    "EndpointID": "a00a861f13fb77a019123b42affeed42f11a8cac8ab00f609b6b2cbfc76e4ee6",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.3",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:11:00:03",
                    "DriverOpts": null
                }
            }
        }

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.

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

答案1

得分: 1

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

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

你可能需要像这样配置它

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

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

you may need to configure it like

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
匿名

发表评论

匿名网友

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

确定