Postgresql docker image with PostGIS extension

This image extends the official Postgres image, exactly the 9.6 tagged one.

All the customizations (bash scripts and DBs) are copied inside the /docker-entrypoint-initdb.d/ path.

Here is an example of the Dockerfile configuration:

FROM postgres:9.6

ENV POSTGIS_MAJOR 2.4
ENV POSTGIS_VERSION 2.4.4+dfsg-4.pgdg80+1

RUN apt-get update \
      && apt-cache showpkg postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR \
      && apt-get install -y --no-install-recommends \
           postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR=$POSTGIS_VERSION \
           postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR-scripts=$POSTGIS_VERSION \
           postgis=$POSTGIS_VERSION \
      && rm -rf /var/lib/apt/lists/*


COPY init-db-postgis.sh /docker-entrypoint-initdb.d/init-db-postgis.sh
COPY update-postgis.sh /usr/local/bin/update-postgis.sh
COPY init-user-db.sh /docker-entrypoint-initdb.d/init-user-db.sh
COPY gis.sql.gz /docker-entrypoint-initdb.d/z.sql.gz

EXPOSE  5432

This way when the container starts users and DBs are created from scratch. It’s very important to remember that resources copied inside the /docker-entrypoint-initdb.d directory are executed in alphabetical order. I have renamed gis.sql.gz to z.sql.gz to be sure to have the my-user user created before the DB creation and restore phase.

Following an example of the init-user-db.sh script :

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER my-user;
    ALTER ROLE my-user WITH PASSWORD 'my-password';
        CREATE DATABASE "my-db-name";
        GRANT ALL PRIVILEGES ON DATABASE "my-db-name" TO my-user;
EOSQL

Here an example of the init-db-postgis.sh script:

#!/bin/sh

set -e

# Perform all actions as $POSTGRES_USER
export PGUSER="$POSTGRES_USER"

# Create the 'template_postgis' template db
"${psql[@]}" <<- 'EOSQL'
CREATE DATABASE template_postgis;
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';
EOSQL

# Load PostGIS into both template_database and $POSTGRES_DB
for DB in template_postgis "$POSTGRES_DB"; do
        echo "Loading PostGIS extensions into $DB"
        "${psql[@]}" --dbname="$DB" <<-'EOSQL'
                CREATE EXTENSION IF NOT EXISTS postgis;
                CREATE EXTENSION IF NOT EXISTS postgis_topology;
                CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
                CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
EOSQL
done

And the update-postgis.sh script file:

#!/bin/sh

set -e

# Perform all actions as $POSTGRES_USER
export PGUSER="$POSTGRES_USER"

POSTGIS_VERSION="${POSTGIS_VERSION%%+*}"

# Load PostGIS into both template_database and $POSTGRES_DB
for DB in template_postgis "$POSTGRES_DB" "${@}"; do
    echo "Updating PostGIS extensions '$DB' to $POSTGIS_VERSION"
    psql --dbname="$DB" -c "
        -- Upgrade PostGIS (includes raster)
        CREATE EXTENSION IF NOT EXISTS postgis VERSION '$POSTGIS_VERSION';
        ALTER EXTENSION postgis  UPDATE TO '$POSTGIS_VERSION';
        -- Upgrade Topology
        CREATE EXTENSION IF NOT EXISTS postgis_topology VERSION '$POSTGIS_VERSION';
        ALTER EXTENSION postgis_topology UPDATE TO '$POSTGIS_VERSION';
        -- Install Tiger dependencies in case not already installed
        CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
        -- Upgrade US Tiger Geocoder
        CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder VERSION '$POSTGIS_VERSION';
        ALTER EXTENSION postgis_tiger_geocoder UPDATE TO '$POSTGIS_VERSION';
    "
done

You'll only receive email when Pietrangelo Masala publishes a new post

More from Pietrangelo Masala