docker compose: postgresql create db, user pass and grant permission

Question

I have the following docker-compose file:

version: '3'
services:
  web:
    build:
      context: ./django_httpd_mod_wsgi
    ports:
     - "8000:80"
  db:
    build:
      context: ./postgresql
    volumes:
       - db-data:/var/lib/postgres/data
volumes:
  db-data:

I am building postgresql image using archlinux:

The following is my postgresql Dockerfile:

FROM archlinux/base

RUN yes | pacman -S postgresql

RUN mkdir /run/postgresql/
RUN chown -R postgres:postgres /run/postgresql/

USER postgres

RUN initdb -D /var/lib/postgres/data

RUN psql -c 'CREATE DATABASE btgapp;' 
RUN psql -c "CREATE USER simha WITH PASSWORD 'krishna';" 
RUN psql -c 'GRANT ALL PRIVILEGES ON DATABASE btgapp TO simha;'

CMD ["/usr/bin/postgres","-D","/var/lib/postgres/data"]

When I try to do:

docker-compose up

I get the error:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"?
ERROR: Service 'db' failed to build: The command '/bin/sh -c psql -c 'CREATE DATABASE dbname;'' returned a non-zero code: 2

I understood that I have to run the psql -c CREATE DATABASE "dbname" after starting the postgresql server by /usr/bin/postgres -D /var/lib/postgres/data

But I cannot start multiple commands in a Dockerfile. So how to do this?

The option is to start a script, but then it will be difficult to see postgres running as a single process.

Answer 1

Based on the comments, I will try to answer here.

I believe that you should go with the postgres 11-alpine image. And I will try to explain why here.

Official docker images come with a number of benefits that you should always consider before starting your own.

1. Upgrade path is easy - when a new revision of the application wrapped in the image is released, the official docker image will in most cases be updated along with it. And ususally the changes respect the configuration conventions that the image has established. Such as environment variables, startup specifics. So that users can simple change the tag in their stacks, and upgrade. There may of course be breaking changes - always check this.
2. Large user base - when images like postgres have been downloaded more than 10 milliion times (2019), this does not only mean that it is popular, but inherently works like a guarantee that the image has been tested thoroughly. Any elementary bugs have been weeded out already, and you will have an easy time with the image.
3. Optimized for size and performance - you can be sure that attention has been paid to a lot of details, minimizing the size of the image and maximizing performance. Many projects publish their applications on a few different linux distros. Like postgres - they publish debian and a alpine based images. The alpine image is the smaller one, while the debian is slightly larger, but gives you access to the vast debian package repositories if you need extra packages installed.
4. Easy configuration - maintainers of the official images usually understand that usecases of their userbase very well. And they try to make our lives as developers and admins easier (god bless them). Official images usually have some pretty good documentation sitting right on their docker hub landing page, or a link to a github repo where the README.md will cover common usecases. I find that these instructions are worth a good read from top to bottom.

I understand that you want to keep the image small, but what do you know - the postgres project has got your usecase covered.

The latest alpine postgres image tagged 11-alpine has a compressed footprint of 28 MB and decompressed of 70MB. While the archlinux/base image that you want to start off with has compressed base footprint of 153MB and a decompressed size of 445MB. And that's before you introduce postgres itself.

Add to that, that the database and user that you want created on startup - can be handled in the environment variables alone for the official postgres image. Like this:

docker run -d --name some-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=simha \
  -e POSTGRES_DB=btgapp \
postgres:11-alpine

If that does not cover the initialization that you need for your database, then you can copy .sql scripts (and .sh scripts) into a special location in the image - and they will be executed on startup. For this you can extend their image like this:

init-user-db.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER simha;
    CREATE DATABASE btgapp;
    GRANT ALL PRIVILEGES ON DATABASE btgapp TO simha;
EOSQL

And then with a Dockerfile like this:

Dockerfile

FROM postgres:11-alpine
COPY ./init-user-db.sh /docker-entrypoint-initdb.d/init-user-db.sh

(This is taken from the postgres description on docker hub)

In closing - I would recommend that you do not prioritize the distro that an image is based on over the usability and maintainability. Docker enables us to run applications in containers without really caring too much about what distro is inside the container. It's all linux anyway. At the end of the day, I expect that you want a stable postgres database container like me. This is what I get with the official postgres image.

I hope I helped you evaluate your options on this.