Skip to content

Instantly share code, notes, and snippets.

@onjin
Created September 5, 2016 09:17
Show Gist options
  • Save onjin/2dd3cc52ef79069de1faa2dfd456c945 to your computer and use it in GitHub Desktop.
Save onjin/2dd3cc52ef79069de1faa2dfd456c945 to your computer and use it in GitHub Desktop.
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);
@onjin
Copy link
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.

@M0r13n
Copy link

M0r13n commented Jul 30, 2020

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!

@anisbhsl
Copy link

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

@harryghgim
Copy link

Thank you so much. It saved my time

@mdillenk
Copy link

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

@kevinhaas
Copy link

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

@Gui-mp8
Copy link

Gui-mp8 commented Dec 10, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment