Failed to build postgres docker container with initial schema

Question

I want to setup some initial tables in docker postgres.

I look at https://stackoverflow.com/a/34753186/72437 , thought it would be straight forward.

docker-compose.yml

version: '2'
services:
  postgres:
    build:
      context: ./postgres
      dockerfile: Dockerfile
    restart: always
    ports:
      - "5432:5432"

postgres/postgres.sql

CREATE TABLE my_table (
    client_id character varying(36) NOT NULL,
    value character varying(255)
);

postgres/Dockerfile

FROM postgres:latest
ENV POSTGRES_DB my_db
ADD postgres.sql /docker-entrypoint-initdb.d/

Then, I execute

docker-compose build
docker-compose up

I try to exam my database by

docker ps
docker exec -it bb54abc9be16 bash
root@bb54abc9be16:/# psql -h 127.0.0.1 -U postgres
psql (10.1)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges

-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

Seem like my_db DB and my_table table are not created.

May I know what thing I had missed out?

My docker-compose version is `docker-compose version 1.18.0, build 8dd22a96`? Are you using the exact command as I do? — Cheok Yan Cheng, Feb 05 '18 at 20:11
Using `FROM postgres:10` will still fetch 10.1 still. I don't think that related to postgres version problem — Cheok Yan Cheng, Feb 05 '18 at 20:12

Shahriar · Accepted Answer · 2018-02-05T21:24:16.770

Run docker-compose down command to cleanup your existing container and volume.

I have followed your process. And it works for me.

$ docker-compose down
$ docker-compose build
$ docker-compose up

When I use docker-compose up first time, It creates a container and local volume for /var/lib/postgresql/data.

docker-compose images
   Container       Repository      Tag       Image Id      Size 
----------------------------------------------------------------
test_postgres_1   test_postgres   latest   4514d60c85fb   274 MB

Then it starts with same container and volume with each time in your case.

So if you first use docker-compose up when you didn't provide init script, and then change Dockerfile to add init script. It will not work unless you delete your existing Container.

Because, in local volume PG_VERSION file is created. And which is checked before database initialization.

Thats why

$ docker-compose down

This will clean up your existing container and local volume.

Thanks for explanation. `docker-compose down` really help. – Cheok Yan Cheng Feb 05 '18 at 21:49 — Cheok Yan Cheng, Feb 05 '18 at 21:49

Failed to build postgres docker container with initial schema

docker-compose.yml

postgres/postgres.sql

postgres/Dockerfile

1 Answers1