this is my first question on StackOverflow, hope I found some answers :D
I'm currently working on a database that I got to restore using Postgis. I installed that extension, but Postgres is unable to find it.
I'm working on a VM based on CentOS 7 that my boss gave me to troubleshoot this issue. He told me the VM, for prod purposes, had to use Postgre 9.2, that I installed using the following command :
yum install https://cbs.centos.org/kojifiles/packages/postgresql92-postgresql/9.2.18/1.el7/x86_64/postgresql92-postgresql-9.2.18-1.el7.x86_64.rpm
https://cbs.centos.org/kojifiles/packages/postgresql92-postgresql/9.2.18/1.el7/x86_64/postgresql92-postgresql-contrib-9.2.18-1.el7.x86_64.rpm
https://cbs.centos.org/kojifiles/packages/postgresql92-postgresql/9.2.18/1.el7/x86_64/postgresql92-postgresql-libs-9.2.18-1.el7.x86_64.rpm
https://cbs.centos.org/kojifiles/packages/postgresql92-postgresql/9.2.18/1.el7/x86_64/postgresql92-postgresql-server-9.2.18-1.el7.x86_64.rpm
https://cbs.centos.org/kojifiles/packages/postgresql92/1.1/20.el7/x86_64/postgresql92-runtime-1.1-20.el7.x86_64.rpm
Next I created a symlink for libpq :
ln -s /opt/rh/postgresql92/root/usr/lib64/libpq.so.postgresql92-5 /lib64/libpq.so.postgresql92-5
And added the PATH in the /etc/profile :
# Rajout du PATH pour postgres
PATH=$PATH:/opt/rh/postgresql92/root/usr/bin
Next I created folder for the datas to be saved here :
[root@bdx-re-postgres02 lib64]# ll /app/data/
total 0
drwxr-xr-x. 4 postgres postgres 29 31 mai 15:40 postgres
[root@bdx-re-postgres02 lib64]# ll /app/data/postgres/
total 4
drwx------. 15 postgres postgres 4096 1 juin 11:01 data
drwxr-xr-x. 3 postgres postgres 18 31 mai 16:31 tbs
[root@bdx-re-postgres02 lib64]# ll /app/data/postgres/data/
total 48
drwx------. 5 postgres postgres 41 31 mai 16:42 base
drwx------. 2 postgres postgres 4096 1 juin 11:01 global
drwx------. 2 postgres postgres 18 31 mai 16:42 pg_clog
-rw-------. 1 postgres postgres 4232 31 mai 16:42 pg_hba.conf
-rw-------. 1 postgres postgres 1636 31 mai 16:42 pg_ident.conf
drwx------. 2 postgres postgres 58 1 juin 00:00 pg_log
drwx------. 4 postgres postgres 36 31 mai 16:42 pg_multixact
drwx------. 2 postgres postgres 18 1 juin 11:01 pg_notify
drwx------. 2 postgres postgres 6 31 mai 16:42 pg_serial
drwx------. 2 postgres postgres 6 31 mai 16:42 pg_snapshots
drwx------. 2 postgres postgres 25 1 juin 11:52 pg_stat_tmp
drwx------. 2 postgres postgres 18 31 mai 16:42 pg_subtrans
drwx------. 2 postgres postgres 19 31 mai 16:46 pg_tblspc
drwx------. 2 postgres postgres 6 31 mai 16:42 pg_twophase
-rw-------. 1 postgres postgres 4 31 mai 16:42 PG_VERSION
drwx------. 3 postgres postgres 60 31 mai 16:42 pg_xlog
-rw-------. 1 postgres postgres 19834 31 mai 16:42 postgresql.conf
-rw-------. 1 postgres postgres 86 1 juin 11:01 postmaster.opts
-rw-------. 1 postgres postgres 95 1 juin 11:01 postmaster.pid
[root@bdx-re-postgres02 lib64]# ll /app/data/postgres/tbs/
total 0
drwx------. 3 postgres postgres 30 31 mai 16:46 DV3F
[root@bdx-re-postgres02 lib64]#
And I created the tablespaces with a symink in /app/data/psotgres with the right permissions for postgres user :
[root@bdx-re-postgres02 lib64]# ll /var/lib/pgsql/9.2/
total 0
lrwxrwxrwx. 1 postgres postgres 22 31 mai 16:04 tbs -> /app/data/postgres/tbs
[root@bdx-re-postgres02 lib64]# ll /app/data/postgres/tbs/
total 0
drwx------. 3 postgres postgres 30 31 mai 16:46 DV3F
[root@bdx-re-postgres02 lib64]#
Next I modified the /usr/lib/systemd/system/postgresql92-postgresql.service file to add the database folder for PGDATA :
# Location of database directory
#Environment=PGDATA=/opt/rh/postgresql92/root/var/lib/pgsql/data
Environment=PGDATA=/app/data/postgres/data
And I added to repo my boss told me for installing Postgis :
yum-config-manager --add-repo http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/
yum install postgis
This is where the troubles begin. When I check for postgis files, I have the following :
[root@bdx-re-postgres02 lib64]# ll /usr/share/pgsql/contrib/postgis-2.0
total 5060
-rw-r--r--. 1 root root 1239 2 avril 2016 legacy_gist.sql
-rw-r--r--. 1 root root 2508 2 avril 2016 legacy_minimal.sql
-rw-r--r--. 1 root root 54418 2 avril 2016 legacy.sql
-rw-r--r--. 1 root root 64582 2 avril 2016 postgis_comments.sql
-rwxr-xr-x. 1 root root 146957 2 avril 2016 postgis_restore.pl
-rw-r--r--. 1 root root 149552 2 avril 2016 postgis_upgrade_20_minor.sql
-rw-r--r--. 1 root root 69411 2 avril 2016 raster_comments.sql
-rw-r--r--. 1 root root 5787 2 avril 2016 rtpostgis_legacy.sql
-rw-r--r--. 1 root root 186351 2 avril 2016 rtpostgis.sql
-rw-r--r--. 1 root root 204888 2 avril 2016 rtpostgis_upgrade_20_minor.sql
-rw-r--r--. 1 root root 3593962 2 avril 2016 spatial_ref_sys.sql
-rw-r--r--. 1 root root 13756 2 avril 2016 topology_comments.sql
-rw-r--r--. 1 root root 271709 2 avril 2016 topology.sql
-rw-r--r--. 1 root root 271597 2 avril 2016 topology_upgrade_20_minor.sql
-rw-r--r--. 1 root root 14401 2 avril 2016 uninstall_legacy.sql
-rw-r--r--. 1 root root 35990 2 avril 2016 uninstall_postgis.sql
-rw-r--r--. 1 root root 47073 2 avril 2016 uninstall_rtpostgis.sql
-rw-r--r--. 1 root root 10198 2 avril 2016 uninstall_topology.sql
[root@bdx-re-postgres02 lib64]# find / -name postgis
/usr/share/postgis
[root@bdx-re-postgres02 lib64]# ll /usr/lib64/postgis-2.0/
total 5060
-rw-r--r--. 1 root root 1239 2 avril 2016 legacy_gist.sql
-rw-r--r--. 1 root root 2508 2 avril 2016 legacy_minimal.sql
-rw-r--r--. 1 root root 54418 2 avril 2016 legacy.sql
-rw-r--r--. 1 root root 64582 2 avril 2016 postgis_comments.sql
-rwxr-xr-x. 1 root root 146957 2 avril 2016 postgis_restore.pl
-rw-r--r--. 1 root root 149552 2 avril 2016 postgis_upgrade_20_minor.sql
-rw-r--r--. 1 root root 69411 2 avril 2016 raster_comments.sql
-rw-r--r--. 1 root root 5787 2 avril 2016 rtpostgis_legacy.sql
-rw-r--r--. 1 root root 186351 2 avril 2016 rtpostgis.sql
-rw-r--r--. 1 root root 204888 2 avril 2016 rtpostgis_upgrade_20_minor.sql
-rw-r--r--. 1 root root 3593962 2 avril 2016 spatial_ref_sys.sql
-rw-r--r--. 1 root root 13756 2 avril 2016 topology_comments.sql
-rw-r--r--. 1 root root 271709 2 avril 2016 topology.sql
-rw-r--r--. 1 root root 271597 2 avril 2016 topology_upgrade_20_minor.sql
-rw-r--r--. 1 root root 14401 2 avril 2016 uninstall_legacy.sql
-rw-r--r--. 1 root root 35990 2 avril 2016 uninstall_postgis.sql
-rw-r--r--. 1 root root 47073 2 avril 2016 uninstall_rtpostgis.sql
-rw-r--r--. 1 root root 10198 2 avril 2016 uninstall_topology.sql
[root@bdx-re-postgres02 lib64]# ll /lib64/pgsql/
total 1148
-rwxr-xr-x. 1 root root 659728 2 avril 2016 postgis-2.0.so
-rwxr-xr-x. 1 root root 511528 2 avril 2016 rtpostgis-2.0.so
[root@bdx-re-postgres02 lib64]#
And when I check if postgis is installed in psql, it's the case
But, when I try to activate the extension using CREATE EXTENSION postgis, i get an output like this :
bash-4.2$ psql
psql (9.2.24, serveur 9.2.18)
Saisissez « help » pour l'aide.
postgres=# CREATE EXTENSION postgis;
ERREUR: n'a pas pu accéder au fichier « $libdir/postgis-2.0 » : Aucun fichier ou dossier de ce type
postgres=#
Translated from french : ERROR: could not access file "$libdir/postgis-2.0" : No such file or directory`
I spend the past 2 days to solve this, but none of the solutions I found worked.
Maybe Postgre is unable to find the correct folder ?
If someone can help me it would be very perfect.
Edit :
I found on this link that the $libdir can be changed within the postgresql.conf file. But I don't know what to append to the "dynamic_library_path", maybe one of the output of pg_config --pkglibdir ?
