Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
example docker compose for postgresql with db init script
postgres:
image: postgres:9.4
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
create table sometable(id int);
@tamlyn

This comment has been minimized.

Copy link

tamlyn commented Jul 31, 2017

Great stuff! Note that you can link multiple scripts into that folder and they will all run (in alphabetical order):

volumes:
  - ./schema.sql:/docker-entrypoint-initdb.d/1-schema.sql
  - ./data.sql:/docker-entrypoint-initdb.d/2-data.sql
@ozbillwang

This comment has been minimized.

Copy link

ozbillwang commented Oct 2, 2017

One update, arrange the sql files in alphabetical order is important.

@tamlyn has mentioned, but maybe you didn't notice.

if you add two or more sql files, which need be run in sequence, you do need to add the number in sql name for the sequence.

I have two sql, one is used to setup database (setup.sql), the other is used to add data (data.sql). I have to combine them into one sql. Otherwise, it is not guaranteed to run setup.sql first.

init process runs according to name in alphabetical order. So add number in name will be a good way.

@thescientist13

This comment has been minimized.

Copy link

thescientist13 commented Oct 29, 2017

thanks, this worked great for me!

@g-bastianelli

This comment has been minimized.

Copy link

g-bastianelli commented Nov 1, 2017

It' s not working for me i have .
e psql:/docker-entrypoint-initdb.d/init.sql:0: could not read from input file: Is a directory

@rlemaitre

This comment has been minimized.

Copy link

rlemaitre commented Nov 17, 2017

It's not working for me. I have this error

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

When go inside my postgresql container the sql file is not mounted. The /docker-entrypoint-initdb.d directory is empty

@digital-h

This comment has been minimized.

Copy link

digital-h commented Nov 28, 2017

It' s not working for me i have .
e psql:/docker-entrypoint-initdb.d/init.sql:0: could not read from input file: Is a directory

Make sure you point to a directory and not a file. So instead of referencing the files directly do sth like:

volumes:
  - ./path/to/my/host/folder:/docker-entrypoint-initdb.d/
  - ./path/to/my/host/folder:/docker-entrypoint-initdb.d/

And /path/to/my/host/folder should the of course contain the files 1-schema.sql and 2-data.sql.

@mauridb

This comment has been minimized.

Copy link

mauridb commented Dec 3, 2017

thank you! Fine for me! <3

@katkol

This comment has been minimized.

Copy link

katkol commented Feb 22, 2018

Hi,
I tried both mapping folder and file, but nothing worked. I have to run two sql scripts on start of mysql server.

Folder mapping:

volumes:

  • "./folder:/docker-entrypoint-initdb.d/"
    file mapping:

volumes:

  • "./folder/1.sql:/docker-entrypoint-initdb.d/1.sql"
  • "./folder/2.sql:/docker-entrypoint-initdb.d/2.sql"

Is there anything that I am missing. My docker-compose.yml
mysql_server:
image: mysql/mysql-server:5.7
environment:
MYSQL_ROOT_PASSWORD: root
ports:

  • "3306:3306"
    volumes:
    .............................
@alex-eri

This comment has been minimized.

Copy link

alex-eri commented Feb 25, 2018

@katkol this tread about postgres, not mysql.

Mysql Docker image has no docker-entrypoint-initdb.d hook

@vladskiy

This comment has been minimized.

Copy link

vladskiy commented Feb 27, 2018

@alex-eri probably offtopic, but mysql image does have the init hook - https://hub.docker.com/_/mysql/, see Initializing a fresh instance

@kamal-github

This comment has been minimized.

Copy link

kamal-github commented Mar 6, 2018

In my case file is copied, but sql file is not executed? what is the config required for running it too.

@ducnguyenhuy

This comment has been minimized.

Copy link

ducnguyenhuy commented Mar 14, 2018

@kamal-github: Try to remove data volume for postgres data and up your container again.

@rambabu-bheemala

This comment has been minimized.

Copy link

rambabu-bheemala commented Mar 18, 2018

Not working for me either, my docker-compose file looks like this. I see that file present in docker-entrypoint-initdb.d but I think its is not executing it.

postgres:
image: onjin/alpine-postgres:9.5
restart: unless-stopped
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/1-init.sql
ports:
- "5432:5432"

Thanks,
Rambabu

@fewknow

This comment has been minimized.

Copy link

fewknow commented Mar 26, 2018

getting same error as @g-bastianelli . I am on windows 8.1 using docker toolbox and getting this:

psql:/docker-entrypoint-initdb.d/init.sql:0: could not read from input file: Is a directory

@poloche

This comment has been minimized.

Copy link

poloche commented May 4, 2018

I have windows 10 and i am using postgresql 10.3 image and also get this message
/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
here my compose file
version: '2' services: gateway-postgresql: image: postgres:10.3 volumes: - ./postgres-init/:/docker-entrypoint-initdb.d/ #also tried - ./postgres-init:/docker-entrypoint-initdb.d environment: - POSTGRES_MULTIPLE_DATABASES=db1,db2 - POSTGRES_USER=abc - POSTGRES_PASSWORD=abc ports: - 5432:5432
I have folder postgres-init/initdb.sh

@mohamednajiullah

This comment has been minimized.

Copy link

