In development I like to use containerized databases. Recently I’ve been working on a micro service project that uses Postgres as a datasource. Each service has it’s own independent datasource. Running independent postgresql docker per service is resource consuming, so hack is to use a single postgres docker with multiple databases.
Maybe I’m wrong, but multiple databases are not available by default on docker postgres image.
Thanks to https://gist.github.com/seandearnaley/0ac5897a5af3ac9e973fce7292ab6317, fix is to include an initialization script in docker-compose.yml. All initialization scripts should be defined in the /docker-entrypoint-initdb.d
folder of the container.
I did some modifications of the original gist, as I wanted a different user login per db, and not have to work with superuser in development.
services: db: image: postgres:15.0 restart: always ports: - 5434:5432 env_file: - .env volumes: - ./init-multi-postgres-databases.sh:/docker-entrypoint-initdb.d/init-multi-postgres-databases.sh
If you take a look at the .env
file, it looks like this:
POSTGRES_PASSWORD=pg_pass POSTGRES_MULTIBLE_DATABASES=db1:test1,db2:test2,db3
POSTGRES_PASSWORD is a mandatory variable, and defines a superuser password for PostgreSQL (default superuser is postgres).
One new environment variable is POSTGRES_MULTIPLE_DATABASES
. Databases are separated by comma, user is same named as the database, and password is optional (colon).
As for the ./init-multi-postgres-databases.sh
, it looks like:
#!/bin/bash # Exit immediately if a command exits with a non zero status set -e # Treat unset variables as an error when substituting set -u function create_databases() { database=$1 password=$2 echo "Creating user and database '$database' with password '$password'" psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL CREATE USER $database with encrypted password '$password'; CREATE DATABASE $database; GRANT ALL PRIVILEGES ON DATABASE $database TO $database; EOSQL } # POSTGRES_MULTIBLE_DATABASES=db1,db2 # POSTGRES_MULTIBLE_DATABASES=db1:password,db2 if [ -n "$POSTGRES_MULTIBLE_DATABASES" ]; then echo "Multiple database creation requested: $POSTGRES_MULTIBLE_DATABASES" for db in $(echo $POSTGRES_MULTIBLE_DATABASES | tr ',' ' '); do user=$(echo $db | awk -F":" '{print $1}') pswd=$(echo $db | awk -F":" '{print $2}') if [[ -z "$pswd" ]] then pswd=$user fi echo "user is $user and pass is $pswd" create_databases $user $pswd done echo "Multiple databases created!" fi
Don’t forget to chmod +x scripts/create-multiple-postgres-databases.sh
.
If your databases are still not created
Scripts in /docker-entrypoint-initdb.d
are only run if you start the container with a data directory that is empty. If you look at the docker logs, you should see that your script got ignored. Solution is to clean the data directory and try again.
Entire configuration is in a git repository at https://github.com/amarjanica/docker-multi-postgres-databases.
0 Comments