DEV Community

Cover image for Multiple Postgres databases in a single Docker container
Bartosz Gordon
Bartosz Gordon

Posted on

Multiple Postgres databases in a single Docker container

This will be a short one.

The official Postgres Docker image supports a few environment variables. One of them, POSTGRES_DB, is responsible for holding a database name. However, if you want your container to include more than one database (e.g app and app_test), you have to reach for different solutions.

One of them is to create a bash script that sets up multiple databases by psql command. Postgres will execute it on database startup inside the container. An example of such script, create-multiple-postgresql-databases.sh, can be found here. Credits to mrts at Github.

You can place it in whatever project directory you want, although pg-init-scripts/ might be a good starting point. Then, to make the script visible and executable by Postgres, it has to be mounted to the /docker-entrypoint-initdb.d container directory.

To mount a host directory to a container directory, we have to use volumes.

Syntax: <host directory>:<container directory>.

WARNING: You cannot mount a single file to a directory, it works just for directories.

Final docker-compose.yml:

version: "3.7"
services:
  db:
    container_name: "app_db"
    image: "postgres:11.5-alpine"
    volumes:
      - ./pg-init-scripts:/docker-entrypoint-initdb.d
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_MULTIPLE_DATABASES=app,app_test
Enter fullscreen mode Exit fullscreen mode

The ${DB_USER} syntax means that the DB_USER value is read from automatically loaded .env file placed inside the same directory as docker-compose.yml.

Corresponding .env:

DB_USER=someuser
DB_PASSWORD=somepassword
Enter fullscreen mode Exit fullscreen mode

Remember, you shouldn't commit .env files! Create and commit .env-example file with empty secret values instead.

If you want to use a different path (e.g .env-docker), define env_file in docker-compose.yml:

env_file:
  - ./.env-docker
Enter fullscreen mode Exit fullscreen mode

Now, with every piece in place, run docker-compose up to start the service.

Happy coding!

Top comments (11)

Collapse
 
dariansampare profile image
Darian Sampare

Anyone ever run into permission issues running the script?

For example:

server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create-multiple-postgres-databases.sh

/usr/local/bin/docker-entrypoint.sh: /docker-entrypoint-initdb.d/create-multiple-postgres-databases.sh: /bin/bash: bad interpreter: Permission denied

Collapse
 
dariansampare profile image
Darian Sampare

Well... in case any other poor soul stumbles onto this problem, you have to set the script as executable because Docker copies over permissions...

chmod +x pg-init-scripts/create-multiple-postgres-databases.sh
Enter fullscreen mode Exit fullscreen mode
Collapse
 
pederfosse profile image
PederFosse

I created an account just to thank you

Thread Thread
 
dariansampare profile image
Darian Sampare

😂 Your welcome!

Collapse
 
danicunhac profile image
Daniel Costa

I also created an account just to thank you

Collapse
 
ykachube profile image
mr Robot

I also created an account just to thank you

Collapse
 
ditharahma12 profile image
Rahmaditha

i've already done that. but still no database created

Thread Thread
 
danicunhac profile image
Daniel Costa

You'll need to then run docker-compose up -d again and if needed, create your new database from an sql script.

Collapse
 
resulyrt93 profile image
Resul Yurttakalan

I figure out this problem with change volume config. Don't attach whole /docker-entrypoint-initdb.d directory as volume. Then not permitted a directory copied to container. Solution was add all files one by one. like below;

volumes:
      - ./db_init/db_init.sh:/docker-entrypoint-initdb.d/db_init.sh 
Enter fullscreen mode Exit fullscreen mode
Collapse
 
ian_waghorne profile image
Ian Waghorne

The server seems to shut down when the DBs are created

Multiple databases created
LOG: received fast shutdown request
waiting for server to shut down...LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
.LOG: shutting down
LOG: database system is shut down
done
server stopped
PostgreSQL init process complete; ready for start up.

Collapse
 
sebtrif profile image
Sebastian Trif

Thanks for sharing this!

Wondering if you found anything else since you wrote this article that may be helpful or did you end up sticking with the approach you wrote here?