How to create User Database in script for Docker Postgres

Question

I have been trying to set up a container for a development postgres instance by creating a custom user  amp  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

User · Answer

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>

.

User · Answer

By using docker-compose   Assuming that you have following directory layout    MYAPP ROOT docker-compose yml             Docker init sql             Docker db Dockerfile    File  docker-compose yml  version   3 3  services    db      build        context    Docker       dockerfile  db Dockerfile     volumes        -   var pgdata  var lib postgresql data     ports        -  5432 5432    File  Docker init sql  CREATE USER myUser   CREATE DATABASE myApp dev  GRANT ALL PRIVILEGES ON DATABASE myApp dev TO myUser   CREATE DATABASE myApp test  GRANT ALL PRIVILEGES ON DATABASE myApp test TO myUser    File  Docker db Dockerfile  FROM postgres 11 5-alpine COPY init sql  docker-entrypoint-initdb d    Composing and starting services   docker-compose -f docker-compose yml up --no-start docker-compose -f docker-compose yml start

User · Answer

You can use this commands   docker exec -it yournamecontainer psql -U postgres -c  CREATE DATABASE mydatabase ENCODING  LATIN1  TEMPLATE template0 LC COLLATE  C  LC CTYPE  C     docker exec -it yournamecontainer psql -U postgres -c  GRANT ALL PRIVILEGES ON DATABASE postgres TO postgres

User · Answer

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 your Dockerfile   Dockerfile  FROM library postgres COPY 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  lt  lt - 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  lt  lt - EOSQL     CREATE USER docker      CREATE DATABASE docker      GRANT ALL PRIVILEGES ON DATABASE docker TO docker  EOSQL

User · Answer

With docker compose there s a simple alternative  no need to create a Dockerfile   Just create a init-database sh      bin bash set -e  psql -v ON ERROR STOP 1 --username   POSTGRES USER  --dbname   POSTGRES DB   lt  lt -EOSQL     CREATE USER docker      CREATE DATABASE my project development      GRANT ALL PRIVILEGES ON DATABASE my project development TO docker      CREATE DATABASE my project test      GRANT ALL PRIVILEGES ON DATABASE my project test TO docker  EOSQL   And reference it in the volumes section   version   3 4   services    postgres      image  postgres     restart  unless-stopped     volumes        - postgres  var lib postgresql data       -   init-database sh  docker-entrypoint-initdb d init-database sh     environment        - POSTGRES USER postgres       - POSTGRES PASSWORD postgres     ports        - 5432 5432  volumes    postgres

User · Answer

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

User · Answer

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   amp   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

[bash] How to create User/Database in script for Docker Postgres

Examples related to bash

Examples related to postgresql

Examples related to docker

Examples related to dockerhub