Today I had to build a new microservice which uses PostgreSQL for data storage. Following the containers principle of disposability instead of reuse, I need to provide some bootstrap logic that setups everything needed for this piece to work independantly and assume that each run might be the first run. Also, following the Twelve-Factor App config guidelines I shouldn't store any relevant configuration value in files, but use environment variables instead.
What looks simple in theory is indeed simple when you find a solution, but might not be obvious, so here's my approach. I use Linux createdb command, but as it doesn't allows you to specify the database connection password as a parameter, I use a .pgpass file with the proper permissions to make it work, with the added bonus of only using bash scripting to achieve it.
echo "*:*:*:$DB_USER:$DB_PASSWORD" > ~/.pgpass chmod 0600 ~/.pgpass createdb $DB_NAME -h $DB_HOST -p $DB_PORT -U $DB_USER -O $DB_USER -w || true rm ~/.pgpass
There might be better solutions (I'd love to hear them) but this does the job and is easy to understand.