Postgresql docker image with PostGIS extension
November 22, 2020•385 words
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