example docker compose for postgresql with db init script

docker-compose.yml

postgres:
  image: postgres:9.4
  volumes:
    - ./init.sql:/docker-entrypoint-initdb.d/init.sql

init.sql

create table sometable(id int);

in docker-compose.yml
patch /file.sql:/patch/to/docker-entrypoint-initdb.d/
in fact, point the file to the directory
hint: remember where the work dir is base on your docker file for the image you are using to compose this container
volumes:

• ./init.sql:/docker-entrypoint-initdb.d/

did anyone get this to work properly. i am currently facing similar issues

it is easier to make a docker file and COPY the init.sql to /docker-entrypoint-initdb.d

did anyone get this to work properly. i am currently facing similar issues

well what i did which worked was that i deleted all previous containers of the same image.
docker rm -f -v postgres_container_name.
restarted and it worked like a charm after that

What is the best way to add tables from sql files after the first init?

at the moment i have a docker compose file with mariadb und phpmyadmin
docker-entrypoint-initdb.d works the first time

but i need the possibilty to creates tables from sql files after docker-compose up
can you help me?

after init, I'm using a separate file with database migrations (using app specific db migrations system) and run them by application entrypoint.sh, from time to time then I merge migrations to init, and clear migrations file.

I had some trouble getting it to work. My docker-compose.yml looks like this:

  volumes:
    - ./:/app

services:
  db:
    image: postgres:12
    restart: unless-stopped
    volumes:
      - ./project/data/init.sql:/docker-entrypoint-initdb.d/init.sql
      - postgres_data:/var/lib/postgresql/data/
    environment:
      - POSTGRES_USER=picker
      - POSTGRES_PASSWORD=#
      - POSTGRES_DB=pricepicker-v2

volumes:
  postgres_data:

This is because Postgres does not execute init scripts if postgres-data folder is present. So I just renamed it to postgres and it works like a charm!

Thanks @Juliannnnshipit for your help. I got it working.

My setup.sh file looks something like this:

#!/usr/bin/env bash
psql "postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST/$POSTGRES_DB?sslmode=disable" <<-EOSQL
-- YOUR SQL Statements here
EOSQL

docker-compose.yml

postgresdb:
    image: postgres:9.4
    container_name: postgresdb
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASS}
      - POSTGRES_DB=${DB_NAME}
      - DATABASE_HOST=${DB_HOST}
    ports:
      - 5433:5432
    volumes:
      - ./contrib/setup.sh:/docker-entrypoint-initdb.d/initdb.sh
      - ./pgdata:/var/lib/postgresql/data

Thank you so much. It saved my time

I struggled with this same issue and thought the only solution was to not use a sql file in the docker-entrypoint-initdb.d directory but actually the only problem was that I wasn't granting pivs to my user i was logging in as so the schemas were getting created just invisible to my user...

Don't have to do this

#!/bin/bash
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$POSTGRES_DB" <<-EOSQL
create schema if not exists $SCHEMA;
create table $SCHEMA.todos (
id serial primary key,
done boolean not null default false,
task text not null,
due timestamptz
);
create role $ANON nologin;
create role $AUTHENTICATOR noinherit login password '$POSTGRES_PASSWORD';
grant $ANON to $AUTHENTICATOR;
EOSQL

You can do this...
create_tables.sql

CREATE DATABASE audit_service;
GRANT ALL PRIVILEGES ON DATABASE audit_service to "postgres";

docker-compose.yml

db:
image: postgres:14.3-alpine
restart: unless-stopped
networks:
- my-appliance
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
ports:
- '5432:5432'
volumes:
- ./postgres/sql/create_tables.sql:/docker-entrypoint-initdb.d/create_tables.sql
- db_data:/var/lib/postgresql/data

I'm running into an issue where my db init process is in fact running in the container, at least batching a bunch of CREATES and INSERTS of my dump data, but then is abruptly shut down and restarted.

strends_psql | CREATE DATABASE
strends_psql | You are now connected to database "rivers" as user "usr".
strends_psql | CREATE SCHEMA
strends_psql | SET
strends_psql | SET
strends_psql | SET
strends_psql | SET
strends_psql | SET
strends_psql | SET
strends_psql | SET
strends_psql | CREATE TABLE
strends_psql | INSERT 0 4
strends_psql |
strends_psql |
strends_psql | 2019-04-29 17:43:06.633 UTC [41] LOG:  received fast shutdown request
strends_psql | waiting for server to shut down....2019-04-29 17:43:06.637 UTC [41] LOG:  aborting any active transactions

When I get a shell into my db container, the database rivers was created but is empty, with no tables, relations, or data from my batched inserts.

I had the same issue but after a few hours of trying, I found a working combination of docker-compose and init script. Btw, I read here that the container shutting down and restarting is (apparently) part of the initialization process. This is part of my initdb.sh:

#!/bin/bash
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$POSTGRES_DB"  <<-EOSQL
     create schema if not exists $SCHEMA;
     create table $SCHEMA.todos (
        id serial primary key,
        done boolean not null default false,
        task text not null,
        due timestamptz
     );
     create role $ANON nologin;
     create role $AUTHENTICATOR noinherit login password '$POSTGRES_PASSWORD';
     grant $ANON to $AUTHENTICATOR;
EOSQL

This is part of my docker-compose.yml:

version: '3.7'
services:
  db:
    image: postgres
    restart: always
    volumes:
      - ./initdb.sh:/docker-entrypoint-initdb.d/initdb.sh
      - ./pgdata:/var/lib/postgresql/data
    ports:
      - 8002:5432
    environment:
       POSTGRES_PASSWORD: docker
       POSTGRES_DB: mydb
       SCHEMA: public
       ANON: web_anon
       AUTHENTICATOR: authenticator

If you put a CREATE DATABASE statement at the top of your actual SQL script, but don't supply POSTGRES_DB, it will use the POSETGRES_USER as the DB name so the tables don't go where they should.

If you supply both POSTGRES_DB and put a CREATE DATABASE statement at the top of the SQL script, you will get an error about the DB already existing since POSTGRES_DB will create the DB before the script runs. You can see this order of operations in the logs.

I think the real fix here is to:

1. set POSTGRES_DB to be the name of the DB you want to create and don't CREATE TABLE in your script
2. Add SQL scripts to volume per docs

I was facing the same issue of init scripts getting ignored.

I HAVE FOUND A SOLUTION if it helps anyone !!

version: '2'
services:

  common_db:
    image: postgres:10.4
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres123
      - POSTGRES_MULTIPLE_DATABASES=db1,db2
    volumes:
      - ./db.sql:/docker-entrypoint-initdb.d/db.sql
      - ./postgres-data:/var/lib/postgresql/data
    ports:
      - 5436:5432
    networks:
       - app-network

volumes:
  postgres-data:
networks:
  app-network:
    driver: bridge`

Now as you can see I have postgres-data: volume mounted. If i remove this directory before starting docker-compose file things work !!.

I that directory/volume is present the init scripts are ignored !! enjoy!!

GOD!!!