run sql script in postgresql docker have included in DockerFile

Question

I am learning to use docker, my goal is to create a postgres sql container in which i create a table, as of now i do not want to add any data just create the table. i was able to create a postgres sql instance but copy/add my sql script (which creates this table) to /docker-entrypoint-initdb.d/ does not seem to work. i can confirm this when i run the psql db i cannot see any table created.

My Dockerfile

"FROM ubuntu

RUN apt-get update && apt-get install -y gnupg

RUN apt-key adv --keyserver hkp://p80.pool.sks-keyservers.net:80 --recv-keys B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8

RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list


RUN apt-get update && apt-get install -y   postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3

USER postgres

RUN    /etc/init.d/postgresql start &&\
    psql --command "CREATE USER docker WITH SUPERUSER PASSWORD 'docker';" &&\
    createdb -O docker docker

RUN echo "host all  all    0.0.0.0/0  md5" >> /etc/postgresql/9.3/main/pg_hba.conf

RUN echo "listen_addresses='*'" >> /etc/postgresql/9.3/main/postgresql.conf

EXPOSE 5432

VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]


copy /scripts/test.sql /docker-entrypoint-initdb.d/

CMD ["/usr/lib/postgresql/9.3/bin/postgres", "-D", "/var/lib/postgresql/9.3/main", "-c", "config_file=/etc/postgresql/9.3/main/postgresql.conf"]"

My sql file

CREATE TABLE email_confirmation_code (
id integer NOT NULL,
code character varying(255) NOT NULL,
user_id integer
);

testing

psql -h localhost -p 32772 -U docker --password
Password for user docker: 
psql (10.6 (Ubuntu 10.6-1.pgdg16.04+1), server 9.3.17)
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

docker=# select * from email_confirmation_code
docker-# ;
ERROR:  relation "email_confirmation_code" does not exist
LINE 1: select * from email_confirmation_code

as you can see the table does not exist. please point out what is the mistake here.

David Maze · Accepted Answer · 2019-02-11 12:21:25Z

6

That's a feature of the standard postgres image; it's not something you get if you install PostgreSQL by hand in a plain Ubuntu container.

If you build a custom image for this, you can just start from that image and add your initialization file. This is a complete Dockerfile:

FROM postgres:9.6
COPY ./scripts/test.sql /docker-entrypoint-initdb.d/

I might not build a custom image for this. Instead, you can use the docker run -v option or the Docker Compose volumes: option to push the script into the container when it starts up (treating it as a configuration file).

(Also remember that these init scripts only run the very first time the database container starts up; if there is already database data they will not re-run.)

answered Feb 11, 2019 at 12:21

David Maze

165k47 gold badges262 silver badges302 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Mendoza Over a year ago

Thanks. Your last comment helped me, I had to delete the volume of container $ docker volume rm docker_database_volume .Finally re-create the image and the Docker container.

Thilak · Accepted Answer · 2019-02-11 10:50:02Z

0

Clean the files/folders inside /var/lib/postgresql/data volume and create a container.

answered Feb 11, 2019 at 10:50

Thilak

1,00510 silver badges12 bronze badges

2 Comments

Whereismywall Over a year ago

can you please elaborate, i am creating the container in my local desktop. so do you want me to delete the files from my local computer's file system or from the containers file system

Thilak Over a year ago

Inside the container, but please check @david-maze answer for better clarification

Collectives™ on Stack Overflow

run sql script in postgresql docker have included in DockerFile

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related