Docker compose postgresql service – can't create user and database during build?

I have wasted an entire day on this, and to say I’m not impressed by the unnecessary complexity of what should be a simple task – would be a gross understatement.

Ok, having got that off my chest, I am building a django application using docker-machine, docker-compose, postgresql and redis – by following this tutorial.

  • A completely closed source docker container
  • Can't get an interactive Docker shell inside screen
  • How to view Docker image layers on Docker Hub?
  • how to run nginx docker container with custom config?
  • docker git and pull setup
  • Can't connect to Docker containers on OSX
  • I have managed to get the basic tutorial to work – but it does not suit my needs, as I need to create a user and a database for my application – as opposed to using ‘postgres’ for both.

    I have used the answer from @dnephin to a similar question, and modified my code as follows:

    I created a new Dockerfile in a new directory ./database/:

    FROM postgres:9.6
    COPY . /fixtures
    WORKDIR /fixtures
    RUN /fixtures/setup.sh
    

    ./database/setup.sh contents:

    #!/bin/bash
    set -e
    
    pg_createcluster 9.6 main --start
    /etc/init.d/postgresql start
    su - postgres # makes no effing difference ...
    psql -f create_fixtures.sql    
    /etc/init.d/postgresql stop
    

    ./database/create_fixtures.sql contents:

    CREATE DATABASE mydatabase WITH ENCODING 'UTF8';
    CREATE USER webuser ENCRYPTED PASSWORD 'deadbeefsnaf0' NOSUPERUSER NOCREATEDB NOCREATEROLE;
    GRANT ALL PRIVILEGES ON mydatabase TO webuser;
    

    and finally my postgres service in the docker_compose.yml is modified to use build:

    postgres:
        build: ./database/
        ...
    

    When I run docker-compose build, the build goes through the motions and then barfs at where I’m importing the SQL fixtures file via psql:

    frothing@themouth:~/path/to/directory$ docker-compose build
    redis uses an image, skipping
    Building postgres
    Step 1/4 : FROM postgres:9.6
     ---> ff0943ecbb3c
    Step 2/4 : COPY . /fixtures
     ---> fae19dc88da8
    Removing intermediate container 84b860aee55c
    Step 3/4 : WORKDIR /fixtures
     ---> aa88438dc69f
    Removing intermediate container b801ddc3b374
    Step 4/4 : RUN /fixtures/setup.sh
     ---> Running in ca3e89ec2460
    Creating new cluster 9.6/main ...
      config /etc/postgresql/9.6/main
      data   /var/lib/postgresql/9.6/main
      locale en_US.utf8
      socket /var/run/postgresql
      port   5432
    Starting PostgreSQL 9.6 database server: main.
    psql: FATAL:  role "root" does not exist
    ERROR: Service 'postgres' failed to build: The command '/bin/sh -c /fixtures/setup.sh' returned a non-zero code: 2
    

    I tried to solve this using the useless documentation on docker for postgresql service – but got no where.

    How can I solve this?

  • Fluent docker tail vs docker fluentd logging driver
  • Use a single VM with Vagrant to host Docker Containers across multiple projects
  • Php development inside docker on ubuntu 16.04 for cpanel shared hosting with some addon domain on it
  • will docker share binary file or not?
  • Where can I configure the start user UID for Docker containers?
  • Node.js docker container runs when folder is located on desktop but wont in C drive
  • 4 Solutions collect form web for “Docker compose postgresql service – can't create user and database during build?”

    Volumes are not available at build time. You can create /var/lib/postgresql/data in your script but it will be overwritten by VOLUME /var/lib/postgresql/data from postgres:9.6 image.

    In your case: just use the following docker file:

    FROM postgres:9.6
    COPY ./create_fixtures.sql /docker-entrypoint-initdb.d/create_fixtures.sql
    

    They get automatically executed once the container starts. Here is an example:

    $ docker run -d --name mydb -p 33306:3306 yourtag
    $ docker exec -ti mydb psql -U postgres
    postgres=# \l
                                     List of databases
        Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
    ------------+----------+----------+------------+------------+-----------------------
     mydatabase | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
                |          |          |            |            | postgres=CTc/postgres+
                |          |          |            |            | webuser=CTc/postgres
     postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
     template0  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                |          |          |            |            | postgres=CTc/postgres
     template1  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                |          |          |            |            | postgres=CTc/postgres
    (4 rows)
    

    Outdated answer:

    Your script should work on a container except in the fixture you have to execute psql like this:

    su postgres -c "psql -f create_fixtures.sql"
    

    su --login postgres does not work because postgres can’t open a bash or shell. You can try around with docker run --rm -ti postgres:9.6 bash.

    Sorry I have to tell you there is one more error in your sql script: GRANT ALL PRIVILEGES ON DATABASE mydatabase TO webuser – the keyword DATABASE is necessary here.

    Here is a complete log how I tested and can confirm this works:

    docker run --rm -ti postgres:9.6 bash
    root@be03ab1eb704:/# cat > test.sql <<EOF
    > CREATE DATABASE mydatabase WITH ENCODING 'UTF8';
    > CREATE USER webuser ENCRYPTED PASSWORD 'asdf123' NOSUPERUSER NOCREATEDB NOCREATEROLE;
    > GRANT ALL PRIVILEGES ON DATABASE mydatabase TO webuser;
    > EOF
    root@be03ab1eb704:/# pg_createcluster 9.6 main --start
    Creating new PostgreSQL cluster 9.6/main ...                                                                                                  
    /usr/lib/postgresql/9.6/bin/initdb -D /var/lib/postgresql/9.6/main --auth-local peer --auth-host md5                                          
    The files belonging to this database system will be owned by user "postgres".                                                                 
    This user must also own the server process.                                                                                                   
    
    The database cluster will be initialized with locale "en_US.utf8".                                                                            
    The default database encoding has accordingly been set to "UTF8".                                                                             
    The default text search configuration will be set to "english".                                                                               
    
    Data page checksums are disabled.                                                                                                             
    
    fixing permissions on existing directory /var/lib/postgresql/9.6/main ... ok                                                                  
    creating subdirectories ... ok                                                                                                                
    selecting default max_connections ... 100                                                                                                     
    selecting default shared_buffers ... 128MB                                                                                                    
    selecting dynamic shared memory implementation ... posix                                                                                      
    creating configuration files ... ok                                                                                                           
    running bootstrap script ... ok                                                                                                               
    performing post-bootstrap initialization ... ok                                                                                               
    syncing data to disk ... ok                                                                                                                   
    
    Success. You can now start the database server using:                                                                                         
    
        /usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main -l logfile start                                                       
    
    Ver Cluster Port Status Owner    Data directory               Log file                                                                        
    9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log                                     
    root@be03ab1eb704:/# /etc/init.d/postgresql start
    [ ok ] Starting PostgreSQL 9.6 database server: main.                                                                                         
    root@be03ab1eb704:/# su postgres -c "psql -f test.sql"
    CREATE DATABASE                                                                                                                               
    CREATE ROLE
    GRANT
    root@be03ab1eb704:/# /etc/init.d/postgresql stop
    [ ok ] Stopping PostgreSQL 9.6 database server: main.
    root@be03ab1eb704:/# exit
    exit
    

    The official postgresql docker image automatically imports scripts at the first start of a container. So if you mount your directory with your init sql script to container path ‘/docker-entrypoint-initdb.d/’ it should be run.

    For example if you have your import script myImport.sql and it’s on your host in a directory /opt/import, you can mount the import directory on container start to your postgres image to /docker-entrypoint-initdb.d and the sql file will be executed after initial database setup.

    docker run -p $toHostParam:5432 -e POSTGRES_PASSWORD="$dbPwd" \
    -e POSTGRES_USER="$dbUser" \
    -e POSTGRES_DB="$dbName" \
    -e PGDATA=/opt/pgdata \
    -v ${dataDir}:/opt/pgdata \
    # look here
    # mount of local import dir
    -v /opt/import:/docker-entrypoint-initdb.d \
     postgres:9.6
    

    Take a look at the postgesql image start script here (from line 126):
    https://github.com/docker-library/postgres/blob/master/9.6/docker-entrypoint.sh

    If you want a specific db user or database you can also customize your postgresql container with environment variables.

    Take a look at the ‘Environment Variables’ section here: https://hub.docker.com/_/postgres/

    Try with this setup.sh

    #!/bin/bash
    set -e
    
    pg_createcluster 9.6 main --start
    su postgres sh -c "/etc/init.d/postgresql start && psql -f create_fixtures.sql && /etc/init.d/postgresql stop"
    

    Try a explicit user when running psql:

    psql -U postgres -f create_fixtures.sql
    
    Docker will be the best open platform for developers and sysadmins to build, ship, and run distributed applications.