create postgres database if it does not exist on docker-compose start up

Question

I have the following docker-compose file:

version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres
    onrun:
      psql -h=localhost -P=${POSTGRES_PASSWORD} -U=${POSTGRES_USER} -tc "SELECT 1 FROM pg_database WHERE datname = 'premiership'" | grep -q 1 || psql -h=localhost -P=${POSTGRES_PASSWORD}-U=${POSTGRES_USER}c "CREATE DATABASE premiership"
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped

The above does not work because there is no onrun in docker-compose services.

All I want to do is create a database if it does not exist but this is insanely difficult because I don't know when the service is up.

Also it is not easy to do this in postgres. I tried mapping a volume so that an initdb.sql is ran:

volumes:
   - postgres:/data/postgres
   - ./initdb/1_schema.sql:/docker-entrypoint-initdb.d/1_schema.sql

1_schema.sql looks like this:

SELECT datname
FROM pg_database
WHERE datname='premiership'';
    IF datname='
premiership' 
        THEN CREATE DATABASE premiership PASSWORD 'test';
    END IF;

THe database is not created and when I run docker-compose logs I don't see anything about running the script.

lewer · Accepted Answer · 2019-05-20 08:00:06Z

16

Have you tried setting POSTGRES_DB variable in your docker-compose.yml environment ?

 image: postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      POSTGRES_DB: premiership
      PGDATA: /data/postgres
    volumes:

see https://hub.docker.com/_/postgres :

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.

answered May 20, 2019 at 8:00

lewer

4314 silver badges6 bronze badges

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

4 Comments

dagda1 Over a year ago

thank you sir. Does PG_DATA mean I don;t have to map the volume?

lewer Over a year ago

PGDATA is the location inside the container for the database files. If you have no particular reason to change it, you should just leave the default /var/lib/postgresql/data and replace your volume mapping with postgres:/var/lib/postgresql/data Have you tried to remove both the container and the volume, and restart the service ? You can do it with docker-compose down -v and then docker-compose up

max Over a year ago

This comment is definitely useful. Having said that, I'm observing that this does not create my database with that name with version 3.9 of docker compose on windows. It is creating a database with my username which is the fall back as you mentioned above.

Manish Over a year ago

@max even I face the same behavior, database is created with the username. were you able to resolve that ?

Collectives™ on Stack Overflow

create postgres database if it does not exist on docker-compose start up

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related