10

I am trying to install a PostgreSQL dockerised service, with plpython. I am able to build the image successfully, but when I come to run the container, I get the following error:

ERROR: could not open extension control file "/usr/share/postgresql/9.5/extension/plpython3u.control": No such file or directory STATEMENT: CREATE EXTENSION "plpython3u"; psql:/docker-entrypoint-initdb.d/create_db.sql:7: ERROR: could not open extension control file "/usr/share/postgresql/9.5/extension/plpython3u.control": No such file or directory

my directory layout:

me@yourbox:~/Projects/experimental/docker/scratchdb$ tree
.
├── Dockerfile
└── sql
    ├── create_db.sql
    └── schemas
        └── DDL
            └── db_schema_foo.sql

Dockerfile

FROM library/postgres:9.6
FROM zitsen/postgres-pgxn

RUN apt-get update \
 && apt-get install -y --no-install-recommends \
    python3 postgresql-plpython3-9.6

RUN pgxn install quantile

COPY sql /docker-entrypoint-initdb.d/ 

# Add VOLUMEs to allow backup of config, logs and databases
VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]

# Set the default command to run when starting the container
# CMD ["/usr/lib/postgresql/9.6/bin/postgres", "-D", "/var/lib/postgresql/9.6/main", "-c", "config_file=/etc/postgresql/9.6/main/postgresql.conf"]

create_db.sql

# Uncomment line below for debugging purposes
set client_min_messages TO debug1;

CREATE EXTENSION "quantile"
CREATE EXTENSION "plpython3u";

-- Create myappuser
CREATE ROLE myappuser LOGIN ENCRYPTED PASSWORD 'passw0rd123' NOINHERIT;
CREATE DATABASE only_foo_and_horses WITH ENCODING 'UTF8' TEMPLATE template1;
-- \l+
GRANT ALL PRIVILEGES ON DATABASE only_foo_and_horses TO myappuser;

-- Import only_foo_and_horses DDL and initialise database data
\c only_foo_and_horses;
\i /docker-entrypoint-initdb.d/schemas/DDL/db_schema_foo.sql;



-- # enable python in database

[[Edit]]

These are the commands I use to build and run the container:

docker build -t scratch:pg .
docker run -it -rm scratch:pg

How do I install plpython in a dockerised PostgreSQL service?

Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341
  • At first glance, it looks like a version mix-up: `FROM library/postgres:9.6` vs `/usr/share/postgresql/9.5/` (note the 9.5 <-> 9.6) – Frank Schmitt Sep 30 '17 at 15:28
  • @FrankSchmitt Yes I noticed that too. But modifiying the paths doesn't seem to do the trick, and at one point I used the PostgreSQL 9.5 image in my Dockerfile - error persisted through all of those changes - so I'm guessing it must be something different. – Homunculus Reticulli Sep 30 '17 at 16:28
  • @HomunculusReticulli did you ever find a solution to this? – Tom Halson Jan 14 '19 at 14:29

2 Answers2

3

I think your error was because of the initial erroneous CMD which pointed to the wrong location of PostgreSQL for this image (9.5 vs 9.6).

However, I think I've spotted the mistake for why the SQL isn't being imported.

The default ENTRYPOINT for this image (at https://github.com/docker-library/postgres/blob/bef8f02d1fe2bb4547280ba609f19abd20230180/9.6/docker-entrypoint.sh) is responsible for importing from /docker-entrypoint-initdb.d/. Since you are overwriting CMD and it is not equal to just postgresql, it is skipping this part.

The default ENTRYPOINT should do what you want. Try removing your CMD.

Andy Shinn
  • 26,561
  • 8
  • 75
  • 93
  • I think you may be mis-understanding the problem. The problem isn't that the SQL script isn't being imported (it is). It is that it fails when creating/installing the plpython language via the `CREATE EXTENSION` statement (which is in the imported SQL file). – Homunculus Reticulli Sep 30 '17 at 16:58
  • Yes, i see. But can you update your error message? As it currently stands, it looks like the error is the wrong version folder destination. – Andy Shinn Sep 30 '17 at 17:00
  • Actually, I'm going to stick with my original answer. I've tested this myself and it does work. So, my guess is your `docker run` command is overwriting something (like the user) and causing an error. Can you please update your original question with the `docker run` command you are using? – Andy Shinn Sep 30 '17 at 17:15
  • it seems that in my attempt to preesent a simple replicable case, I had left out some relevant information in my `Dockerfile` and `create_database.sql` script. The issue is that attempting to install `plpython` fails when the `quantile` extension is also being installed. I have added the relevant details to my question. – Homunculus Reticulli Oct 10 '17 at 12:31
0

I have just run this all from scratch and seems extension have been successfully created. Is there still an issue ?

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create_db.sql
SET
CREATE EXTENSION
CREATE ROLE
CREATE DATABASE
GRANT


LOG:  received fast shutdown request
waiting for server to shut down...LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
.LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.
andjelx
  • 199
  • 1
  • 13
  • it seems that in my attempt to preesent a simple replicable case, I had left out some relevant information in my `Dockerfile` and `create_database.sql` script. The issue is that attempting to install `plpython` fails when the `quantile` extension is also being installed. I have clarified my question. – Homunculus Reticulli Oct 10 '17 at 12:32