Use environment variables in docker-compose for initdb of postgres

Question

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

@AshrafulIslam sorry, no, I did not follow any further that approach. I had to do the replacements upfront and keep the data hardcoded in the files unfortunately. — fritz
– fritz, Commented Mar 16, 2021 at 19:17

orangecaterpillar · Accepted Answer · 2022-02-03 18:39:25Z

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.

Collectives™ on Stack Overflow

Use environment variables in docker-compose for initdb of postgres

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related