ERROR: required extension "postgis" is not installed

[Starefossen]

Host OS: Ubuntu 14.04
Docker version: 1.3.3

Dockerfile:

FROM mdillon/postgis:9.3

# Add init script
ADD initdb-foo.sh /docker-entrypoint-initdb.d/foo.sh

# Add locale
RUN echo "nb_NO.UTF-8 UTF-8" >> /etc/locale.gen && locale-gen

initdb-foo.sh:

#!/bin/sh
POSTGRES="gosu postgres postgres"

$POSTGRES --single -E <<EOSQL
CREATE DATABASE foo template template0 lc_collate 'nb_NO.UTF-8' lc_ctype 'nb_NO.UTF-8';
EOSQL

$POSTGRES --single foo -E <<EOSQL
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
EOSQL

I get the following output when I run my Dockerfile:

PostgreSQL stand-alone backend 9.3.5
backend> statement: CREATE DATABASE foo template template0 lc_collate 'nb_NO.UTF-8' lc_ctype 'nb_NO.UTF-8';

backend> 
PostgreSQL stand-alone backend 9.3.5
backend> statement: CREATE EXTENSION postgis;

ERROR:  type addbandarg[] does not exist
STATEMENT:  CREATE EXTENSION postgis;

backend> statement: CREATE EXTENSION postgis_topology;

ERROR:  required extension "postgis" is not installed
STATEMENT:  CREATE EXTENSION postgis_topology;

backend> LOG:  database system was shut down at 2015-01-16 14:10:49 UTC

[purpleP]

Yeah, me too. But I can create extension through pgadmin though. I don't know how extensions are working in postgres, so don't really know how can we fix this.

[md5]

Hey guys. I just saw this report. For some reason this repo wasn't on my watch list 😬

I'm swamped with a project at work right now, but I'll look into this when I have some time.

@Starefossen Off the top of my head, my guess is that the init script you're adding is running before the initdb-postgis.sh script, so the extension is not initialized when it runs. I'd need to see a more complete docker run output to have more confidence that that's what's happening though.

If that is the issue, then you should be able to work around it by naming your initdb script something lexicographically after postgis.sh (e.g. postgis-foo.sh). I don't have time to test at the moment.

[purpleP]

I also thought that my script was running before yours. And it was. So I changed the name and I am sure that it was executed after yours, but still haven't worked.

[purpleP]

Also if you'll figure out the way to create extensions in init scripts, then what extensions should be created can be configured via environment_variables.

[purpleP]

Here is output of my docker build.

ostgreSQL stand-alone backend 9.3.5
backend> backend>
PostgreSQL stand-alone backend 9.3.5
backend> statement: CREATE DATABASE template_postgis

backend> statement: UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis'

backend>
PostgreSQL stand-alone backend 9.3.5
backend> backend>
PostgreSQL stand-alone backend 9.3.5
backend> backend>
PostgreSQL stand-alone backend 9.3.5
backend> statement: CREATE EXTENSION IF NOT EXISTS postgis;

ERROR: type addbandarg[] does not exist
STATEMENT: CREATE EXTENSION IF NOT EXISTS postgis;

backend> statement: CREATE EXTENSION IF NOT EXISTS postgis_topology;

ERROR: required extension "postgis" is not installed
STATEMENT: CREATE EXTENSION IF NOT EXISTS postgis_topology;

[Starefossen]

I'm getting the same result when I rename my init script to run after the postgis init script. The full docker run log is available here.

[md5]

@Starefossen Could you try using template_postgis as the template for your foo database instead of template0?

I think the issue is that the Postgis stuff is only getting installed into template_postgis, not template0, so addbandarg[] is not available when you go to CREATE EXTESION postgis.

I was able to make a little progress here I think: https://gist.github.com/md5/c0de85617891e1c17fcd

[Starefossen]

First, thanks a lot for taking your time to help me with this issue!

With your suggested alternations to the CREATE DATABASE query I got this error:

ERROR:  new collation (nb_NO.UTF-8) is incompatible with the collation of the template database (C)
HINT:  Use the same collation as in the template database, or use template0 as template.
STATEMENT:  CREATE DATABASE foo template template_postgis lc_collate 'nb_NO.UTF-8' lc_ctype 'nb_NO.UTF-8';

Then, I removed the lc_collate and lc_ctype from the above statement to see what happened and got the following errors:

PostgreSQL stand-alone backend 9.3.5
backend> statement: CREATE DATABASE foo template template_postgis;

backend> 
PostgreSQL stand-alone backend 9.3.5
backend> statement: CREATE EXTENSION postgis;

ERROR:  PostGIS is already installed in schema 'public', uninstall it first
STATEMENT:  CREATE EXTENSION postgis;

backend> statement: CREATE EXTENSION postgis_topology;

ERROR:  required extension "postgis" is not installed
STATEMENT:  CREATE EXTENSION postgis_topology;

I tried to run my original CREATE DATABASE query through psql against a running postgis-container and it worked without errors. Is my init script perhaps executed before postgis has finished loading?

[md5]

You don't need to run CREATE EXTENSION postgis if you have template template_postgis.

As for the other error, I think you need to change nb_NO.UTF-8 to nb_NO.utf8 as in my example script.

[md5]

FYI, I just added postgis_topology to the template database in this commit: aae37e6

It's building on the Docker Registry now.

[md5]

I've created a PR over at #3 to add a "Usage" section to the README. Let me know what you guys think.

[purpleP]

If I understand correctly to use postgis I need to extend your image? I mean, you're saing "Use template_postgis for you database", but the database is created in official postgres image without any lines of code from me. I think that we should be ably to use your images in that way also or it wouldn't make much sense to use it at all. The database name shouldn't be hardcoded in init script or dockerfile, It should be created automatically and be ready to use after building the image.

What do you think?

[md5]

@purpleP I didn't mean to imply that. I was only providing the derived image instructions because that seemed to be what @Starefossen was trying to achieve. You should be able to create a PostGIS-enabled database in the normal way using this image. Furthermore, I think it would be reasonable for the default database created by the container to be PostGIS-enabled as you suggest. However, my investigation has found that creating a PostGIS-enabled database in postgres --single mode may not be possible (see below).

Stepping back a bit, it seems I was a bit hasty in this comment. When I created this image, I did it for the purpose of running iNaturalist locally, not to act as a general-purpose PostGIS image. As such, I didn't really read up on PostGIS or the current state of Postgres before creating it.

It turns out that my comment about CREATE EXTENSION postgis was incorrect due to my ignorance of the CREATE EXTENSION mechanism. It seems that CREATE EXTENSION is the preferred way to enable PostGIS in Postgres 9.1+ and that the template mechanism is an older mechanism that is only necessary for 9.0 or earlier.

The actual problem with type addbandarg[] does not exist seems to be that Postgres's implicit array types (e.g. addbandarg[]) are not available in --single mode. To test, I updated @Starefossen's Dockerfile and initdb-foo.sh to look like this:

Dockerfile

FROM postgres:9.4

# Add init script
ADD initdb-foo.sh /docker-entrypoint-initdb.d/foo.sh

initdb-foo.sh

#!/bin/sh
POSTGRES="gosu postgres postgres"

$POSTGRES --single -E <<EOSQL
CREATE DATABASE foo template template0
EOSQL

$POSTGRES --single foo -E <<EOSQL
CREATE TYPE footype AS (id int)
CREATE TABLE a_table (a footype)
CREATE TABLE b_table (b footype[])
EOSQL

When I do this, I get the error ERROR: type "footype[]" does not exist at character 25

[md5]

@purpleP I opened #4 to track enabling PostGIS on the default database created by the parent postgres image.