RubyPDF Blog Postgres How to enable plpython3u extension on Postgres.app

How to enable plpython3u extension on Postgres.app

Postgres.app is the easiest way to get started with PostgreSQL on the Mac and it can install minor updates automatically.
but it only includes plpython2u at the moment. 

A issue opened on Sep 5, 2014, and is still open.
When trying:

CREATE EXTENSION plpython3u;

Got

RROR: could not open extension control file “/Applications/Postgres.app/Contents/Versions/9.3/share/postgresql/extension/plpython3u.control”: No such file or directory

and today I will show you how to fix it.

Suppose you are ready the following,

find the compiled plpython3 from conda-forge
and download postgresql-plpython-11.3-py37h8e224ba_0.tar.bz2
decompress it and copy

share/extension/plpython3u--unpackaged--1.0.sql
share/extension/plpython3u.control
share/extension/hstore_plpython3u.control
share/extension/hstore_plpython3u--1.0.sql
share/extension/plpython3u--1.0.sql

to
/Applications/Postgres.app/Contents/Versions/11/share/postgresql/extension

and copy
lib/plpython3.so
lib/pgxs
lib/hstore_plpython3.so

to
/Applications/Postgres.app/Contents/Versions/11/lib

#create an environment with a specific version of Python: 3.7
conda create -n plpython3u python=3.7
#active it
conda activate plpython3u
#suppose it plpython3u path is /Users/steven/anaconda3/envs/plpython3u
#run Postgre.app in this way,
PYTHONPATH=/Users/steven/anaconda3/bin/python:$PYTHONPATH /Applications/Postgres.app/Contents/MacOS/Postgres
#then you can active your plpython3u extension,
CREATE EXTENSION plpython3u;

and the job done.
btw, the plpython2u will deactivate, if you try to use the function created for plpython2u, you will get the error,

SQL Error [XX000]: FATAL: multiple Python libraries are present in session
Detail: Only one Python major version can be used in one session.

P.S.
If you think it is too duplicated, you can try other much simple ways,
conda install -c conda-forge postgresql-plpython
or
brew install postgres --with-python

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.