我只需要运行这个脚本文件一次吗？

Question

kshnkvn

Asked: 2020-02-09 06:25:04 +0800 CST2020-02-09 06:25:04 +0800 CST 2020-02-09 06:25:04 +0800 CST

为什么我无法连接到 Docker 中的 Postgres？

772

我使用了Openpoiservice 项目中的 docker -compose。两个 Docker 容器均已成功启动。

kshnkvn@kshnkvn-vb:~$ docker ps
CONTAINER ID        IMAGE                           COMMAND                  CREATED             STATUS              PORTS                    NAMES
10fafbab73dc        openpoiservice_gunicorn_flask   "/ops_venv/bin/gunic…"   23 minutes ago      Up 22 minutes       0.0.0.0:5000->5000/tcp   openpoiservice_gunicorn_flask_1
a66fe5691455        kartoza/postgis:11.0-2.5        "/bin/sh -c /docker-…"   23 minutes ago      Up 22 minutes       5432/tcp                 openpoiservice_psql_postgis_db_1

但是在尝试检查服务的功能时，它无法连接到数据库。我尝试手动进行：

kshnkvn@kshnkvn-vb:~$ docker exec -it 10fafbab73dc /bin/bash
root@10fafbab73dc:/deploy/app# psql -h localhost -U gis_admin-gis
psql: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Cannot assign requested address
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
root@10fafbab73dc:/deploy/app#

奇怪，检查以防容器网络的类型是网桥：

kshnkvn@kshnkvn-vb:~$ docker network ls
NETWORK ID          NAME                         DRIVER              SCOPE
81001dac99c0        bridge                       bridge              local
8e65fb4ef6f8        host                         host                local
94ce4e1605ef        none                         null                local
a3f48ac3facc        openpoiservice_default       bridge              local
e3d4286df013        openpoiservice_poi_network   bridge              local

检查 Postgres 启动日志：

kshnkvn@kshnkvn-vb:~$ docker logs a66fe5691455
Add rule to pg_hba: 0.0.0.0/0
Add rule to pg_hba: replication replicator 
Setup master database
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
2020-02-08 13:50:20.675 UTC [25] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-08 13:50:20.683 UTC [25] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-08 13:50:20.756 UTC [37] LOG:  database system was interrupted; last known up at 2020-02-08 13:35:17 UTC
2020-02-08 13:50:21.830 UTC [48] postgres@postgres FATAL:  the database system is starting up
psql: FATAL:  the database system is starting up
2020-02-08 13:50:22.726 UTC [37] LOG:  database system was not properly shut down; automatic recovery in progress
2020-02-08 13:50:22.730 UTC [37] LOG:  redo starts at 0/21CCC50
2020-02-08 13:50:22.730 UTC [37] LOG:  invalid record length at 0/21CCC88: wanted 24, got 0
2020-02-08 13:50:22.730 UTC [37] LOG:  redo done at 0/21CCC50
2020-02-08 13:50:22.867 UTC [25] LOG:  database system is ready to accept connections
                              List of databases
   Name    |   Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+-----------+----------+---------+---------+-----------------------
 gis       | gis_admin | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres  | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
 template1 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
(4 rows)

postgres ready
Setup postgres User:Password
Creating superuser gis_admin
ALTER ROLE
Creating replication user replicator
ALTER ROLE
gis db already exists
                              List of databases
   Name    |   Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+-----------+----------+---------+---------+-----------------------
 gis       | gis_admin | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres  | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
 template1 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
(4 rows)

2020-02-08 13:50:24.785 UTC [25] LOG:  received smart shutdown request
2020-02-08 13:50:24.799 UTC [25] LOG:  background worker "logical replication launcher" (PID 58) exited with exit code 1
2020-02-08 13:50:24.801 UTC [53] LOG:  shutting down
2020-02-08 13:50:24.838 UTC [25] LOG:  database system is shut down
Postgres initialisation process completed .... restarting in foreground
2020-02-08 13:50:25.842 UTC [148] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-02-08 13:50:25.842 UTC [148] LOG:  listening on IPv6 address "::", port 5432
2020-02-08 13:50:25.850 UTC [148] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-08 13:50:25.880 UTC [150] LOG:  database system was shut down at 2020-02-08 13:50:24 UTC
2020-02-08 13:50:25.887 UTC [148] LOG:  database system is ready to accept connections

