Create table in PostgreSQL docker image

Question

I am new to docker. I have extended PostgreSQL image and able to create new DB instance while running the image.I need to create tables in that DB from shell script that will execute .sql file. While running PostgreSQL image it says server not started ? how we can execute script only after the PostgreSQL server starts in docker container?

Dockerfile:

FROM postgres:9.3
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker 
RUN apt-get update && apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3
ADD initialize.sh /home/MM0312/docker-entrypoint-initdb.d/initialize.sh
CMD ["/home/MM0312/docker-entrypoint-initdb.d/initialize.sh"]

initialize.sh

#!/bin/bash
set -e
set -x

echo "******PostgreSQL initialisation******"
gosu docker psql -h localhost -p 5432 -U docker -d $docker -a -f createDatabase.sql

createDatabase.sql file

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

Answer 1

In the docker-entrypoint.sh script of the official docker image of postgres is written:

psql+=( --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" )

        echo
        for f in /docker-entrypoint-initdb.d/*; do
            case "$f" in
                *.sh)     echo "$0: running $f"; . "$f" ;;
                *.sql)    echo "$0: running $f"; "${psql[@]}" < "$f"; echo ;;
                *.sql.gz) echo "$0: running $f"; gunzip -c "$f" | "${psql[@]}"; echo ;;
                *)        echo "$0: ignoring $f" ;;
            esac
            echo
done

So every .sql file you want to execute inside your docker image can just be placed inside that folder. So my dockerfile looks like

FROM postgres:9.3
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker
ADD CreateDB.sql /docker-entrypoint-initdb.d/

And the content of my CreateDB.sql:

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

So I just start my container with:

docker run -d my-postgres

To check:

docker exec -it 6250aee43d12 bash
root@6250aee43d12:/# psql -h localhost -p 5432 -U docker -d docker
psql (9.3.13)
Type "help" for help.

docker=# \c
You are now connected to database "docker" as user "docker".
docker=# \dt
           List of relations
 Schema |    Name     | Type  | Owner
--------+-------------+-------+--------
 public | web_origins | table | docker
(1 row)

You can find the details for mysql here in this blog.