Solution for creating multiple databases when Docker starts PostgreSQL

1 Introduction

In the article "Start PostgreSQL with Docker and Recommend Several Connection Tools", we introduced how to start PostgreSQL through Docker , but there is only one database. What if you want to create multiple databases on the same Docker container?

2 Two solutions

One solution is to put shell/sql script into the /docker-entrypoint-initdb.d/ directory and have it automatically created when the container starts; the other solution is to specify the creation through a shell script, which is essentially the same. Only the first one is introduced here.

Put the shell script or sql script into the specified directory and it will be executed automatically. Both scripts are acceptable.

The following is an example of shell script:

#!/bin/bash

set -e
set -u

function create_user_and_database() {
	local database=$1
	echo " Creating user and database '$database'"
	psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
	    CREATE USER $database;
	    CREATE DATABASE $database;
	    GRANT ALL PRIVILEGES ON DATABASE $database TO $database;
EOSQL
}

if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
	echo "Multiple database creation requested: $POSTGRES_MULTIPLE_DATABASES"
	for db in $(echo $POSTGRES_MULTIPLE_DATABASES | tr ',' ' '); do
		create_user_and_database $db
	done
	echo "Multiple databases created"
fi

The following is an example of a sql script:

CREATE USER pkslowuser;

CREATE DATABASE logdata;
GRANT ALL PRIVILEGES ON DATABASE logdata TO pkslowuser;

CREATE DATABASE orderdata;
GRANT ALL PRIVILEGES ON DATABASE orderdata TO pkslowuser;

CREATE DATABASE userdata;
GRANT ALL PRIVILEGES ON DATABASE userdata TO pkslowuser;

3 Packaging and startup

Prepare Dockerfile and put shell/sql script file into the image:

FROM postgres:10
COPY src/main/resources/create-multiple-postgresql-databases.sh /docker-entrypoint-initdb.d/
COPY src/main/resources/create-multiple-postgresql-databases.sql /docker-entrypoint-initdb.d/

Start as follows:

docker run -itd \
    --name pkslow-postgres \
    -e POSTGRES_MULTIPLE_DATABASES=db1,db2 \
    -e POSTGRES_USER=pkslow \
    -e POSTGRES_PASSWORD=pkslow \
    -p 5432:5432 \
    pkslow/postgresql-multiple-databases:1.0-SNAPSHOT

After successful startup, the following database will be created:

db1,db2,
logdata,orderdata,userdata

4 Conclusion

This is a solution used in the development and testing phase. In fact, putting the database in a container is not a good choice.

Please check the code: https://github.com/LarryDpk/pkslow-samples

This concludes this article about creating multiple databases when starting PostgreSQL with Docker. For more information about starting PostgreSQL with Docker, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!