How to execute commands in docker compose files

Question

I have a very complex environment, there are two services which named as A service and B service depend on service postgresql, but A and B use difference super user. so once I start the service postgresql, I need create another superuser for service B. This is a part of docker compose file :

  postgres:
image: postgres:13.4
container_name: postgresql
hostname: postgresql
volumes:
    - 'postgres_data:/var/lib/postgresql/data'
environment:
  POSTGRES_DB: keycloak
  POSTGRES_USER: keycloak
  POSTGRES_PASSWORD: password
command: ["CREATE ROLE postgres LOGIN SUPERUSER; | ALTER USER postgres CREATEDB CREATEROLE LOGIN INHERIT REPLICATION BYPASSRLS;"]

enter image description here In the environments, I already setup the default user, password and DB in service postgresql for service A, BUT I’d like to create another super user for service B once service postgresql started, does there any one know how to write the commands. many thanks. I think this grammer is wrong: command: ["CREATE ROLE postgres LOGIN SUPERUSER; | ALTER USER postgres CREATEDB CREATEROLE LOGIN INHERIT REPLICATION BYPASSRLS;"]

The postgres image only supports running scripts on database initialization. Not at container startup. — Hans Kilian, Jun 15 '22 at 08:16
@HansKilian So , it is not imporssible to run commands after postgresql started in container? — NothingIsEve, Jun 15 '22 at 08:19
I'd run the command from another container once Postgres is up. But you need to consider things like what to do if Postgres crashes and is restarted. Do you then want to run the command again? — Hans Kilian, Jun 15 '22 at 08:23
Thank you for your reply, but at this moment, I don’t need to consider the situation of crash of postgresql , this kind of thing, I just want to know how to write the part of commands in configuration file of compose. Or is it true ,cannot run command in postgres service of docker compose file ? — NothingIsEve, Jun 15 '22 at 08:30
You can put the `CREATE USER ...` command in a script in `/docker-entrypoint-initdb.d` in the container. You could also create separate database containers for the two applications, setting a different `POSTGRES_USER` for each. The linked question walks through several paths to run that SQL command. — David Maze, Jun 15 '22 at 10:12
@DavidMaze As I read OP's post, they already have a database, so using `docker-entrypoint-initdb.d` or the solutions in the duplicate question will not work. Can you reopen the question please? — Hans Kilian, Jun 15 '22 at 10:18
At least one of the answers there involves using `psql` to run the SQL commands, which would work with a running database. If just running `psql` is the obstacle, [Docker - How can run the psql command in the postgres container?](https://stackoverflow.com/questions/37099564/docker-how-can-run-the-psql-command-in-the-postgres-container) discusses this too. — David Maze, Jun 15 '22 at 10:44

score 0 · Answer 1 · answered Jun 15 '22 at 08:58

The issue with running commands on container startup is that you need the database to be up to be able to run the commands. By overriding the command: on the container, you replace the normal command which is to start Postgres. So with a new command, Postgres will never start.

You can run the command from a different container once Postgres is started. The command-runner container uses psql to connect to the database and run the command. It also uses the postgres docker image, but because it overrides the command on the container, this container doesn't start a database. It only runs the command.

Here's what I've come up with.

version: "3"

services:
  postgres:
    image: postgres
    environment:
      - POSTGRES_USER=keycloak
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=keycloak

  command-runner:
    image: postgres
    command: /bin/sh -c 'sleep 10 && PGPASSWORD=password psql -U keycloak -h postgres -d keycloak -c "CREATE ROLE postgres LOGIN SUPERUSER; ALTER USER postgres CREATEDB CREATEROLE LOGIN INHERIT REPLICATION BYPASSRLS;"'
    depends_on:
      - postgres

I had to put in a sleep 10 command in the command-runner container for it to wait for Postgres to be ready to accept connections.

I also removed the | you had in your command before ALTER USER. I got a syntax error on it.

How to execute commands in docker compose files

1 Answers1