Run SQL scripts inside Dockerfile

Question

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?

Answer 1

======================== 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.