installing pyscopg2 on macOS 10.14.6 results in unusable install

[cjw296]

Ah, macOS, the gift that keeps on giving.

Here's the last exciting installment: #385

This time, installation/build resulted in no errors, now using poetry (which is still basically installing into a virtualenv using pip), but when I come to use the installation, I get:

  File "...lib/python3.8/site-packages/psycopg2/__init__.py", line 51, in <module>
    from psycopg2._psycopg import (                     # noqa
ImportError: dlopen(.../lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so, 2): Library not loaded: libpq.5.dylib
  Referenced from: .../lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so
  Reason: unsafe use of relative rpath libpq.5.dylib in .../lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so with restricted binary

Now to go and try and dig around and find what I did to workaround this last time.

[cjw296]

Okay, noticed that I was on an old EnterpriseDB install of Postgres 9.4.
As a first step, removing that and trying again with the EnterpriseDB install of Postgres 12.2.

(this made no difference)

[cjw296]

Okay, so this still works:

export PGVERSION=12
export PSYCOPG2=/Users/chris/virtualenvs/psycopg2_test/lib/python3.8/site-packages/psycopg2/

install_name_tool -change libpq.5.dylib /Library/PostgreSQL/$PGVERSION/lib/libpq.5.dylib $PSYCOPG2/_psycopg*.so
install_name_tool -change libssl.1.1.dylib /Library/PostgreSQL/$PGVERSION/lib/libssl.1.1.dylib $PSYCOPG2/_psycopg*.so
install_name_tool -change libcrypto.1.1.dylib /Library/PostgreSQL/$PGVERSION/lib/libcrypto.1.1.dylib $PSYCOPG2/_psycopg*.so

I find it surprising that there appear to be the so few people hitting this. I suspect the intersection of python developers using psycopg2 on macOS is not a small one, which suggests I must be doing something wrong.

[dvarrazzo]

I gave a 3 minutes googling for that error and it seems related to some system policies: maybe they are stricter than out-of-the-box on your machine?

I'll leave this open just to allow someone to chip in, but I'm not sure there's anything to do for us. If any, it seems more in the distutils playfield.

[cjw296]

3 minute googling is something I'd already done before logging this issue, each and every time it comes up ;-)

My machine is a stock macOS install. So have been the other 2 or 3 I've had over the last 6 years this issue has kept cropping up.

I'm trying to get wider reach here, feel free to retweet...

[dvarrazzo]

Retweeteth.

[cjw296]

Right, so, more experimenting later...

Turns out if I use the https://postgresapp.com/ provided Postgres on macOS instead of the https://www.enterprisedb.com/ provided one, the problem goes away.

I suspect this is probably an issue relating to how EnterpriseDB are doing their packaging, and thus what pg_config is returning. Will try and raise it with them, but I guess a tweet is the easiest way for me to try that...

[sandeep-edb]

Hi,

I created a virtualenv using python 3.7 and then used pg_config from 3 different Postgres installations (postgresql@11 installation from brew; postgresql installation from www.enterprisedb.com ; postgresql installation from postgresapp.com) on my macOS Catalina and results are always the same.

$ otool -L _psycopg.cpython-37m-darwin.so
_psycopg.cpython-37m-darwin.so:
libpq.5.dylib (compatibility version 5.0.0, current version 5.11.0)
libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)

So this (not setting the install paths of the dependencies like libpq from pg_config output) seems the behaviour from psycopg2.

[dvarrazzo]

@sandeep-edb psycopg only uses setuptools to compile and link the package. If you think that the package is not created the right way we should escalate this issue to the Python Package Authority. Would you like to open a bug on setuptools and link it to this conversation?

[sandeep-edb]

@dvarrazzo No, I'm not saying it's a bug. It may be a valid behaviour. But my response was for @cjw296 to let him know my findings that there is no difference in the behaviours whether you use pg_config from EnterpriseDB installer or any other installation methods.

Also, the virtualenv bundled in the EnterpriseDB installer has the paths fixed.

$ otool -L /Library/PostgreSQL/11/pgAdmin\ 4.app/Contents/Resources/venv/lib/python/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so
/Library/PostgreSQL/11/pgAdmin 4.app/Contents/Resources/venv/lib/python/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so:
@loader_path/../../../../../../Frameworks/libpq.5.dylib (compatibility version 5.0.0, current version 5.11.0)
@loader_path/../../../../../../../Contents/Frameworks/libssl.1.1.dylib (compatibility version 0.9.8, current version 0.9.8)
@loader_path/../../../../../../../Contents/Frameworks/libcrypto.1.1.dylib (compatibility version 0.9.8, current version 0.9.8)

[cjw296]

@sandeep-edb - how can I help find out why EDB installations across multiple macOS machines over 5+ years have all had this problem, but as soon as I switch to Postgres.app, the problem goes away?

What would probably be useful is if you can reproduce the problem I had. That said, my problem is also "solved" now, given that Postgres.app is working fine for me, so if this is too tricky to tackle for the gain solving it will realise, don't worry too much :-)

