I am using next Dockerfile to setup postgres database engine
FROM postgres:9.6.5
ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxx
ENV POSTGRES_DB postgres
ADD localDump.sql /docker-entrypoint-initdb.d
i need to create ROLE CREATE ROLE read_only; before initializing my database and before running localDump.sql script.
So how can i run SQL scripts inside Dockerfile direct?
======================== Correct Answer Start ========================
According to the Postgres image documentation you can extend the original image by running a script or an SQL file.
If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files and source any *.sh scripts found in that directory to do further initialization before starting the service.
For your Dockerfile, you could do something like this.
FROM postgres:9.6.5
ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxx
ENV POSTGRES_DB postgres
ADD create-role.sh /docker-entrypoint-initdb.d
ADD localDump.sql /docker-entrypoint-initdb.d
You will also need the create-role.sh script to execute the plsql command
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
CREATE ROLE read_only;
EOSQL
Note that I'm not a SQL DBA, the plsql command is just an example.
======================== Correct Answer End ========================
Original answer left for posterity :
Can you use psql ? Something like that.
FROM postgres:9.6.5
ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxx
ENV POSTGRES_DB postgres
RUN psql -U postgres -d database_name -c "SQL_QUERY"
ADD localDump.sql /docker-entrypoint-initdb.d
You should find how to run SQL Query for Postgres from bash, and then add a RUN instruction in your Dockerfile.
docker exec -it CONTAINER_ID bash And then check the same command from inside the container ?