Docker: can't RUN psql commands from Dockerfile

Question

I have a Python/PostgreSQL project I'm trying to put in a Docker container. When I try to run psql commands inside the container to create a user and a database while building the image, I get this error:

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"?

Here's one Dockerfile:

FROM python:2.7-slim

WORKDIR /app

ADD . /app

RUN apt-get update && apt-get install -y postgresql postgresql-contrib libgeos-c1

RUN service postgresql start

RUN su postgres bash -c "psql -c \"CREATE USER myuser WITH PASSWORD 'password';\"" \
   && su postgres bash -c "psql -c \"CREATE DATABASE db WITH OWNER = myuser;\""```

Notice that I even try to start the postgresql service before psql commands.

I tried another approach which is to use base image postgres:9.6.3 and install Python pip on it instead. I still get the same error. Here's the second Dockerfile for reference:

FROM postgres:9.6.3

WORKDIR /app

ADD . /app

RUN apt-get update && apt-get install -y python-pip libgeos-c1

RUN service postgresql start

RUN su postgres bash -c "psql -c \"CREATE USER myuser WITH PASSWORD 'password';\"" \
   && su postgres bash -c "psql -c \"CREATE DATABASE db WITH OWNER = myuser;\""

Confession: I'm a docker noob.

Similar to this? https://stackoverflow.com/questions/35928670/docker-container-for-postgres-9-1-not-exposing-port-5432-to-host — GregHNZ, Jun 10 '17 at 22:05
@GregHNZ hmm. I don't think so. There he's trying to connect to postgres from outside the container. In my case I can't run psql commands inside the container. I don't want to connect from outside. Can you replicate my results? I dumped my Dockerfiles so you can confirm/refute my case. — MadeOfAir, Jun 11 '17 at 02:31
Did you manage to find a way to run psql commands inside Dockerfile? — VFX, May 03 '23 at 18:50
@VFX For my use case above it's much more foolproof to run Python and Postgresql containers side-by-side, each of which is already readily available in the hub, using docker compose instead of trying to bundle them into one container. — MadeOfAir, May 04 '23 at 09:31

score 11 · Answer 1 · answered Jan 14 '18 at 00:39

This is related Multiple RUN vs. single chained RUN in Dockerfile, what is better?

I ran into this also as a docker noob. Each RUN may not be in the same "context". See this output, note the container is different for the start command and the psql command. Seems it's building snapshots of container as it builds the image.

Step 25/30 : RUN service postgresql start
 ---> Running in 5c22e985359c
Starting PostgreSQL 9.6 database server: main.
Removing intermediate container 5c22e985359c
 ---> db1f2c2cdb33
Step 26/30 : USER postgres
 ---> Running in 6d56501e7286
Removing intermediate container 6d56501e7286
 ---> b35d3912eacf
Step 27/30 : RUN psql -c "CREATE USER user with password 'password'"
 ---> Running in 8c9130d2daa5
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain

I used the following to link these commands together

RUN service postgresql start &&\
  sudo -u postgres psql -c "CREATE USER user with password 'password'"

Results in successful CREATE USER command and should be able to continue with further &&\ if needed

Step 23/23 : RUN service postgresql start &&  sudo -u postgres psql -c "CREATE USER user with password 'password'"
 ---> Running in f234f6074f56
Starting PostgreSQL 9.6 database server: main.
CREATE ROLE

I tried this and it is still showing the same error `psql: could not connect to server: .....` — VFX, May 03 '23 at 18:52

score 10 · Answer 2 · answered Jul 10 '18 at 13:13

It is better to use a script in docker-entrypoint-initdb.d. in Dockerfile

COPY ./init-user-db.sh /docker-entrypoint-initdb.d/init-user-db.sh

And script itself:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;

EOSQL

More details here: https://docs.docker.com/samples/library/postgres/#how-to-extend-this-image

Docker: can't RUN psql commands from Dockerfile

2 Answers2