in a postgres container, where are the imported extensions located?-CodePudding

i would like to create a volume on that directory, and bring in plpython and postgis extensions.

for some reason i am unable to create extensions from within a container.

i have tried to run the postgres container by using the local version and just connecting to it, since the local one has the extensions...but to no avail. \dx shows nothing.

i know that in /usr/share/postgresql/14/extension i can find plpython3u.control

which has the following:

# plpython3u extension
comment = 'PL/Python3U untrusted procedural language'
default_version = '1.0'
module_pathname = '$libdir/plpython3'
relocatable = false
schema = pg_catalog
superuser = true

but i cant find what it's referring to...

my error, after i went inside the container and made that file:

CREATE EXTENSION plpython3u;
FATAL:  extension "plpython3u" has no installation script nor update path for version "1.0"
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

CodePudding user response：

Postgres extensions are implemented as shared library modules (.so) files, generally located in /usr/lib/postgresql/14/lib. So for example, the control file for the plpgsql extension (/usr/share/postgresql/14/extension/plpgsql.control) looks like this:

# plpgsql extension
comment = 'PL/pgSQL procedural language'
default_version = '1.0'
module_pathname = '$libdir/plpgsql'
relocatable = false
schema = pg_catalog
superuser = true
trusted = true

In the module_pathname value, $libdir refers to /usr/lib/postgresql/14/lib, where we find /usr/lib/postgresql/14/lib/plpgsql.so.

If you want to enable a new extension, like plpython3u, you need both the control and sql files as well as the shared library (which needs to be built for the Postgres version that you're running).

Fortunately, it looks like the plpython extension is already packaged and installable using the stock postgres image. The following Dockerfile will produce an image that has the plpython extension installed:

FROM postgres:14

RUN apt-get update && \
    apt-get -y install postgresql-plpython3-14 && \
    apt-get clean all

If we build an image from that Dockerfile:

docker build -t postgres-plpython .

And then start a container:

docker run -d --name postgres -e POSTGRES_PASSWORD=secret postgres-plpython

We can docker exec into the container and add the extension to a database:

$ docker exec -it postgres psql -U postgres
psql (14.3 (Debian 14.3-1.pgdg110 1))
Type "help" for help.

postgres=# create extension plpython3u;
CREATE EXTENSION