Configure postgres in a dockerfile

Question

I'm feeling a bit stupid, but...

All I want is a dockerfile that allows me to configure the db and the user to replace a script like

docker run --name $CONTAINER_NAME  \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=mypass \
  -e POSTGRES_DB=mydb \
  -d postgres:13.5-alpine

###
### artificial wait here
###

docker exec $CONTAINER_NAME bash -c "psql --username postgres --command \"CREATE USER foo WITH SUPERUSER ENCRYPTED PASSWORD 'bar';\""

I.e. something like this (I thought):

FROM postgres:13.5-alpine

EXPOSE 5432

ENV POSTGRES_DB mydb
ENV POSTGRES_PASSWORD mypass

CMD psql --username postgres --command "CREATE USER foo WITH SUPERUSER ENCRYPTED PASSWORD 'bar';"

except this results in

psql: error: 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"?

So what's the proper way to do this?

postgresql default image supports a variable POSTGRES_USER, not sure if it is what you need. see more there https://hub.docker.com/_/postgres section "how to extend this image" — Sombriks, Aug 23 '22 at 19:27
Alternatively, put the SQL command(s) in a `/docker-entrypoint-initdb.d/something.sql` file, and the standard PostgreSQL image will run it automatically the first time the container is started. — David Maze, Aug 24 '22 at 13:00

score 0 · Answer 1 · answered Aug 23 '22 at 20:07

The Dockerfile is for building the image and the CMD will run when the container is ran. So the reason that

docker run --name $CONTAINER_NAME  \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=mypass \
  -e POSTGRES_DB=mydb \
  -d postgres:13.5-alpine

###
### artificial wait here
###

docker exec $CONTAINER_NAME bash -c "psql --username postgres --command \"CREATE USER foo WITH SUPERUSER ENCRYPTED PASSWORD 'bar';\""

this worked is because the container (db) already is up and running before you input the SQL query into it. But if you try to do it from the Dockerfile it literally will try to run the psql command before the db is up and running, hence the connection error.

Your best bet is to use Docker Compose and add an initialize script

version: 3
services: 
  db:
     image: postgres:13.5-alpine
     environment:
                POSTGRES_PASSWORD:mypass
                POSTGRESS_DB: mydb
     volume:
           data:/var/lib/postgresql/data
  volumes:
         data:

Something like this(Excuse some typos. I'm typing from memory here.

See https://hub.docker.com/_/postgres for more info on initialising scripts

But it's basically a bash script you can write and then pass to docker compose to run upon container startup.

Configure postgres in a dockerfile

1 Answers1