CODEWITHSUNDEEP
postgresqldockerenvironment-variables
fritz
fritz

Reputation: 163

Use environment variables in docker-compose for initdb of postgres

I'd like to have my basic database built using *.sql script placed in /docker-entrypoint-initdb.d/

My SQL files look like this:

create schema crm;
alter schema crm owner to ${CRM_SCHEMA_OWNER};
....

Of course, importing using psql does not resolve the variable. My question is now, how could I use environment variables like this?

I tried around having also a .sh file in the entrypoint directory, replacing the strings in the .sql file with environment data, but I always get:

db_1 | sed: couldn't open temporary file /docker-entrypoint-initdb.d/sedjKqYgZ: Permission denied

I even tried 

COPY sql/*.sql /mydata/
RUN ln -sf /mydata/*.sql /docker-entrypoint-initdb.d/

in the Dockerfile and in my .sh script in /docker-entrypoint-initdb.d/

sed -i "s/__CRM_SCHEMA_OWNER__/$CRM_SCHEMA_OWNER/g" /mydata/01_create_crm.sql

but this also did not work.

Any suggestions on how to use env variables for setting up a DB?

Thanks Fritz

Upvotes: 9

Views: 4338

Answers (1)

orangecaterpillar
orangecaterpillar

Reputation: 866

Currently postgres docker images will run shell scripts found in /docker-entrypoint-initdb.d/ as well as SQL scripts. You could write something like this:

#!/bin/bash

psql -U ${POSTGRES_USER} <<-END
    create schema crm;
    alter schema crm owner to ${CRM_SCHEMA_OWNER};
END

in a file called create_crm_schema.sh for example.

From the offical postgres docker image docs:

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

Upvotes: 3

Related Questions