current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 53 down vote favorite
16

I have been trying to set up a container for a development postgres instance by creating a custom user & database. I am using the official postgres docker image. In the documentation it instructs you to insert a bash script inside of the /docker-entrypoint-initdb.d/ folder to set up the database with any custom parameters.

My bash script: make_db.sh

su postgres -c "createuser -w -d -r -s docker"
su postgres -c "createdb -O docker docker"

Dockerfile

FROM library/postgres

RUN ["mkdir", "/docker-entrypoint-initdb.d"]
ADD make_db.sh /docker-entrypoint-initdb.d/

The error I get from the docker logs -f db (db is my container name) is:

createuser: could not connect to database postgres: could not connect to server: No such file or directory

It seems that the commands inside of the /docker-entrypoint-initdb.d/ folder are being executed before postgres is started. My question is, how do I set up a user/database programmatically using the official postgres container? Is there any way to do this with a script?

share|improve this question
up vote 85 down vote accepted

EDIT - since Jul 23, 2015

The official postgres docker image will run .sql scripts found in the /docker-entrypoint-initdb.d/ folder.

So all you need is to create the following sql script:

init.sql

CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;

and add it in you Dockerfile:

Dockerfile

FROM library/postgres
ADD init.sql /docker-entrypoint-initdb.d/

But since July 8th, 2015, if all you need is to create a user and database, it is easier to just make use to the POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB environment variables:

docker run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=docker library/postgres

or with a Dockerfile:

FROM library/postgres
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker

for images older than Jul 23, 2015

From the documentation of the postgres Docker image, it is said that

[...] it will source any *.sh script found in that directory [/docker-entrypoint-initdb.d] to do further initialization before starting the service

What's important here is "before starting the service". This means your script make_db.sh will be executed before the postgres service would be started, hence the error message "could not connect to database postgres".

After that there is another useful piece of information:

If you need to execute SQL commands as part of your initialization, the use of Postgres single user mode is highly recommended.

Agreed this can be a bit mysterious at the first look. What it says is that your initialization script should start the postgres service in single mode before doing its actions. So you could change your make_db.ksh script as follows and it should get you closer to what you want:

NOTE, this has changed recently in the following commit. This will work with the latest change:

export PGUSER=postgres
psql <<- EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

Previously, the use of --single mode was required:

gosu postgres postgres --single <<- EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL
share|improve this answer
    
Worked perfectly. Thank you. I guess I just didn't understand the purpose of single user mode before. Interesting feature! – pech0rin Oct 28 '14 at 12:44
    
Is there a way to run an sql file in single user mode – DarVar Jan 6 '15 at 17:05
2  
you can with: gosu postgres postgres --single < /tmp/somefile.sql – Thomasleveil Jan 6 '15 at 17:06
1  
How can I run psql -U myDb -d myDb -f myDb.sql – DarVar Jan 6 '15 at 17:38
2  
Note, --single is no longer supported in any of the postgres Dockerfiles. – brianz Aug 25 '15 at 23:02
up vote 7 down vote

The accepted answer is outdated

You can now put .sql files inside the init directory:

From the docs

If you would like to do additional initialization in an image derived from this one, add one or more *.sql 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 and source any *.sh scripts found in that directory to do further initialization before starting the service.

So copying your .sql file in will work.

share|improve this answer
    
environmental variables for user/password and db creation still works. (9.5.3) – Jeremiah Adams Aug 17 at 20:52
    
I have a project that works locally. However, when I move it to AWS EC2, it says that DB is not found. I copy the .sql file in the proper directory, but it still returns that error. Any idea where I could look? I am new to Docker. – MadPhysicist Nov 17 at 22:53
up vote 2 down vote

You need to have the database running before you create the users. For this you need multiple processes. You can either start postgres in a subshell (&) in the shell script, or use a tool like supervisord to run postgres and then run any initialization scripts.

A guide to supervisord and docker https://docs.docker.com/articles/using_supervisord/

share|improve this answer
up vote 1 down vote

I add custom commands to a environment evoked in a CMD after starting services... I haven't done it with postgres, but with Oracle:

#set up var with noop command
RUN export POST_START_CMDS=":"
RUN mkdir /scripts
ADD script.sql /scripts
CMD service oracle-xe start; $POST_START_CMDS; tail -f /var/log/dmesg

and start with 

docker run -d ... -e POST_START_CMDS="su - oracle -c 'sqlplus @/scripts/script' " <image>

.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.