[sandeep-edb]

@cjw296 I would love to fix it but like I mentioned above I was able to reproduce the behaviour with all the 3 Postgres installations. Here is what I did on my macOS Catalina:
$ virtualenv --always-copy -p /usr/bin/python3 venv-py3
$ source venv-py3/bin/activate
$ export PATH=/Applications/Postgres.app/Contents/Versions/12/bin:$PATH
$ pip3 install psycopg2
$ otool -L venv-py3/lib/python3.7/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so
venv-py3/lib/python3.7/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so:
libpq.5.dylib (compatibility version 5.0.0, current version 5.11.0)
libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)

$ pip3 uninstall psycopg2
$ export PATH=/Library/PostgreSQL/11/bin:$PATH
$ pip3 install psycopg2
$ otool -L venv-py3/lib/python3.7/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so
venv-py3/lib/python3.7/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so:
libpq.5.dylib (compatibility version 5.0.0, current version 5.11.0)
libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)

$ pip3 uninstall psycopg2
$ export PATH=/usr/local/Cellar/postgresql@11/11.7_1/bin:$PATH
$ pip3 install psycopg2
$ otool -L venv-py3/lib/python3.7/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so
venv-py3/lib/python3.7/site-packages/psycopg2/_psycopg.cpython-37m-darwin.so:
libpq.5.dylib (compatibility version 5.0.0, current version 5.11.0)
libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)

[cjw296]

Are you sure you're clearing out your env properly? What does which pg_config give you for the Postgres.app version?

Here's my output:

$ which pg_config
/Applications/Postgres.app/Contents/Versions/latest/bin/pg_config
$ python
Python 3.8.2 (v3.8.2:7b3ab5921f, Feb 24 2020, 17:52:18) 
[Clang 6.0 (clang-600.0.57)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2._psycopg
>>> psycopg2._psycopg.__file__
'/Users/chris/Library/Caches/pypoetry/virtualenvs/diary-2r3Uc3gD-py3.8/lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so'
$ otool -L /Users/chris/Library/Caches/pypoetry/virtualenvs/diary-2r3Uc3gD-py3.8/lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so
/Users/chris/Library/Caches/pypoetry/virtualenvs/diary-2r3Uc3gD-py3.8/lib/python3.8/site-packages/psycopg2/_psycopg.cpython-38-darwin.so:
	/Applications/Postgres.app/Contents/Versions/12/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.12.0)
	/Applications/Postgres.app/Contents/Versions/12/lib/libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
	/Applications/Postgres.app/Contents/Versions/12/lib/libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1252.250.1)

Also beware of pip's wheel cache, which might mean you're using a version of psycopg2 compiled previously (pip3 --no-cache-dir will hopefully remove that possibility).

[cjw296]

@sandeep-edb - no follow up?

[sandeep-edb]

@cjw296 Sorry to respond late to this.

Yes, I'm able to reproduce the issue when I execute pip with --no-cache-dir. It's because we set the identification name of the shared libraries to the name of those libraries and not to their installation paths as the installers are relocatable and the installation location can vary. We also can't change the names on the fly as it could break the signature..

So, unless the installation location of the installer is fixed, i'm afraid this probably can't be done.

[cjw296]

Okay, please can you prominently advertise that the EnterpriseDB installation on postgres on mac is not compatible with using Python? (Googling suggests this doesn't only affect python, so maybe widen it to "anything that compiles against postgres libraries on mac").

[cjw296]

@sandeep-edb - any news on this?

[sandeep-edb]

Hi,

One of the options we are considering is to fix this for the default installation paths and letting the user know about this issue in case he decides to choose a different installation path.

[cjw296]

Default installation paths for EnteriseDB postgres or psycopg2?

[sandeep-edb]

We would set the identification name of the libpq dependent shared libraries to the default installation path of the EDB Postgres installer so this issue is not seen. When user chooses installs the EDB installer in a non-default path then the installer would display/log the message to make the user aware of this.

[sandeep-edb]

@cjw296 you may want to talk a look at the PostgreSQL 13 RC1 installer that will be out in sometime. We have included the fix in there. Just wanted to update you. If you still have any issues with regards to installation, please send an email to pgsql-bugs mailing list

[terrynguyen255]

In case someone need this. This is how I solve this on my machine

It try to load libpq.5.dylib from the symlink /opt/homebrew/opt/postgresql/lib/libpq.5.dylib but found not file, so I updated it:

# TODO: get this from the error, after "Library not loaded:"
SYMLINK_PATH="/opt/homebrew/opt/postgresql/lib/libpq.5.dylib"

# TODO: find this in your machine. The version maybe different than mine
DESTINATION_PATH="/opt/homebrew/opt/postgresql/lib/postgresql@14/libpq.5.dylib"

sudo mv $SYMLINK_PATH $SYMLINK_PATH.old
sudo ln -s $DESTINATION_PATH $SYMLINK_PATH