How to connect to postgres by psql (-h localhost -p 5432) when docker container starts

Question

I have some docker-compose.yml file. In this file is defined db service (postgres:9.6):

db:
image: postgres:9.6
volumes:
  - ./test/data/postgresql/:/var/lib/postgresql/data:delegated
  - ./test/bootstrap/postgres:/docker-entrypoint-initdb.d:delegated
ports:
  - 15432:5432
environment:
  - POSTGRES_DB=test
  - POSTGRES_USER=test
  - PGDATA=/var/lib/postgresql/data/testdata

And some sh script located in ./test/bootstrap/postgres. In this script i try to connect to postgres by psql like that: psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -h localhost -p 5432 (I know that -h localhost -p 5432 is not necessary but i need it).
When my container is up error occured:

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?

But after this i restart (after first start this script not executed) my db container then inside in container manually run sh script and it executed successfully. It can connect to localhost and port 5432 even if it is first start.

If i connect in script like that psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" there is no connection error.

Can someone explain me this strange behavior? Why i can't connect to localhost:5432 when container is starting but if connect manually from container it connect successfully?

Thanks!

Can you use Unix socket to connect to postgres or do you need to use port 5432? — whites11
– whites11, Commented May 30, 2018 at 20:47
based on your docker-compose.yml, you are forwarding localhost: 15432 to container:5432. So try ... -h localhost -p 15432. — Robert Ranjan
– Robert Ranjan, Commented May 31, 2018 at 2:18
@RobertRanjan port 15432 available to connection outside the container but inside 5432 and my problem is that connection refeused when container first starts but if in the second start i manually execute script it connect successfully by localhost:5432 — V. Kuznetsov
– V. Kuznetsov, Commented May 31, 2018 at 5:55

b0gusb · Accepted Answer · 2018-05-31 14:11:17Z

1

My understanding is that during the initialization of the container the server is listening on Unix-domain socket.

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

The scripts in the docker-entrypoint-init.d directory are executed while the server runs in this mode. You're trying to connect via TCP/IP by specifying -h localhost. Try to remove that argument to connect to the server using Unix sockets.

answered May 31, 2018 at 14:11

b0gusb

4,8492 gold badges19 silver badges34 bronze badges

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

Comments

Kilian · Accepted Answer · 2018-05-31 14:20:54Z

0

try to add a sleep command in your script to give enougth time to your database to start correctly.

answered May 31, 2018 at 14:20

Kilian

1,80115 silver badges22 bronze badges

Comments

Douglas Bett · Accepted Answer · 2020-01-10 18:24:20Z

According to docs at hub.docker.com/_/postgres, to add a custom script to run when your container starts, create a shell script like the one below and create a custom Dockerfile where you'll copy the shell script into /docker-entrypoint-initdb.d/

Assuming you save the following script as initdatabase.sh in the same folder as your custom Dockerfile also defined below in example, when you build the image, you'll have created your custom image which runs the following sql statements defined below.

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER user;
    CREATE DATABASE customdatabase;
    GRANT ALL PRIVILEGES ON DATABASE bloodapp TO user;
    // add other sql statements here
EOSQL

FROM mdillon/postgis:9.6-alpine

COPY initdatabase.sh /docker-entrypoint-initdb.d/

Collectives™ on Stack Overflow

How to connect to postgres by psql (-h localhost -p 5432) when docker container starts

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related