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

Answer 1

Installed the psycopg2-binary package solved my issue.

pip install psycopg2-binary

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

Answer 3

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