mohamednajiullah commented May 6, 2018

Ordering the scripts alphabetically works but there are a few caveats. If you are ordering using numbers be aware that if you have more than 9 files then the script name that begins with 10 is executed before the script whose name begins with 1

@poloche

This comment has been minimized.

Copy link

poloche commented May 7, 2018

the thing here is that no mather what we have in docker-entrypoint-initdb.d in my case a single file called init,sh, seems that all are ignored there.

@rjurney

This comment has been minimized.

Copy link

rjurney commented Jun 20, 2018

My docker-compose.yaml file looks like this below. I've tried all combinations of possible configurations.

        volumes: 
            - ./dumps:/docker-entrypoint-initdb.d/dumps
            - ./dumps/portal.sql:/docker-entrypoint-initdb.d/portal.sql
            - ./dumps:/docker-entrypoint-initdb.d/dumps.sql

And ./dumps contains portal.sql. However, I get this error: /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/dumps

I am following the directions, so what gives?

UPDATE: I got it to work!

Here is what works:

        volumes: 
            - ./dumps/portal2.sql:/docker-entrypoint-initdb.d/1-portal.sql
...
volumes:
    dumps:

This works. Apparently you have to have a subdirectory which you make a volume down below and then your scripts sit in it and can link as volumes directly inside the service.

@tbaer-c7ks7s

This comment has been minimized.

Copy link

tbaer-c7ks7s commented Jul 4, 2018

@poloche make sure you aren't setting the PGDATABASE environment variable. I had a script that created this database, but got an error that the database didn't exist before the init scripts had a chance to run

@NaitikShah

This comment has been minimized.

Copy link

NaitikShah commented Aug 25, 2018

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

@peterlada

This comment has been minimized.

Copy link

peterlada commented Sep 5, 2018

init scripts are run when there postgres-data folder is created. If the postgres-data folder is initialized, the init scripts will be ignored.

@marcospgp

This comment has been minimized.

Copy link

marcospgp commented Sep 30, 2018

My docker-compose.yaml file looks like this below. I've tried all combinations of possible configurations.

        volumes: 
            - ./dumps:/docker-entrypoint-initdb.d/dumps
            - ./dumps/portal.sql:/docker-entrypoint-initdb.d/portal.sql
            - ./dumps:/docker-entrypoint-initdb.d/dumps.sql

And ./dumps contains portal.sql. However, I get this error: /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/dumps

I am following the directions, so what gives?

UPDATE: I got it to work!

Here is what works:

        volumes: 
            - ./dumps/portal2.sql:/docker-entrypoint-initdb.d/1-portal.sql
...
volumes:
    dumps:

This works. Apparently you have to have a subdirectory which you make a volume down below and then your scripts sit in it and can link as volumes directly inside the service.

@rjurney are you sure that is the reason? I wouldn't think a top-level named volume would have anything to do with a local folder. Does anyone know?

@filipebezerra

This comment has been minimized.

Copy link

filipebezerra commented Apr 1, 2019

init scripts are run when there postgres-data folder is created. If the postgres-data folder is initialized, the init scripts will be ignored.

nice caveat, thanks

@honestcomrade

This comment has been minimized.

Copy link

honestcomrade commented Apr 29, 2019

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.

@nealoke

This comment has been minimized.

Copy link

nealoke commented Jul 3, 2019

@honestcomrade did you manage to fix this?

@siseko

This comment has been minimized.

Copy link

siseko commented Jul 24, 2019

@honestcomrade any luck?

@Juliannnnshipit

This comment has been minimized.

Copy link

Juliannnnshipit commented Jul 29, 2019

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

This comment has been minimized.

Copy link

honestcomrade commented Jul 29, 2019

@Juliannnnshipit this looks like it might help me out, thanks a bunch for the reply.

@SavostinVladimir

This comment has been minimized.

Copy link

SavostinVladimir commented Sep 15, 2019

Hi there! Thank you! Is there way to update my sql script and then update existing image, without data loss? for example I need to add trigger for existing postgres image...

@onjin

This comment has been minimized.

Copy link
Owner Author

onjin commented Sep 15, 2019

Hi there! Thank you! Is there way to update my sql script and then update existing image, without data loss? for example I need to add trigger for existing postgres image...

Hi, initdb.sh scripts are called only if there is no database set, so if you need to migrate current database it's better to run this sql migrations from psql cli for already created databases, and initdb.sh/ scripts will be run for new clean instances.

@thoroc

This comment has been minimized.

Copy link

thoroc commented Sep 24, 2019

FYI: do not pass the host (localhost) in the initialization script or you'll likely to encounter an error about not being able to connect to database.

@harem234

This comment has been minimized.

Copy link

harem234 commented Jan 9, 2020

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/
@boyfunky

This comment has been minimized.

Copy link

boyfunky commented Mar 30, 2020

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

@harem234

This comment has been minimized.

Copy link

harem234 commented Mar 30, 2020

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

@nayibor

This comment has been minimized.

Copy link

nayibor commented Apr 10, 2020

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

@Ezak91

This comment has been minimized.

Copy link

Ezak91 commented Apr 16, 2020

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?

@onjin

This comment has been minimized.

Copy link
Owner Author

onjin commented Apr 16, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.