Multiple databases in docker and docker-compose

Question

I have a project consisting of two main Java apps that use eight Postgres databases, so is there a way in docker-compose to build eight different databases so that each one has a different owner and password? Can I even do that in docker-compose?

Example:

services:
    postgresql:
        build: db/.
        ports:
            - "5432:5432"
        environment:
          - POSTGRES_DB=database1
          - POSTGRES_USER=database1
          - POSTGRES_PASSWORD=database1

I know I can put all the .sql files in the docker-entrypoint-initdb.d and Postgres will make them automatically, but how do I declare what .sql file goes in what database?

Answer 1

Usually when I need more than one database in a docker project it's a test database. I find it easier to simply spin up a second docker container, without worrying about scripts or volume separation. The main trick is to not conflict the default ports (e.g. 5432 for postgres) and you're good to go. Then docker-compose can be something as simple as this:

version: '3.0'

services: 

  db:
    image: postgres
    environment: 
      - POSTGRES_DB
      - POSTGRES_USER
      - POSTGRES_PASSWORD
    ports:
      - ${POSTGRES_DEV_PORT}:5432
    volumes:
      - app-volume:/var/lib/postgresql/data

  db-test:
    image: postgres
    environment: 
      - POSTGRES_DB
      - POSTGRES_USER
      - POSTGRES_PASSWORD
    ports:
      - ${POSTGRES_TEST_PORT}:5432
    # Notice I don't even use a volume here since I don't care to persist test data between runs

volumes:
  app-volume: #

Caveat: Obviously, more containers will typically imply in a higher memory footprint

Answer 2

According to this Github issue might be possible to achieve multiple databases by using bash scripts which you will have to pass in your Dockerfile

EDIT:

To create multiple Databases you could use the following script:

https://github.com/mrts/docker-postgresql-multiple-databases

or

https://github.com/MartinKaburu/docker-postgresql-multiple-databases

Which suggest that you have to clone one of the above git repos and mount it as a volume to: /docker-entrypoint-initdb.d then you would be able to pass multiple database names by using: POSTGRES_MULTIPLE_DATABASES variable

Answer 3

This works for me

postgres:
  container_name: postgres-db
  image: postgres
  environment:
    POSTGRES_USER: PG_USER
    POSTGRES_PASSWORD: PG_PASSWORD
  ports:
    - "5432:5432"
  volumes:
    - postgres:/var/lib/postgresql/data
  healthcheck:
    test: ["CMD-SHELL", "cat /initialised.txt && pg_isready -U postgres"]
    interval: 10s
    timeout: 5s
    retries: 5
  entrypoint: [ "/bin/bash", "-c","
    docker-entrypoint.sh postgres &
    rm -f /initialised.txt || true &&
    until pg_isready -U postgres; do sleep 3; done &&
    psql -U PG_USER -d postgres -c 'CREATE DATABASE ms_catalog;' || true &&
    psql -U PG_USER -d postgres -c 'CREATE DATABASE ms_profile;' || true &&
    psql -U PG_USER -d postgres -c 'CREATE DATABASE ms_cache;' || true &&
    psql -U PG_USER -d postgres -c 'CREATE DATABASE ms_payment;' || true &&
    psql -U PG_USER -d postgres -c 'CREATE DATABASE ms_notification;' || true &&
    echo done > /initialised.txt &&
    wait"
  ]

Answer 4

Inspired by the accepted answer, but not satisfied with the fact that the db user is hardcoded, I've created a fully functional solution (docker-compose.yml file with related bash script) to solve that issue.

Same mechanism is used -> add the database name values to the POSTGRES_MULTIPLE_DATABASES variable and build or mount, but this time the related user will be responsible for these databases.

You can use the docker-compose file, replace the placeholders with the current values and everything is runnable. Everything is explained in the README file.

Here is the github repository.

Answer 5

The PostgreSQL image has the concept of "initialization scripts" that you can put in the directory /docker-entrypoint-initdb.d.

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

reference

You can use that to create a new database and then grant access to it.

Example of compose.yml:

services:
  postgresql:
    image: postgres:17.4
    ports:
      - "5432:5432"
    volumes:
      - "database:/var/lib/postgresql/data"
      - "./docker/postgresql/initdb.d:/docker-entrypoint-initdb.d"
    environment:
      POSTGRES_DB: somedb
      POSTGRES_USER: someuser
      POSTGRES_PASSWORD: somepassword

volumes:
  database:

The script ./docker/postgresql/initdb.d/init-test-db.sh can be as following:

#!/usr/bin/env bash
set -e

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

Then, when the main database is created, the postgresql container will run the script and create the database somedb_test.