35

The documentation of the postgres Docker image says the following about the env var POSTGRES_DB:

This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

I have found that this is not true at all. For example, with this config:

version: '3.7'

services:
  db:
    image: postgres:11.3-alpine
    restart: always
    container_name: store
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=custom
      - POSTGRES_DB=customname
      - POSTGRES_PASSWORD_FILE=/run/secrets/db_password
    secrets:
      - db_password

volumes:
  postgres_data:

secrets:
  db_password:
    file: config/.secrets.db_password

The default database is called postgres, and not customname as I have specified:

$ docker exec -it store psql -U custom customname
psql: FATAL:  database customname does not exist
$ docker exec -it store psql -U custom postgres
psql (11.3)
Type help for help.

postgres=# ^D

Am I missing something obvious?

Improve this question

3 Answers 3

Reset to default
44

Providing the environment variables, as you did, SHOULD create the customname database when the container is initialized. There is no need to create the username and database in the /docker-entrypoint-initdb.d/' init scripts.

I would make sure there isn't any hanging postgres_data volume. If you have previously started the container without specifing the environment variables, the volume gets created for the default postgres database. Next time you start the container (with the POSTGRES_DB env specified), the database creation part is skipped.

Just to make sure, remove any created volume (the name should be something like *_postgres_data)

docker volume ls
docker volume rm <volume_name>

See User and DB were not created from environment variable arguments as well. Hope that helps

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Wow, you nailed it! I did so much searching but I had not found that issue. Finally everything is clear.
When I run docker volume ls, it gives me a long list of volumes, all of which described by something looking like a hash, I don't see any postgres_data. Did the format change or does this mean that I don't have any such volumes?
@cgold postgres_data is the name of the volume declared in the docker_compose file in question. In your case, the hashed volumes names returned by the ls command are anonymous volumes. PostgreSQL Dockerfile defines a volume for /var/lib/postgresql/data where the database data is stored. If this volume is not mapped explicitly to the host, an anonymous volume is created. Use docker inspect -f '{{ .Mounts }}' container-id to see the mapping
docker volume ls returns a blank list to me. I deleted the host volume before starting the container again, but it still created the default database but not the custom one.
Script to delete ALL VOLUMES: docker stop $(docker ps -qa) && docker rm $(docker ps -qa) && docker volume rm $(docker volume ls -q) github.com/laradock/laradock/issues/542#issuecomment-271898987
2

You need to create the database first.

If you want to do that automatically for new data directories, then the official Docker Postgres image has an option to do so by placing Initialization Scripts with the extension .sql in the /docker-entrypoint-initdb.d/ directory.

For example, create a file with contents like:

CREATE USER custom_user;
CREATE DATABASE custom_db;
GRANT ALL PRIVILEGES ON DATABASE custom_db TO custom_user;

And save it to /docker-entrypoint-initdb.d/create-db.sql in the container, e.g. with COPY in the Dockerfile. Scripts with extension .sql inside that directory will only run if the DATA directory is empty, and multiple files will run in the alphabetical order of the file names.

If you want to set it up manually, you can also do that with the createdb utility

createdb [connection-option...] [option...] [dbname [description]]

Or by connecting to the postgres database and use the CREATE DATABASE ... command, e.g.

docker exec -it store psql -U postgres -c 'CREATE DATABASE customname;'

If you connect interactively as in your question, you can do the following:

$ docker exec -it store psql -U postgres
psql (11.3)
Type help for help.

postgres=# CREATE DATABASE customname;
CREATE DATABASE

postgres=# \c customname

The last command will connect you to the customname database.

Improve this answer

4 Comments

The wording of the documentation made me think that the database would be created automatically. I need some way to create it during the compose pipeline though.
Actually I read the docs again and it says "the database that is created when the image is first started". So it definitely seems like it should create it automatically.
Did you previously create a cluster on that volume before you added that environment variable to docker-compose? I don't think it will create a new database if the cluster already exists.
@rubik - OK, I understand better what you want to do now. I updated the answer.
1

If you've changed the username/password since the very first run, try to delete the prior volume created

    docker volume rm <volume-name>

Then run the compose file again

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.