Do not install extra extensions by default

[nyurik]

New containers automatically get database with CREATE EXTENSION ... executed for all postgis related extensions. This results in the creation of numerous tiger-related tables, and possibly other unneeded cruft.

I would like to propose that postgis image by default should not run any CREATE EXTENSION statements, with the possible exception of the postgis extension itself -- after all we can safely assume that if a user picked postgis docker, they need it.

The extra extensions should still be packaged with the image to make their installation simpler.

[nyurik]

Per discussion in gitter.im with @phillipross , the approach should be:

• add a new env var ON_INIT_INSTALL_ALL_EXTENSIONS, with allowed values true and false (feel free to suggest better var naming and/or values)
• If false, new DB script will only initialize with CREATE EXTENSION postgis;, and nothing else
• If true, creates all postgis extensions, e.g. tiger, etc.
• If any other value or not set, print a big warning message saying that for now we default to true, but at some point in the future we will make the default false. Make sure we do this for at least a few months, after which we can change the default, and keep printing the warning for a bit longer, just in case someone didn't update and the warning will help them debug their issue.

[phillipross]

This implementation for phasing out the pre-installation of extensions laid out by @nyurik looks fine to me. I'd like to add a few points here that I touched upon in the gitter discussion.

One thing that I think is very important is avoiding changes that would break consumers of this image who've been using it for a very long time. Changes in behavior could be very burdensome for some folks who have infrastructure that incorporates these images. Pulling the rug out from under them suddenly after several years of seamless operation would probably not be garnering too much confidence or goodwill. The scheme that @nyurik has proposed appears to try to minimize this.

Another point is that there's an inherent cost/benefit balance that we're always faced with: the balance of adding functionality or making optimizations versus extra maintenance overhead. On the surface, @nyurik's scheme doesn't appear to add too much extra ongoing maintenance.

The next point is the balance between optimization and facility. Not automatically installing extensions and having extra objects is a nice optimization, but the flip side of this is having almost everything you need ready for use as soon as you start a container without extra steps. Specifically, I think of the portion of the community that do a lot of presentations, training, etc which help students and new users get onboard and begin learning. They benefit immensely when there are ready-to-go "batteries included" container images with as few steps as possible to getting something up and running. Many container images maintain "slim" variants which act as barebones minimal images, and then more standard images which install/configure more things that are commonly used by the image consumers. I think @nyurik's proposal is a decent middle-ground that avoids the extra maintenance of an image variant, while also providing an easy way to continue having a low-barrier way to get a a container up and running with commonly used extensions already installed.

The somewhat long-winded points I've brought up here are actually broader than just this specific proposal. I'd like to see input from others regarding anything that I've mentioned thus far.

[ImreSamu]

I'd like to see input from others

TLDR: We need a Roadmap & I prefer a full list of wanted extensions ..

• PGPOSTGIS_loading_extensions=postgis,hstore,unaccent,btree_gist

IMHO/Details:

We need a

• Roadmap ( planned new functions )
• a Consistent syntax for the new functions ( ~draft specifications )
• and a Support tiers

(Roadmap)Proposal ( based on my experience) :

• TIER1 support: the current functions

• TIER2 support (the new functions) and I would like - a same "consistent syntax" in any customisation in this list.

  • multiple db-#175
  • multiple users ( and passwords )
  • multiple extensions ( like this issue , postgres extensions and postgis extensions )
  • multiple configuration parameters ( work_mem, ... )
  • auto tunings ( pg_cloudconfig? )
  • automatic RAM disk setup ( for testing , like in the circleci )
  • ...

• TIER3 (experimental) support: We need to create a cookbooks ( with working examples and tests - without published binaries ) for any other request:

  • extending with pgrouting
  • extending with pgxn
  • importing OSM / Naturalearth
  • ...

my (syntax) taste:

• I prefer the "list of want I want" / list of extensions/users/DBs
  • and IF no definitions --> THEN we setup the current default settings for compatibility.

draft - not perfect - example:

• I would like an future proof - "unique prefix" ( like PGPOSTGIS_*? )

example:

PGPOSTGIS_loading_extensions=postgis,hstore,unaccent,btree_gist
PGPOSTGIS_create_users=db1#user1#pw1|db1#user2#pw2|db2#user3#pw3
PGPOSTGIS_create_db=db1,db2

[ImreSamu]

@nyurik : I have created a workaround for hacking /docker-entrypoint-initdb.d/10_postgis.sh
see "hacker example" in the https://github.com/postgis/docker-postgis/issues/191#issuecomment-623124954

so you can add your customised file with as a simple volume file:

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

in you case probably similar code:

rm -f initdb-postgis.sh
wget https://raw.githubusercontent.com/postgis/docker-postgis/master/12-3.0/initdb-postgis.sh
cat initdb-postgis.sh \
    | sed 's/postgis_tiger_geocoder/hstore/g' \
    > 10_postgis.sh

[srstsavage]

Ideally there would be an env var controllable option to not do any template database or extention installation at all (basially surrounding all of 10_postgis.sh in an if statement). This is useful when using bootstrapping SQL which creates the extensions, such as the output of pg_dump or pg_dumpall. In the meantime you can just mount /dev/null to /docker-entrypoint-initdb.d/10_postgis.sh as alluded to above.

[yuriploc]

Hey there. I'd love to use the community-supported version of Postgis, but the installation of extra extensions is somewhat of overhead for the project I'm currently working on.

I'd suggest a similar approach to what kartoza/postgis does.

An environment variable POSTGRES_MULTIPLE_EXTENSIONS on the YAML and an extra check on the init sh.

The default could be to install all, so it wouldn't break current usages of the image.

[Brent-Wood]

Apologies, I asked about this in a new thread before finding this discussion.

I'm particularly interested in being able to easily add postgres_fdw & ogr_fdw to a Docker Postgis database. Also plpython3u, hstore and some of the more popular and useful Postgres monitoring extensions. I do use Timescaledb as well, but I think that might be asking a bit much!!

While there is some discussion above, there doesn't seem to be any resolution. Is there any progress with supporting more extensions in the image (but not necessarily automatically in actual databases - leave that to the end user, as per Postrgres standard practice)?

Thanks,

[nyurik]

@ImreSamu hi, can we revisit this issue? It seems to have gained a lot of traction with the community (lots of :+1: and links from other projects and dups). I recently tried to use it with the Martin project, and Martin automatically "discovered" all the tiger.* tables as valid data sources - exposing them all to the web. It is very easy for the users to run CREATE EXTENSION xxx, but far harder to cleanup whatever random stuff already-installed extensions create.

Thanks for all the hard work maintaining this image!

[abhijitgujar86]

controlling extension default is certainly a must have configuration.