psycopg2 ImportError: undefined symbol: PQconninfo

Question

Couldn't able to import psycopg2

Output in python console:

import psycopg2

Traceback (most recent call last): File "", line 1, in File "/home/user/.py_virtualenvs/verb_py3/lib/python3.5/site-packages/psycopg2/init.py", line 50, in

from psycopg2._psycopg import ( # noqa ImportError: /home/user/.py_virtualenvs/verb_py3/lib/python3.5/site-packages/psycopg2/_psycopg.cpython-35m-x86_64-linux-gnu.so: undefined symbol: PQconninfo

Mobocracy

Anyone has a solution if you build from source? – Tiffanitiffanie

Answer 1

Installed the psycopg2-binary package solved my issue.

pip install psycopg2-binary

Mobocracy

Answer 2

This might be a defect problem in the underlying system. There might be multiple version of shared library, and at runtime you may not be able to find the proper library. It is usually painful to resolve. Since I am seeing the same problem, I will give a try at how to figure out the problem. This will be more useful than giving a fixed solution. I still have to find a solution for myself: 1. build the libpq library from a newer version. 2. find the proper libpq that was used at compile time by psycopg2.

Go to the source directory of psycopg2:

myuid@mycomputer:~/Downloads/psycopg2$ find . -name "*.c" | xargs grep PQconninfo
./psycopg/psycopgmodule.c:    PQconninfoOption *options = NULL;
./psycopg/psycopgmodule.c:    options = PQconninfoParse(Bytes_AS_STRING(dsn), &err);
./psycopg/psycopgmodule.c:            PyErr_SetString(OperationalError, "PQconninfoParse() failed");
./psycopg/psycopgmodule.c:    PQconninfoFree(options);    /* safe on null */
./psycopg/connection_int.c:    PQconninfoOption *connopts, *ptr;
./psycopg/connection_int.c:    connopts = PQconninfoParse(pgdsn, NULL);
./psycopg/connection_int.c:    PQconninfoFree(connopts);
./psycopg/connection_int.c:    PQconninfoOption *options = NULL;
./psycopg/connection_int.c:    if (!(options = PQconninfoParse(dsn, NULL))) {
./psycopg/connection_int.c:    PQconninfoFree(options);
./psycopg/conninfo_type.c:".. seealso:: libpq docs for `PQconninfo()`__ for details.\n"
./psycopg/conninfo_type.c:    PQconninfoOption *options = NULL;
./psycopg/conninfo_type.c:    if (!(options = PQconninfo(self->conn->pgconn))) {
./psycopg/conninfo_type.c:    PQconninfoFree(options);
./psycopg/conninfo_type.c:    PyErr_SetString(NotSupportedError, "PQconninfo not available in libpq < 9.3");
./psycopg/connection_type.c:    PQconninfoOption *options = NULL;
./psycopg/connection_type.c:    if (!(options = PQconninfo(self->pgconn))) {
./psycopg/connection_type.c:    PQconninfoFree(options);
./psycopg/connection_type.c:    PyErr_SetString(NotSupportedError, "PQconninfo not available in libpq < 9.3");
./psycopg/utils.c:/* Make a dict out of PQconninfoOption array */
./psycopg/utils.c:psyco_dict_from_conninfo_options(PQconninfoOption *options, int include_password)
./psycopg/utils.c:    PQconninfoOption *o;

Note: psycopg author already gave you an answer by the line:

"PQconninfo not available in libpq < 9.3"

Which means that the problem is the older libpq at RUNTIME, you probably used a newer version of libpq otherwise, psycopg2 may not compile.

By the line: PQconninfo(self->conn->pgconn) You know that PQconninfo is a C function and it should be in your libpq

Your error message usually tells you where is the problem, in my case it is:

/usr/local/lib/python3.8/site-packages/psycopg2-2.8.5.dev0-py3.8-linux-x86_64.egg/psycopg2
Full error: >>> import psycopg2
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.8/site-packages/psycopg2-2.8.5.dev0-py3.8-linux-x86_64.egg/psycopg2/__init__.py", line 50, in <module>
    from psycopg2._psycopg import (                     # noqa
ImportError: /usr/local/lib/python3.8/site-packages/psycopg2-2.8.5.dev0-py3.8-linux-x86_64.egg/psycopg2/_psycopg.cpython-38-x86_64-linux-gnu.so: undefined symbol: PQconninfo

Here you should find a shared library:

_psycopg.cpython-38-x86_64-linux-gnu.so

Take a look at the library:

ldd _psycopg.cpython-38-x86_64-linux-gnu.so
    linux-vdso.so.1 =>  (0x00007ffe985c7000)
    libpq.so.5 => /usr/lib64/libpq.so.5 (0x00007ff922a13000)
    libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007ff9227f7000)
    libc.so.6 => /usr/lib64/libc.so.6 (0x00007ff922429000)
    libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007ff9221b7000)
    libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007ff921d54000)
    libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00007ff921a6b000)
    libcom_err.so.2 => /usr/lib64/libcom_err.so.2 (0x00007ff921867000)
    libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00007ff92161a000)
    libldap_r-2.4.so.2 => /usr/lib64/libldap_r-2.4.so.2 (0x00007ff9213bb000)
    /lib64/ld-linux-x86-64.so.2 (0x00007ff922e87000)
    libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00007ff921188000)
    libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007ff920f84000)
    libz.so.1 => /usr/lib64/libz.so.1 (0x00007ff920d6e000)
    libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00007ff920b5e000)
    libkeyutils.so.1 => /usr/lib64/libkeyutils.so.1 (0x00007ff92095a000)
    libresolv.so.2 => /usr/lib64/libresolv.so.2 (0x00007ff920741000)
    liblber-2.4.so.2 => /usr/lib64/liblber-2.4.so.2 (0x00007ff920532000)
    libsasl2.so.3 => /usr/lib64/libsasl2.so.3 (0x00007ff920315000)
    libssl3.so => /usr/lib64/libssl3.so (0x00007ff9200bc000)
    libsmime3.so => /usr/lib64/libsmime3.so (0x00007ff91fe94000)
    libnss3.so => /usr/lib64/libnss3.so (0x00007ff91fb65000)
    libnssutil3.so => /usr/lib64/libnssutil3.so (0x00007ff91f935000)
    libplds4.so => /usr/lib64/libplds4.so (0x00007ff91f731000)
    libplc4.so => /usr/lib64/libplc4.so (0x00007ff91f52c000)
    libnspr4.so => /usr/lib64/libnspr4.so (0x00007ff91f2ee000)
    libselinux.so.1 => /usr/lib64/libselinux.so.1 (0x00007ff91f0c7000)
    libcrypt.so.1 => /usr/lib64/libcrypt.so.1 (0x00007ff91ee90000)
    librt.so.1 => /usr/lib64/librt.so.1 (0x00007ff91ec88000)
    libpcre.so.1 => /usr/lib64/libpcre.so.1 (0x00007ff91ea26000)
    libfreebl3.so => /usr/lib64/libfreebl3.so (0x00007ff91e823000)

