CODEWITHSUNDEEP
postgresqldockerdocker-compose
rubik
rubik

Reputation: 9104

Postgres Docker image is not creating database with custom name

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?

Upvotes: 33

Views: 36591

Answers (3)

oseleslie
oseleslie

Reputation: 31

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

Upvotes: 1

b0gusb
b0gusb

Reputation: 4731

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

Upvotes: 44

isapir
isapir

Reputation: 23610

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.

Upvotes: 2

Related Questions