Subject: pgsql-9.6/lib/postgis-2.1.so: undefined symbol: pj_get_spheroid_defn

From: Justin Pryzby <[email-masked]>
Date: Fri, 2 Feb 2018 19:56:26 -0600

I'm sorry but I'm still have issues with the latest packages for centos6.

I'm transitioning a customer off PG9.5+centos5, and onto PG96+centos6, by
executing a db dump and restore.  I'm transitioning to PG96, and not PG10,
since there are packages for PG96+postgis2.2, which is what's on the PG95
instance (and its DB dump) - I plan to execute an pg_upgrade to pg10 shortly
after the OS transition.

But during the restore onto the centos6 machine:

pg_restore: [archiver (db)] Error from TOC entry 2732; 1255 16405 FUNCTION box2d_in(cstring) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  could not load library "/usr/pgsql-9.6/lib/postgis-2.1.so": /usr/pgsql-9.6/lib/postgis-2.1.so: undefined symbol: pj_get_spheroid_defn
    Command was: CREATE FUNCTION box2d_in(cstring) RETURNS box2d
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/postgis-2.1', 'BOX2D_in';

I have these packages installed:

[pryzbyj@telsasoft ~]$ rpm -qa |grep -Ei 'pgdg|proj|gis' #|grep -vE 'postgresql10|_10'
postgresql96-contrib-9.6.6-5PGDG.rhel6.x86_64
postgis24_10-2.4.3-1.rhel6.x86_64
postgis24_10-docs-2.4.3-1.rhel6.x86_64
postgresql10-devel-10.1-5PGDG.rhel6.x86_64
postgresql10-10.1-5PGDG.rhel6.x86_64
postgis22_96-client-2.2.6-1.rhel6.x86_64
postgresql96-libs-9.6.6-5PGDG.rhel6.x86_64
postgresql96-debuginfo-9.6.6-5PGDG.rhel6.x86_64
postgis24_10-utils-2.4.3-1.rhel6.x86_64
pgdg-centos10-10-2.noarch
postgresql10-libs-10.1-5PGDG.rhel6.x86_64
proj49-4.9.3-3.rhel6.x86_64
postgis24_10-client-2.4.3-1.rhel6.x86_64
postgresql10-docs-10.1-5PGDG.rhel6.x86_64
postgresql96-9.6.6-5PGDG.rhel6.x86_64
postgresql96-server-9.6.6-5PGDG.rhel6.x86_64
pgdg-centos96-9.6-3.noarch
postgis22_96-2.2.6-1.rhel6.x86_64
postgresql10-server-10.1-5PGDG.rhel6.x86_64
postgresql10-contrib-10.1-5PGDG.rhel6.x86_64
proj-nad-4.7.0-1.el6.x86_64
proj-4.7.0-1.el6.x86_64

[pryzbyj@telsasoft ~]$ yum list installed |grep -i pgdg
check_postgres.noarch               2.23.0-1.rhel6                    @pgdg10   
gdal-libs.x86_64                    1.9.2-8.rhel6                     @pgdg10   
geos.x86_64                         3.5.0-1.rhel6                     @pgdg96   
geos36.x86_64                       3.6.2-3.1.rhel6                   @pgdg10   
libgeotiff.x86_64                   1.4.0-1.rhel6                     @pgdg10   
pg_repack10.x86_64                  1.4.2-1.rhel6                     @pgdg10   
pgdg-centos10.noarch                10-2                              @/pgdg-centos10-10-2.noarch
pgdg-centos96.noarch                9.6-3                             @/pgdg-centos96-9.6-3.noarch
pgfincore10.x86_64                  1.1.2-2.rhel6                     @pgdg10   
postgis22_96.x86_64                 2.2.6-1.rhel6                     @pgdg96   
postgis22_96-client.x86_64          2.2.6-1.rhel6                     @pgdg96   
postgresql96.x86_64                 9.6.6-5PGDG.rhel6                 @pgdg96   
postgresql96-contrib.x86_64         9.6.6-5PGDG.rhel6                 @pgdg96   
postgresql96-debuginfo.x86_64       9.6.6-5PGDG.rhel6                 @pgdg96   
postgresql96-libs.x86_64            9.6.6-5PGDG.rhel6                 @pgdg96   
postgresql96-server.x86_64          9.6.6-5PGDG.rhel6                 @pgdg96   
proj49.x86_64                       4.9.3-3.rhel6                     @pgdg10   
python-psycopg2.x86_64              2.7.3.2-1.rhel6                   @pgdg10   

I gather that symbol is added in proj49:
[pryzbyj@telsasoft ~]$ rpm -ql proj |xargs grep pj_get_spheroid_defn
grep: /usr/share/doc/proj-4.7.0: Is a directory
[pryzbyj@telsasoft ~]$ rpm -ql proj49|xargs grep pj_get_spheroid_defn
Binary file /usr/proj49/lib/libproj.so.12 matches
Binary file /usr/proj49/lib/libproj.so.12.0.0 matches
/usr/proj49/share/doc/ChangeLog:        * src/proj.def: add pj_get_spheroid_defn to proj.def. (#214)
/usr/proj49/share/doc/ChangeLog:        * src/pj_utils.c: Add pj_get_spheroid_defn() (#142)
/usr/proj49/share/doc/NEWS: o Add pj_get_spheroid_defn() accessor.

I thought the issue was here:
[pryzbyj@telsasoft ~]$ ldd /usr/lib64/libgdal.so.1 |grep proj
        libproj.so.12 => /usr/proj49/lib/libproj.so.12 (0x00007f7d6127d000)
        libproj.so.0 => /usr/lib64/libproj.so.0 (0x00007f7d60431000)

[pryzbyj@telsasoft ~]$ ldd /usr/pgsql-9.6/lib/postgis-2.1.so 
        linux-vdso.so.1 =>  (0x00007fffcf5c7000)
        libgeos_c.so.1 => /usr/lib64/libgeos_c.so.1 (0x00007f2b4663c000)
        libproj.so.0 => /usr/lib64/libproj.so.0 (0x00007f2b463f8000)

I tried to fix with:
[pryzbyj@telsasoft ~]$ sudo yum --disablerepo=pgdg10 --enablerepo=pgdg96 reinstall libgeotiff gdal-libs
[pryzbyj@telsasoft ~]$ sudo rpm -e proj49

Now:
[pryzbyj@telsasoft ~]$ ldd /usr/lib64/libgdal.so.1 |grep proj
        libproj.so.0 => /usr/lib64/libproj.so.0 (0x00007f3c6fe95000)
[pryzbyj@telsasoft ~]$ 

..But I'm still getting the same error restore the DB, so seems to be due to
compile-time linking against proj49 (without a versioned dependency?).

Let me know if I can send any other info.

Justin

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 21 Feb 2018 17:47:44 +0000

Hi Justin,

> I'm sorry but I'm still have issues with the latest packages for centos6.

<snip>

Should be fixed, can you please confirm?

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR