Cannot CREATE EXTENSION postgis, $libdir/postgis-2.1 not found

Question

I'm on Debian Wheezy and I have PostgreSQL 9.3 installed. Recently I've installed Postgis 2.1.7, with the scripts package as well (63 .sql files totaling 38,7 MB)

apt-get install postgresql-9.3-postgis-2.1

According to aptitude, everything is installed just fine. However, it doesn't seem to work. After installation, this was the error I was getting:

CREATE EXTENSION postgis;
ERROR:  could not open extension control file "/usr/local/pgsql-9.3.2/share/extension/postgis.control": File or directory not found

Using find, I discover it's in another folder:

root@server:/# find / -name postgis.control
/usr/share/postgresql/9.3/extension/postgis.control

I copied the file over to /usr/local/pgsql-9.3.2/share/extension. Then the error changed to:

ERROR: could not access file "$libdir/postgis-2.1": No such file or directory

And this is where I can't seem to do anything to make it work. What I've tried so far, with no success:

1- Editing postgis.control and changing the following line. CREATE EXTENSION kept giving the same error about $libdir

# module_pathname = '$libdir/postgis-2.1'
module_pathname = '/usr/local/pgsql-9.3.2/share/extension/'

2- Fully uninstalling Postgis and reinstalling it. Nothing changed at all.

3- Creating a symbolic link that pointed to /usr/lib/postgresql/9.3/lib/postgis-2.1.so. Same error

4- Creating an environment variable named libdir pointing to the following paths:

/usr/local/pgsql-9.3.2/share/extension/
/usr/share/postgresql/9.3/extension/
/usr/lib/postgresql/9.3/
/usr/lib/postgresql/9.3/lib

None of these worked, the error message kept being "could not access $libdir/postgis". I want to note that on that last one, I also tried creating a new folder named postgis-2.1 and copying postgis-2.1.so to it, to no avail.

What options I have left to make postgis work? What did I overlook?

EDIT1: This is the result of a dpkg -S psql

postgresql-client-9.3: /usr/share/locale/zh_TW/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/es/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/de/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/zh_CN/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/ru/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/lib/postgresql/9.3/bin/psql
postgresql-client-9.3: /usr/share/locale/pt_BR/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/postgresql/9.3/man/man1/psql.1.gz
postgresql-client-9.3: /usr/share/locale/pl/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/ja/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/fr/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/it/LC_MESSAGES/psql-9.3.mo
postgresql-client-9.3: /usr/share/locale/cs/LC_MESSAGES/psql-9.3.mo
bash-completion: /usr/share/bash-completion/completions/psql
postgresql-client-9.3: /usr/share/postgresql/9.3/psqlrc.sample
odbcinst1debian2:amd64: /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so

EDIT2: For some reason, this line is in the /home/postgres/.bashrc file. The person that set up the database left the job at least 2 years ago, so I can't even ask "why?":

export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

Moving postgis-2.1.so there made it "work", but now I'm facing yet another problem.

create extension postgis;
ERROR:  could not load library "/usr/local/pgsql-9.3.2/lib/postgis-2.1.so": /usr/local/pgsql-9.3.2/lib/postgis-2.1.so: undefined symbol: HeapTupleHeaderGetDatum

I'm looking at similar problems, most seem related to postgresql being 9.3.3 or lower with a version of postgis designed for 9.3.4 or higher. Being built from source/script makes me think that upgrading my current Postgres (9.3.2) isn't exactly a simple apt-get upgrade.

I'll keep looking for solutions, but if anyone can think of something and would like to point me, I'd be grateful.

A postgresql-9.3 packaged/configured for debian would not search an extension inside /usr/local/pgsql-9.3.2 Did you self-compile postgres? — Daniel Vérité
Where was PostgreSQL installed from? That's a very odd configuration. What is the output of dpkg -S /usr/local/pgsql-9.3.2/bin/psql ? You can't generally mix packaged extensions (like PostGIS) with a self-compiled PostgreSQL. — Craig Ringer
Asking around with my colleagues, I've discovered that postgres was installed with a script. Some of the lines before the make install read like this: # mkdir /usr/local/pgsql-9.3.2 (versão) ; # chown postgres. /usr/local/pgsql-9.3.2/ ; $ ./configure --prefix=/usr/local/pgsql-9.3.2/ — Arthur

Arthur Arthur · Accepted Answer · 2015-06-19T13:29:30

Since the version of PostgreSQL I was using had some custom lines when installing from source, it pretty much became immune to minor release updates and extension installations.

I had to stop the service, copy the configurations (postgresql.config and pg_hba.conf), uninstall it and reinstall via apt-get.

To uninstall

root@host:/home# wget http://ftp.postgresql.org/pub/source/v9.3.2/postgresql-9.3.2.tar.bz
root@host:/home# tar xfvz postgresql-9.3.2
root@host:/home# cd postgresql-9.3.2
root@host:/home/postgresql-9.3.2# ./configure -prefix=/usr/local/pgsql-9.3.2
root@host:/home/postgresql-9.3.2# make uninstall
root@host:/home/postgresql-9.3.2# apt-get install postgresql-9.3 postgresql-server-dev-9.3 postgresql-doc-9.3 postgresql-contrib-9.3 postgresql-client-9.3 postgresql-client-common postgresql-9.3-postgis-2.1

After this, everything is running smoothly, the new version immediately recognized the old databases and users/roles. I've also successfully executed CREATE EXTENSION postgis without having to do anything else.

Cannot CREATE EXTENSION postgis, $libdir/postgis-2.1 not found

2 Answers