How can I enable plpythonu out of the box?

[xermus]

I am trying to migrate a Postgres 9 database to this docker-postgis Postgres 11 container. However, I am getting the error

ERROR:  language "plpythonu" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.

as plpythonu does not seem to be enabled. When specifying Python (plpythonu) as an additional extension to be available, the PG version created does still not support plpythonu (select * from pg_language;).

My Docker environment is running on an Ubuntu 18 server.

Any idea how to enable plpythonu?

[NyakudyaA]

Hi, @xermus Just a quick google search on your problem it seems it needs you to do a bit more configurations as opposed to just doing a create extension. http://raghavt.blogspot.com/2017/09/create-language-plpython3u-postgresql-96.html

[xermus]

Thanks.

I need Postgres 11.2 - because plpythonu seems to be only compatible with 11.2 (there seems not to be a plpythonu version out there for 11.1). How can I install "docker-postgis" with Postgres 11.2?

docker pull kartoza/postgis:11-2 

did not work

[NyakudyaA]

Hi, @xermus I suggest you create a PR using the following conditions and you can test it on your side and we can merge if it will benefit a lot of people.

Currently, you might have to rebuild the image yourself using these settings

From line 11 and 12 https://github.com/kartoza/docker-postgis/blob/develop/Dockerfile#L11

RUN sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Change it to the following

RUN sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" > /etc/apt/sources.list.d/postgresql.list'
RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | apt-key add -

And also install apt-get install postgresql-plpython-11 whilst building the image.

Then in the docker-compose use the following environment variable - POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,plpythonu

[xermus]

Hi @NyakudyaA! Thanks for your feedback. Python 2 is still the default version as of Postgres v11.2 -- preferred by the Postgres Group over Python 3. Here is what I did to make Python 2 work:

1. Clone the Github repository and change the Dockerfile as following:

RUN sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" > /etc/apt/sources.list.d/postgresql.list'
RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | apt-key add -

#-------------Application Specific Stuff ----------------------------------------------------

# Install Phyton 2.7
RUN apt-get update; apt-get install -y python2.7 python-pip libreadline-dev zlib1g-dev

# We add postgis as well to prevent build errors (that we dont see on local builds)
# on docker hub e.g.
# The following packages have unmet dependencies: -- including postgresql-plpython-11
RUN apt-get update; apt-get install -y postgresql-client-11 postgresql-common postgresql-11 postgresql-11-postgis-2.5 postgresql-11-pgrouting netcat postgresql-plpython-11

2. I created the image using:

docker build -t <your-image-name> .

3. I ran the container using:

docker run -d --name "<your-container-name>" --security-opt apparmor=unconfined -p 25432:5432 -e POSTGRES_USER=<username> -e POSTGRES_PASS=<password> -e POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,plpythonu,plpgsql -v /data/postgres-data:/var/lib/postgresql -t <your-image-name>

(note that you must have created the host directory /data/postgres-data beforehand)

4. I logged into the Postgres database

docker exec -it <your-container-name> /bin/bash

and enabled Python: (which is PER DATABASE)

su postgres
psql
CREATE database <database-name>;
\c <database-name>;
CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler;
UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpythonu';

and voilá you are able to use Python as a language in Postgres 11.2. If you have many database you do not want to repeat the last step many times (in this case you can assign plpythonu to "template1" and Python will be available for all databases). Hope this will help someone else as it took some time to put these pieces together.

I used this setup to migrate a Postgres 9.5 database (running in a bare metal server) to Postgres 11.2 in a Docker contrainer.

[NyakudyaA]

I will close this as you have managed to achieve the end goal. We will however Not be adding this to the Docker recipe because Plpython is untrusted as documented here https://www.postgresql.org/docs/10/plpython.html