Error when running psql command in /docker-entrypoint-initdb.d/db_init.sh (psql: could not connect to server: Connection refused)

Question

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?

Danila Kiver · Accepted Answer · 2018-08-02 22:36:00Z

6

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.

edited Aug 2, 2018 at 22:36

answered Aug 2, 2018 at 22:05

Danila Kiver

3,8181 gold badge26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karl Keefer Over a year ago

I was bashing my head against the wall on this issue. Thanks so much for this answer.

Collectives™ on Stack Overflow

Error when running psql command in /docker-entrypoint-initdb.d/db_init.sh (psql: could not connect to server: Connection refused)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related