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.

[purpleP]

md5, Yes, I've posted bugreport about addbandargs[] not working in single-user mode to postgresql team.

[md5]

Great! You saved me the trouble of doing that later today.

Do you have a URL for the bug report?
On Jan 26, 2015 6:55 AM, "purpleP" notifications@github.com wrote:

md5, Yes, I've posted bugreport about addbandargs[] not working in
single-user mode to postgresql team.

—
Reply to this email directly or view it on GitHub
#2 (comment).

[md5]

@purpleP I took a look at pgsql-bugs and didn't find any mention of addbandarg. Unless I hear otherwise, I'm going to proceed with submitting a bug report myself when I get some time to work up a repeatable test case (preferably in the form of a patch with a regression test).

[md5]

I got some time to join #postgresql on Freenode today and had a chat with @RhodiumToad who helpfully pointed me to this thread on pgsql-bugs: http://www.postgresql.org/message-id/20150126124133.19763.60605@wrigleys.postgresql.org

Long story short, there is code in Postgres that explicitly avoids creating implicit array types in --single mode:

warrior2031(at)mail(dot)ru writes:

[ Can't create postgis extension in single-user mode ]

Why in the world would you think that's a good thing to do?

Single-user mode is a barely-documented disaster recovery aid.
It's not meant for routine activity. There are a whole lot
of behaviors you want that are turned off in single-user mode.

The specific reason why this doesn't work is this bit in
heap_create_with_catalog:

/*
 * Decide whether to create an array type over the relation's rowtype. We
 * do not create any array types for system catalogs (ie, those made
 * during initdb). We do not create them where the use of a relation as
 * such is an implementation detail: toast tables, sequences and indexes.
 */
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
                          relkind == RELKIND_VIEW ||
                          relkind == RELKIND_MATVIEW ||
                          relkind == RELKIND_FOREIGN_TABLE ||
                          relkind == RELKIND_COMPOSITE_TYPE))
    new_array_oid = AssignTypeArrayOid();

We could possibly develop some other mechanism for detecting whether
we're within the initdb sequence, but I can't get very excited about
treating this as a bug. Single-user mode hasn't been considered a
standard user environment since maybe the early 90s.

      regards, tom lane

[md5]

ping @tianon @yosifkit

[tianon]

Single-user mode is a barely-documented disaster recovery aid.

This is basically the exact conclusion I came to when I started digging into single-user mode more to improve our entrypoint code to be more flexible. 😞

[tianon]

For the record, MySQL's --init-file and mysql_embedded have similar limitations and issues too. 😢

[md5]

One of the suggestions on IRC was to start up the server on an alternate port to perform the initialization before switching to the actual port. At first I balked at this idea, but perhaps it's not that bad... Let's look at the current UX:

1. User starts a container derived from postgres for the first time. This image has a script in docker-entrypoint-initdb.d to do some initialization.
2. The user's app attempts to connect to port 5432 on the postgres container through container linking or port forwarding. The app expects the db to already be created if it is able to connect.

If the script in step 1 is a long-running one, the user will get ECONNREFUSED in step 2 until the initialization script has completed.

Let's say that instead of doing the mechanics of step 1 using --single, we were to start up postgres listening only to a Unix-domain socket and perform the initialization in the docker-entrypoint-initdb.d scripts using normal psql commands. If we were to do this, the UX would be exactly the same; the user still would not be able to connect to Postgres in the container until all the entrypoint scripts had completed and the container started listening on its normal port.

The big drawback of this approach is that the postgres --single approach is already documented and switching over would break any existing derived images using that method if there were actually a server running against the same datadir...

BTW, this same sort of approach would also work for the MySQL case I believe.

[md5]

I'd also say that this is just one more instance of the general problem of differentiating when a Docker container is started v. when it's actually available to do its job.

[md5]

Looks like there are a fair number of public repos on Github using the docker-entrypoint-initdb.d functionality currently: https://github.com/search?q=docker-entrypoint-initdb.d&type=Code&utf8=%E2%9C%93

Perhaps it's worth nudging some more to get postgres --single to work better. I haven't taken the time to look at the Postgres source for the uses of IsUnderPostmaster to see what other special cases there are in the --single behavior, but it may not actually be that hard to change.

Whether or not it would be backported to earlier Postgres versions is of course another question, one which I assume would be answered in the negative.

[md5]

Here's some discussion related to container readiness v. startedness: moby/moby#7445

[purpleP]

md5, I thought about starting postgres as a deamon process in CMD command and then execute psql commands. What do you think about that?

[md5]

Are you just talking about using docker exec -it CONTAINER gosu postgres psql or something?

That works fine for creating things after the container is started, but not if you want them to be created before Postgres starts responding to requests on port 5432.

[Starefossen]

Could we start the Postgres deamon on an alternate port during the bootstrap?

