Solution for creating multiple databases when Docker starts PostgreSQL

1 Preface

In the article "Docker Launches PostgreSQL and Recommends Several Connection Tools", we show how to use the Docker To start PostgreSQL But only 1 database, if you want to create multiple databases in the same 1 Docker What about the container?

2 Two schemes

One option is to put shell/sql Script placement /docker-entrypoint-initdb.d/ Directory, let the container be created automatically when it starts; The other is through shell The script specifies the creation, and the essence is 1. Only the first one is introduced here.

Put shell Script or sql When the script is placed in the specified directory, it will be automatically executed. Both scripts can be used.

shell Examples of scripts are as follows:

#!/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

sql Examples of scripts are as follows:

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 Package Startup

Prepare PostgreSQL0 , put shell/sql The script file is put 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 Summary

This is a scenario used in the development and testing phase, and actually putting the database in a container is not a good choice.

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