Notice the line: libpq.so.5 => /usr/lib64/libpq.so.5

Now take a look at this library and only interested in the PPconninfo (it has a lots of functions and data)

nm -D /usr/lib64/libpq.so.5 | grep PQconninfo
000000000000cb90 T PQconninfoFree
000000000000df30 T PQconninfoParse

See you don't have PQconninfo function.

I believe it is more helpful by illustrating how to solve this problem; this may help you solve many similar problems.

Now I will add the solution on my system:

It turned out that there are multiple libpg on my system.

cd /usr
find . -name "libpq*"
... many others ignored
./pgsql-11/lib/libpq.so

Then update LD_LIBRARY_PATH to /usr/pgsql-11/lib:/usr/local/lib64, then the problem is gone.

python3.8
Python 3.8.1 (default, Jan  7 2020, 15:07:37) 
[GCC 7.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2

Tiffanitiffanie

Thanks Kemin! Your debug flow help me a lot. Apparently, I had two libraries postgresql-libs.x86_64 and postgresql12-libs.x86_64 . For me, solutions was to yum remove postgresql-libs.x86_64 . – Checkmate

Answer 3

Re-installing psycopg2 using conda instead of using pip resolved the issue.

Load

For me too, thanks @tomanizer. In case someone else needs it here's the link anaconda.org/anaconda/psycopg2 (remember to install it from inside the conda environment) – Caernarvonshire

Answer 4

Just like @Kemin Zhou has said in one of the answers, one of the reasons for this problem could be an older libpq.so file.

I'm currently using Cent OS 7 and the version of postgresql-lib installed was:

$ yum list installed | grep postgres
Failed to set locale, defaulting to C
postgresql.x86_64                    9.2.24-4.el7_8                   installed
postgresql-devel.x86_64              9.2.24-4.el7_8                   installed
postgresql-libs.x86_64               9.2.24-4.el7_8                   installed

And I was not even able to update it because yum was unable to find the later versions for it:

$ yum list --showduplicates postgresql
Failed to set locale, defaulting to C
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * epel: mirrors.ircam.fr
Installed Packages
postgresql.x86_64 9.2.24-4.el7_8 installed
Available Packages
postgresql.i686                    9.2.24-2.el7                    base
postgresql.x86_64                  9.2.24-2.el7                    base
postgresql.i686                    9.2.24-2.el7_7                    updates
postgresql.x86_64                  9.2.24-2.el7_7                    updates
postgresql.i686                    9.2.24-4.el7_8                    updates
postgresql.x86_64                  9.2.24-4.el7_8                    updates

This link helped me out.

Using that link I first added this repo that contained the updated postgresql10-libs:

$ yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

and then I installed the updated postgresql10-libs

$ yum install postgresql10-libs.x86_64

Alleras