Postgres: Language pack availability/strategy for <= 9.5 alpine

[cdaringe]

problem

language packs (plpython) aren't available in any alpine version official repos for pg <= 9.5.x

discussion

alpine doesn't ship a plpython for postgres 9.5.x/9.5.10, and im struggling to find documentation on how to load it.

• i've tried downloading the 9.5 source and compiling it, but still not clear which artifacts are relevant and where they go!
• with some help on getting it loaded, i'd gladly flip a PR back here to the README.md to help others w/ the same issue

[tianon]

Ah, this is a hard one -- from what I can tell, the only official way to build PL/Python is by supplying --with-python on the ./configure line while building PostgreSQL itself. If you were able to get it built somehow after the fact, I _think_ it would belong in /usr/share/postgresql/10/extension/, given the error message given when trying to CREATE it:

postgres=# CREATE EXTENSION plpythonu;
ERROR:  could not open extension control file "/usr/share/postgresql/10/extension/plpythonu.control": No such file or directory

See also https://github.com/docker-library/postgres/issues/340, https://github.com/docker-library/postgres/issues/290, and https://github.com/docker-library/docs/pull/1204.

[tianon]

I was able to successfully perform CREATE EXTENSION plpythonu; within an image FROM postgres:10-alpine by using the following Dockerfile (which borrows heavily from the main PostgreSQL Dockerfile):

FROM postgres:10-alpine

RUN set -eux; \
    \
    apk add --no-cache --virtual .fetch-deps \
        ca-certificates \
        openssl \
        tar \
    ; \
    \
    wget -O postgresql.tar.bz2 "https://ftp.postgresql.org/pub/source/v$PG_VERSION/postgresql-$PG_VERSION.tar.bz2"; \
    echo "$PG_SHA256 *postgresql.tar.bz2" | sha256sum -c -; \
    mkdir -p /usr/src/postgresql; \
    tar \
        --extract \
        --file postgresql.tar.bz2 \
        --directory /usr/src/postgresql \
        --strip-components 1 \
    ; \
    rm postgresql.tar.bz2; \
    \
    apk add --no-cache --virtual .build-deps \
        coreutils \
        dpkg-dev dpkg \
        gcc \
        libc-dev \
        libedit-dev \
        make \
        python-dev \
        zlib-dev \
    ; \
    \
    cd /usr/src/postgresql; \
    gnuArch="$(dpkg-architecture --query DEB_BUILD_GNU_TYPE)"; \
# explicitly update autoconf config.guess and config.sub so they support more arches/libcs
    wget -O config/config.guess 'https://git.savannah.gnu.org/cgit/config.git/plain/config.guess?id=7d3d27baf8107b630586c962c057e22149653deb'; \
    wget -O config/config.sub 'https://git.savannah.gnu.org/cgit/config.git/plain/config.sub?id=7d3d27baf8107b630586c962c057e22149653deb'; \
# configure options taken from:
# https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/rules?h=9.5
    ./configure \
        --build="$gnuArch" \
        --prefix=/usr/local \
        --with-includes=/usr/local/include \
        --with-libraries=/usr/local/lib \
        --with-python \
    ; \
    cd src/pl/plpython; \
    make -j "$(nproc)"; \
    make install; \
    find /usr/local -iname '*plpython*' \
        -exec scanelf --needed --nobanner --format '%n#p' '{}' + \
        | tr ',' '\n' \
        | sort -u \
        | awk 'system("[ -e /usr/local/lib/" $1 " ]") == 0 { next } { print "so:" $1 }' \
        | xargs -rt apk add \
    ; \
    apk del .fetch-deps .build-deps; \
    cd /; \
    rm -rf \
        /usr/src/postgresql \
        /usr/local/share/doc \
        /usr/local/share/man \
    ; \
    find /usr/local -name '*.a' -delete

So in short, this _is_ possible to add after the fact without recompiling all of PostgreSQL. :metal:

[qcq]

I am not really understand, if I can just mkdir such folder in container will solve this question? @tianon

[tianon]

No, you will need to build the desired extensions similar to the example
I've supplied above or use Debian-based images instead and install the
appropriate packages.

[jcuenod]

@tianon sorry to revive this old thread but I am wanting to get plpython3 working in a docker container and I don't quite understand why I'm failing. I succeeded with CREATE EXTENSION plpython2u; having built your Dockerfile but plpython3u fails - it seems not to have been built.

I tried modifying the Dockerfile with python3-dev in the apks and --with-python3 in ./configure but to no avail:

• --with-python3 is not an option (which kind of surprised me)
• And apparently with python3-dev make can't find Python.h.

In file included from plpy_elog.c:11:0:
plpython.h:57:20: fatal error: Python.h: No such file or directory
 #include <Python.h>
                    ^
compilation terminated.

I guess I need to give the compiler python3 flags somehow...

EDIT:

I was so close: For posterity, the solution for me was to install python3-dev (instead of python-dev) and, just before running ./configure, add the line:

    export PYTHON=python3; \

[tianon]

Hah, I should've checked your edit before I played with it! For reference, the place I found that PYTHON=xxx line was https://salsa.debian.org/postgresql/postgresql/blob/85bd067c4a43a259c34151a4af205d1951e26119/debian/rules#L118 (from the official Debian packaging). :+1:

(Glad you got it figured!)