Run SQL commands inside postgres docker container using docker-compose

Question

I can run SQL commands inside a postgres docker container using psql:

psql -U postgres -c "SELECT * FROM USERS;"

Now, I want to run this command using docker-compose. Here's what I have so far but no luck:

docker-compose run db psql -U postgres -c "SELECT * FROM USERS;"
# psql: error: could not connect to server: 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"?

Any idea what I'm doing wrong or is this not possible?

Answer 1

I think you can't. See postgresql Dockerfile:

CMD ["postgres"]

The final CMD is postgres which will start the postgresql server.

If you use docker-compose run db psql -U postgres -c "SELECT * FROM USERS;", see this:

the command passed by run overrides the command defined in the service configuration.
For example, if the web service configuration is started with bash, then

docker-compose run web python app.py overrides it with python app.py.

So, the original postgres command never have chance to run, which means the postgresql server not start, then you have your error to connect to server.

I don't know if next alternative meet your requirement, just FYI:

First, use docker-compose up -d db to start the service.

Second, use docker-compose exec db psql -U postgres -c "select * from pg_user;" for example to run your command.