[md5]

@Starefossen yes, I think so. That's what I was getting at in this comment: #2 (comment)

The only issue with that is that all the existing Dockerfiles using postgres --single would break.

[Starefossen]

Maybe @strk or @robe2 from Postgis could have a look at this issue too? I think would be very beneficial for the Postgis community to have a solid Docker image for their applications.

[RhodiumToad]

I looked into the IsUnderPostmaster usage (see the pgsql-bugs thread). This one case regarding array types is the only one that actually changes the behavior of SQL commands. I think a strong case can be made that it should be fixed.

[md5]

For anyone interested, I've started a thread about this issue on the pgsql-pkg-docker mailing list: http://www.postgresql.org/message-id/CAG077fBjVukagYqub_yhHzF_pLmj=3=k5ORSsXu6kG+cy6xNVw@mail.gmail.com

[md5]

It should be possible to make this work now. The *.sh script will need to be updated to use psql instead of postgres --single, but there shouldn't be any changes besides that.

[md5]

Hi all. I pushed a branch with a proposed update to add PostGIS to the default database created by the postgres parent image (i.e. $POSTGRES_DB, which defaults to postgres, or $POSTGRES_USER if that is set to something other than postgres). See #11.

[Starefossen]

Good job @md5 👏🏼

[md5]

Thanks @Starefossen! 👍

Any chance you've got some time to test it out?

[purpleP]

https://github.com/purpleP/docker-postgis That's how I do it. Maybe someone would use it too. We actually can generalize that and instead of creating postgis extensions execute any sh script like they do in docker postgis repository.

[md5]

@purpleP Did you take a look at #11? I'm using the postgres image's /docker-entrypoint-initdb.d extension mechanism to install the extensions into the user-chosen POSTGRES_DATABASE (defaulting to postgres or the name of POSTGRES_USER) using a custom script called initdb-postgis.sh.

It looks like you instead forked postgres and modified the docker-entrypoint.sh directly instead of adding an extension script under /docker-entrypoint-initdb.d.

[purpleP]

@md5 I did exactly that, because guys from postgres doesn't recommend to use single user mode for anything except recovery from failures feature. I did look at 11, but I haven't tried it yet. If it works, then why haven't you've done that earlier?

[md5]

Because I wanted this image to be based on the postgres image and it still required postgres --single until docker-library/postgres#75 was merged three weeks ago.

That merge was a breaking change for users, quite a number of whom complained about it, so I can understand (and supported) the caution that went into making that change.

I personally still hoped the Postgres folks would change their minds and fix --single, since I think it's a better approach.

[purpleP]

@md5 Oh, I didn't know about this change in docker library. That's a great news.

[md5]

@purpleP 👍

I'll probably merge #11 early next week, though I may want to get some tests added first (see #12).

[purpleP]

@md5 Hi again. I finally had the time to check your new branch. after building and running your image I've tried to do the following - execute CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; and got following errors.

ERROR: PostGIS is already installed in schema 'public', uninstall it first
********** Error **********

ERROR: PostGIS is already installed in schema 'public', uninstall it first
SQL state: P0001

I conclude from that that postgis is indeed installed right after starting database, but I'm not sure because usually this sql gives me another error (if postgis installed (and I do that via exactly this sql))- something like ERROR already exists.

[purpleP]

@md5 I can also confirm that our django app that uses postgis seems to works fine.

[md5]

@purpleP Glad to hear your app works.

Out of curiosity, what command did you use to start the container?

[purpleP]

@md5 docker run -itd -p 5432:5432 -e POSTGRES_PASWWORD=pass -e POSTGRES_USER=user some_postgis.

[md5]

I'm assuming some_postgis is a local build of the branch from #11.

Assuming you're connecting to the user database, you shouldn't have to run CREATE EXTENSION since it has already been run. You may want to run CREATE EXTENSION IF NOT EXISTS instead, which should avoid the error.

I'm still curious about the difference in the error message you mention, though. I'd like to rule out some sort of mistake on my part before I merge #11.

[purpleP]

@md5 You are assuming right. Well, I don't know why are error is different, but we can try to solve that.

[purpleP]

@md5 For one thing you are creating template database, right? And I usually just executing create extension sql

[md5]

@purpleP Creating template_postgis should have no effect on other databases.

[md5]

I just pushed another commit (deb7d56) to #11 after doing some more debugging. Turns out there were all kinds of issues with my CREATE EXTENSION changes. In particular, the file-based method was still being used to install PostGIS under Postgres 9.4... 😖 Not to mention my boneheaded use of echo...

I also made changes to the initialization for 9.0 to add raster support (cf. f705708).

I believe everything should be working properly now.

[md5]

I just merged #11. I think this should be fixed.

@Starefossen please reopen if you don't think that's the case. Others can open new issues as needed (hopefully there won't be any).