看起来 Postgres 从 IP 地址 0.0.0.0 开始。

我查看了 Dockerip addr show命令使用了哪些 IP。尝试使用此 IP 重新连接：

psql: could not connect to server: Connection refused
    Is the server running on host "172.17.0.1" and accepting
    TCP/IP connections on port 5432?
root@10fafbab73dc:/deploy/app# psql -h 172.17.255.255 -U gis_admin-gis
psql: could not connect to server: Connection timed out
    Is the server running on host "172.17.255.255" and accepting
    TCP/IP connections on port 5432?

我可以尝试做些什么来将脚本连接到数据库？

2 个回答

Voted

Zeitounator · Answer 1 · 2020-02-09T10:04:31+08:00

TLDR；

psql -h psql_postgis_db -U gis_admin gis
# or
psql -h psql_postgis_db gis gis_admin

服务器地址有问题

您尝试的所有 IP 都不正确。

127.0.0.1 是本地主机地址。由于您从烧瓶容器启动命令，因此没有运行 posgres 服务。
172.17.0.1 是 docker 网桥的 ip。这实际上是您的 docker 引擎主机的 ip，正如您在同一网桥上的容器所看到的那样。除非你在你的机器上运行一个 postgres 并监听那个 ip，否则你不会得到任何答案（而且这无论如何都不是正确的 postgres 服务器）
172.17.255.255是之前桥接网络的网络广播地址。

从您的启动日志中，我们可以看到您的 postgres 应该正确监听。0.0.0.0 实际上并不是真正的 ip：它代表“在此主机上配置的任何 ip”。

您可以查找您的 postgres 容器的 ip 来联系它（例如参见SO 上的这个答案），但您甚至不必这样做。docker/docker-compose 通过自动将同一网络上的容器/服务名称映射到它们各自的 IP，使您可以轻松完成此操作。所以您的数据库服务器可以使用服务名称访问psql_postgis_db

用户名和数据库名有问题

我真的不明白你在命令-U选项中写的内容psql（用户+数据库名称的混合......）。无论如何，它应该是你想用来连接到 postgres 服务器的用户名。从您的撰写文件中，它是gis_admin. 由于没有与用户同名的数据库，因此您需要指定要连接的数据库名称。您可以使用-Uoption( psql -U <user> <db>) 或使用位置参数 ( psql <db> <user>)

Lazor · Answer 2 · 2021-03-26T16:49:08+08:00

Lazor

2021-03-26T16:49:08+08:002021-03-26T16:49:08+08:00

只需将端口参数添加到 docker-compose.yml。

例子：

# Use postgres/example user/password credentials
version: '3.1'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: example
    ports:
      - "5432:5432"

1

为什么我无法连接到 Docker 中的 Postgres？

TLDR；

服务器地址有问题

用户名和数据库名有问题

模块 i915 可能缺少固件 /lib/firmware/i915/*

无法获取 jessie backports 存储库

如何将 GPG 私钥和公钥导出到文件

我们如何运行存储在变量中的命令？

如何配置 systemd-resolved 和 systemd-networkd 以使用本地 DNS 服务器来解析本地域和远程 DNS 服务器来解析远程域？

dist-upgrade 后 Kali Linux 中的 apt-get update 错误 [重复]

如何从 systemctl 服务日志中查看最新的 x 行

Nano - 跳转到文件末尾

grub 错误：你需要先加载内核

如何下载软件包而不是使用 apt-get 命令安装它？

为什么我无法连接到 Docker 中的 Postgres？

2 个回答

TLDR；

服务器地址有问题

用户名和数据库名有问题

相关问题