Install postgis into another schema, note the public one

[mcherb]

Hi,

In my production environment, postgis is installed into a schema called postgis. Now I'm using postgis/postgis:10-3.0 but by default this image install postgis into the public schema.

Is there a way to make this schema controlled by a environment variable ?

[ImreSamu]

Is there a way to make this schema controlled by a environment variable ?

I'm not entirely sure I understand your issue,
but if you want to customize the PostGIS initialization script (/docker-entrypoint-initdb.d/10_postgis.sh),
you can use your own custom script by mapping it in a Docker file like this:

• -v $(pwd)/10_postgis.sh:/docker-entrypoint-initdb.d/10_postgis.sh

Additionally, you might need to modify it to correctly use the 'CREATE EXTENSION' command as described here:

• https://www.postgresql.org/docs/10/sql-createextension.html
• This is an untested example:
  • CREATE SCHEMA postgis;
  • CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis;

And this is the the contents of postgis/postgis:10-3.0 - /docker-entrypoint-initdb.d/10_postgis.sh:

$ docker run -it --rm  postgis/postgis:10-3.0 cat /docker-entrypoint-initdb.d/10_postgis.sh

#!/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 IS_TEMPLATE true;
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

[ImreSamu]

related:

https://gis.stackexchange.com/questions/87212/add-postgis-spatial-functions-to-a-custom-schema-other-than-public-in-postgres

[mcherb]

Thanks for your replay :)

[stmtk1]

In this image, the environment variables POSTGRES_DB, POSTGRES_USER and POSTGRES_PASSWORD can be used to change the database name, user name and password name, but not the schema name. So we want to be able to change the schema name using the new environment variable POSTGRES_SCHEMA.

[mertant]

The current workaround for us is to mount a volume containing an init.sql file, in which we drop and re-create the extension(s):

DROP EXTENSION postgis CASCADE;
CREATE EXTENSION postgis WITH SCHEMA my_desired_postgis_schema;

This way there's no need to add a custom initdb shell scrip.

However, it would probably be more optimal if the schema was configurable, as suggested, so that it's created correctly to start with and there's no need for the extra re-creation steps.