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

Question

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.

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?

Answer 1

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

Answer 2

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/