How to create User/Database in script for Docker Postgres

by

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

More Related Contents:

  1. Check if image:tag combination already exists on docker hub
  2. How to mount a host directory in a Docker container
  3. Clone private git repo with dockerfile
  4. Using the RUN instruction in a Dockerfile with ‘source’ does not work
  5. How to pass in password to pg_dump?
  6. How to pass arguments to Shell Script through docker run
  7. Docker: How to use bash with an Alpine based docker image?
  8. access host’s ssh tunnel from docker container
  9. Shell script to execute pgsql commands in files
  10. Permission denied to Docker daemon socket at unix:///var/run/docker.sock
  11. Missing Carriage Return in Docker for Mac Containers
  12. docker entrypoint running bash script gets “permission denied”
  13. Get Environment Variable from Docker Container
  14. How to set uid and gid in Docker Compose?
  15. Can I export a variable to the environment from a Bash script without sourcing it?
  16. Passing arguments to an interactive program non-interactively
  17. How to urlencode data for curl command?
  18. How to define hash tables in Bash?
  19. Why isn’t tilde (~) expanding inside double quotes? [duplicate]
  20. bash: shortest way to get n-th column of output
  21. Calling shell functions with xargs
  22. Openssh Private Key to RSA Private Key
  23. Parse out key=value pairs into variables
  24. How to write a bash script to set global environment variable?
  25. Bash Script Binary Search
  26. Escaping forward slashes in sed command [duplicate]
  27. Changing a postgres containers server port in Docker Compose
  28. Switch case with fallthrough?
  29. Is bash a programming language?
  30. Getting a ‘source: not found’ error when using source in a bash script

Leave a Comment