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.
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.