Create a table in a PostgreSQL docker image

Question

I'm new to docker. I added a PostgreSQL image and was able to create a new database instance when the image was launched. I need to create tables in this database from a shell script that will execute the .sql file. When starting a PostgreSQL image does it say that the server is not running? how can we execute the script only after starting the PostgreSQL server in the 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

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

Answer 1

In the docker -entrypoint.sh script, the official docker postgres image 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 

Thus, each .sql file that you want to execute inside your docker image can be placed inside this folder. So my docker file 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 contents 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)