postgresql - How to create postgres extension inside the container?

Question

I have to install hstore to my docker postgres
Here is my ordinary command in the shell to execute my need

psql -d template1 -c 'create extension hstore';

If I remove that line my container, it works, but I have execute hstore installation by myself and I have to tell everyone in my project to do so which is not a good practice

Here is my yml file

  postgres:
    build:
      context: .
      dockerfile: dockerfiles/devdb.dockerfile
    environment:
      POSTGRES_USER: uih
      POSTGRES_PASSWORD: uIhbod!
      POSTGRES_DB: uih_portal
    ports:
        - "5433:5432"

Here is my docker file devdb.dockerfile

FROM postgres:9.5

RUN mkdir -p /var/lib/postgresql-static/data
ENV PGDATA /var/lib/postgresql-static/data

# psql -d template1 -c 'create extension hstore;'
CMD ["psql", "-d", "template1", "-c", "'create extension hstore;'"]

RUN echo "hstore extension installed"

After build I can not get it run

$ docker-compose up postgres
Recreating uihportal_postgres_1
Attaching to uihportal_postgres_1
postgres_1       | psql: could not connect to server: No such file or directory
postgres_1       |      Is the server running locally and accepting
postgres_1       |      connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
uihportal_postgres_1 exited with code 2

Question:

How to install hstore from the dockerfile?
I want to make an image and re-use it for the entire project for my team

Answer 1

It's failing because Postgres isn't running in the container during the build, it's only started in the CMD when a container runs.

The entrypoint script for the Docker image has support for running setup steps - any .sql or .sh files in the /docker-entrypoint-initdb.d directory will be executed when the container starts.

So you can do this by putting your extension setup in a SQL script, and copying the script into the image in the init directory:

> cat hstore.sql
create extension hstore
> cat Dockerfile
FROM postgres:9.5
COPY hstore.sql /docker-entrypoint-initdb.d

When you build that image, the SQL script will be in the right place to be executed, so whenever a container runs from the image it will install the extension.