在/docker-entrypoint-initdb.d/db_init.sh中运行psql命令时出错（psql：无法连接到服务器：连接被拒绝） [英] Error when running psql command in /docker-entrypoint-initdb.d/db_init.sh (psql: could not connect to server: Connection refused)

问题描述

我曾经使用脚本（ /docker-entrypoint-initdb.d/db_init.sh ）遍历复制到postgres容器中的文件夹中的数据库转储，然后恢复它们。它曾经很好地工作，但是最近它停止了工作。

我遇到以下错误：

  postgres_server_1 | /usr/local/bin/docker-entrypoint.sh：运行/docker-entrypoint-initdb.d/db_init.sh 
 postgres_server_1 | -> db_init.sh：找到/dumps/dataset_1.dump作为数据集_1 
 postgres_server_1 | psql：无法连接到服务器：连接被拒绝
 postgres_server_1 |服务器是否在主机 localhost（127.0.0.1）上运行并接受
 postgres_server_1 |端口5432上的TCP / IP连接？ 
  

db_init.sh 脚本通过包含数据库转储的文件夹，并检查数据库是否已经存在。如果没有，它将恢复转储。

/docker-entrypoint-initdb.d/db_init.sh 内容：

  shopt -s nullglob 
 for /dumps/*.dump中的i； 
 do 
 db_name = $ {i ## * /} 
 db_name = $ {db_name％.dump} 
 echo-> db_init.sh：找到$ i为$ db_name 
 
，如果psql -U postgres -h localhost -lqt |切-d \ | -f 1 | grep -qw $ {db_name};然后
 echo-> db_init.sh：数据库$ {db_name}已经存在。 
 else 
 echo-> db_init.sh：设置数据库：$ {db_name} 
 createdb -U postgres -h localhost-T template0 $ {db_name} 
 psql -U postgres -h本地主机$ {db_name}< $ {i} 
 fi 
完成
 echo-> db_init.sh：安装完成。 
  

我正在使用docker-compose启动postgres容器（以及其他一些容器）。

docker-compose.yml 内容：

 版本：'3'
 
服务：
 postgres_server：
图片：postgres 
数量：
-/ etc / localtime： / etc / localtime：ro 
-./data/dumps:/dumps:ro 
-./scripts/db_init.sh:/docker-entrypoint-initdb.d/db_init.sh 
环境：
-TZ =欧洲/柏林
重新启动：始终
 
卷：
 postgres_data：
驱动程序：本地
  

现在我不明白的是为什么似乎存在连接错误，通常与尝试从另一个数据库连接到Postgres数据库有关机器或容器。但是脚本本身在postgres容器中运行，并且一个卷将包含转储的目录连接到容器中。
使用 docker exec -it container_name bash 在容器内运行 psql 命令可以正常工作，并且转储在那里。从容器中手动执行 psql 命令为什么起作用，但通过 /docker-entrypoint-initdb.d/db_init.sh执行时却不起作用？

解决方案

它看起来像此提交破坏了您的脚本。

$ b $ p>

PostgreSQL不仅可以通过TCP / IP接受连接，还可以通过UNIX套接字接受连接。 -h localhost 参数告诉 psql 使用TCP连接而不是UNIX套接字。

如果您查看当前的 docker-entrypoint.sh 版本，您将看到，在 /docker-entrypoint-initdb.d 中执行脚本的过程中，PostgreSQL仅在UNIX上侦听套接字，启动日志显示：

 日志：监听Unix套接字 /var/run/postgresql/.s.PGSQL .5432 
  

这意味着 psql -h localhost 将不会连接到数据库，因为PostgreSQL不监听IP套接字。您必须使用 psql without -h localhost 选项使其使用UNIX套接字而不是TCP连接。

但是为什么要手动运行 psql -h localhost 呢？

如果再次查看 docker-entrypoint.sh ，您将看到在执行所有初始化脚本时，PostgreSQL正在已停止，然后再次以正常（运行）模式启动，在该模式下，它同时监听UNIX和IP套接字：

  LOG：侦听IPv4地址 0.0.0.0，端口5432 
 LOG：侦听IPv6地址 ::，端口5432 
日志：在Unix套接字上侦听 /var/run/postgresql/.s.PGSQL.5432 
  

因此，启动过程完成后，您可以使用TCP连接连接到PostgreSQL，从而进入容器并运行 psql -h localhost 成功。

I used to use a script (/docker-entrypoint-initdb.d/db_init.sh) to loop through database dumps in a folder copied into the postgres container and restoring them. It used to work nicely but recently it stopped working.

I get following error:

postgres_server_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/db_init.sh
postgres_server_1  | --> db_init.sh: Found /dumps/dataset_1.dump as dataset_1
postgres_server_1  | psql: could not connect to server: Connection refused
postgres_server_1  |    Is the server running on host "localhost" (127.0.0.1) and accepting
postgres_server_1  |    TCP/IP connections on port 5432?

The db_init.sh script loops through a folder containing database dumps and checks if the database exists already. If not it restores the dump.

/docker-entrypoint-initdb.d/db_init.sh content:

shopt -s nullglob
for i in /dumps/*.dump;
do
    db_name=${i##*/}
    db_name=${db_name%.dump}
    echo "--> db_init.sh: Found $i as $db_name"

    if psql -U postgres -h localhost -lqt | cut -d \| -f 1 | grep -qw ${db_name}; then
        echo "--> db_init.sh: Database ${db_name} already exists."
    else
        echo "--> db_init.sh: Setting up database: ${db_name}"
        createdb -U postgres -h localhost-T template0 ${db_name}
        psql -U postgres -h localhost ${db_name} < ${i}
    fi
done
echo "--> db_init.sh: Setup complete."

I am using docker-compose to start the postgres container (and some others).

The docker-compose.yml content:

version: '3'

services:
  postgres_server:
    image: postgres
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - ./data/dumps:/dumps:ro
      - ./scripts/db_init.sh:/docker-entrypoint-initdb.d/db_init.sh
    environment:
      - TZ=Europe/Berlin
    restart: always

volumes:
  postgres_data:
    driver: local

Now what I don't understand is why there seems to be a connection error usually associated with trying to connect to a postgres database from a different machine or container. But the script itself is running in the postgres container and a volume connects the directory containing the dumps into the container. Running the psql command from within the container using docker exec -it container_name bash works fine and the dumps are there. Why do the psql commands work when executing them manually from within the container but not when executed via /docker-entrypoint-initdb.d/db_init.sh?

解决方案

It looks like this commit has broken your script.

Explanation:

PostgreSQL may accept connections not only via TCP/IP, but also via UNIX socket. The -h localhost argument tells psql to use TCP connections rather than UNIX socket.

If you look into the current docker-entrypoint.sh version, you will see, that during the execution of scripts in /docker-entrypoint-initdb.d PostgreSQL listens only on the UNIX socket, and the startup log says:

LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

This means that psql -h localhost will not connect to the database, as PostgreSQL does not listen on IP socket. You must use psql without -h localhost option to make it use UNIX socket instead of TCP connections.

But why running psql -h localhost manually works?

If you look into the docker-entrypoint.sh again, you will see that when all init scripts are executed, PostgreSQL is being stopped and then started again in normal (operational) mode, in which it listens both on UNIX and IP sockets:

LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  listening on IPv6 address "::", port 5432
LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

So, when the startup process is complete, you may connect to PostgreSQL using TCP connections, thus, entering into the container and running psql -h localhost succeeds.

这篇关于在/docker-entrypoint-initdb.d/db_init.sh中运行psql命令时出错（psql：无法连接到服务器：连接被拒绝）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！