Postgresql – CREATE EXTENSION postgis fails with /usr/lib/libgdal.so.1: undefined symbol: sqlite3_column_table

postgispostgresqlubuntu-16.04

Problem:

postgres=# CREATE EXTENSION postgis;
LOG:  statement: CREATE EXTENSION postgis;
ERROR:  could not load library "/usr/lib/postgresql/9.5/lib/rtpostgis-2.3.so": /usr/lib/libgdal.so.1: undefined symbol: sqlite3_column_table_name`

What I've done:

  • Added Postgres official PPA – http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main
  • Installed Postgis – sudo apt-get install postgresql-9.5-postgis-2.3
  • I've also uninstalled libgdal and it's dependencies (sudo apt-get remove --purge libgdal1i) and then reinstalled postgis as above. Still same error.
  • I installed clean Ubuntu in a VirtualBox and there it installed and worked ok.

Versions:

  • Ubuntu – Ubuntu 16.04.3 LTS
  • Postgres – 9.5.9-1.pgdg16.04+1
  • Postgis – 2.3.3+dfsg-1.pgdg16.04+1
  • Libgdal – 1.11.3+dfsg-3build2 (libgdal1i)

I'm all out if ideas how to debug this further.

Best Answer

OK, finally solved the problem.

Root cause

Postgres did not have access to libsqlite3.so.

Debugging

Error refers to libgdal.so.1 having problems and is linked against sqlite3, so checked the dependencies:

  ~ ldd /usr/lib/libgdal.so.1 | grep "sql"
 libmysqlclient.so.20 => /usr/lib/x86_64-linux-gnu/libmysqlclient.so.20 (0x00007fc80cd65000)
 libsqlite3.so.0 => /usr/local/lib/libsqlite3.so.0 (0x00007fc80825e000)

Interesting part is that all dependencies are from /usr/lib/..., but libsqlite3.so.0 is from /usr/local/lib. So it makes one wonder if maybe Postgres does not have access to /usr/local/lib and causing problems?

As it seems, officially libsqlite should not be installed in /usr/local/lib, but /usr/lib where it actually already existed. Removed the /usr/local/lib version and reinstalled the whole postgis package and it worked.