Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Problems with PGDG Centos Upgrade Procedure when using PostGIS

Centos 7 Upgrade

PgSQL 10 / PostGIS 2.4 => PgSQL 11 / PostGIS 2.5

Setup

Starting from a bare Centos 7 box:

# root
sudo bash
yum update
yum install epel-release

Install PgSQL 10 / PostGIS 2.4

# root
rpm -ivh https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum install postgresql10-server postgis24_10 postgis24_10-client unzip
systemctl enable postgresql-10
systemctl start postgresql-10

Enable PostGIS / Create Spatial Table

# postgres
su - postgres
createdb postgis
psql -c 'create extension postgis' -d postgis
wget https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_populated_places.zip
unzip ne_10m_populated_places.zip 
shp2pgsql -s 4326 -I -D ne_10m_populated_places places | psql postgis
exit

Install PgSQL 11 / PostGIS 2.5

# root
systemctl stop postgresql-10
rpm -ivh https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
yum install postgis25_11 postgresql11-server postgis25_11-client

Attempt Upgrade

This should work...

# postgres
su - postgres
/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data/
/usr/pgsql-11/bin/pg_upgrade \
  --old-datadir=/var/lib/pgsql/10/data/ \
  --old-bindir=/usr/pgsql-10/bin \
  --new-datadir=/var/lib/pgsql/11/data/ \
  --new-bindir=/usr/pgsql-11/bin

But, alas it does not.

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Looking into the log file, we actually have two distinct problems.

could not load library "$libdir/postgis-2.4": ERROR:  could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /usr/pgsql-11/lib/postgis-2.4.so: undefined symbol: GEOSFrechetDistanceDensify
could not load library "$libdir/rtpostgis-2.4": ERROR:  could not access file "$libdir/rtpostgis-2.4": No such file or directory

Solving the second one first. While the postgis-2.5 RPM does have a symlink to provide a fake postgis-2.4.so, it does not have one to provide a fake rtpostgis-2.4.so (or a fake postgis_topology-2.4.so, which would only matter if we were using the postgis_topology` extension, which we could have been). So, we add the missing symlink.

# root
cd /usr/pgsql-11/lib/
ln -s rtpostgis-2.5.so rtpostgis-2.4.so

The second problem is a little less obvious, but it arises because we have both the geos36 and geos37 packages installed simultaneously at this point.

rpm -qa | grep geos

PostGIS 2.5 has been built with GEOS 3.7 as the dependency.

# rpm -q --requires postgis25_11 | grep geos
geos37 >= 3.7.0
libgeos_c.so.1()(64bit)

So, we have the right GEOS library installed, but on load the PostGIS module is not finding it. What's up? Let's look at the dependencies of our module.

# ldd /usr/pgsql-11/lib/postgis-2.5.so
    linux-vdso.so.1 =>  (0x00007ffe84495000)
    libgeos_c.so.1 => /usr/geos36/lib64/libgeos_c.so.1 (0x00007f60bc492000)
    libproj.so.12 => /usr/proj49/lib/libproj.so.12 (0x00007f60bc229000)
    libjson-c.so.2 => /lib64/libjson-c.so.2 (0x00007f60bc01e000)
    libxml2.so.2 => /lib64/libxml2.so.2 (0x00007f60bbcb4000)
    libm.so.6 => /lib64/libm.so.6 (0x00007f60bb9b2000)
    libSFCGAL.so.1 => /lib64/libSFCGAL.so.1 (0x00007f60baee3000)
    libc.so.6 => /lib64/libc.so.6 (0x00007f60bab16000)
    libgeos-3.6.3.so => /usr/geos36/lib64/libgeos-3.6.3.so (0x00007f60ba768000)
    libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f60ba461000)
    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f60ba24b000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f60ba02f000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007f60b9e2b000)
    libz.so.1 => /lib64/libz.so.1 (0x00007f60b9c15000)
    liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f60b99ef000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f60bc986000)
    libCGAL.so.11 => /usr/lib64/libCGAL.so.11 (0x00007f60b97c7000)
    libCGAL_Core.so.11 => /usr/lib64/libCGAL_Core.so.11 (0x00007f60b958e000)
    libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x00007f60b9333000)
    libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x00007f60b90bb000)
    libboost_date_time-mt.so.1.53.0 => /usr/lib64/libboost_date_time-mt.so.1.53.0 (0x00007f60b8eaa000)
    libboost_thread-mt.so.1.53.0 => /usr/lib64/libboost_thread-mt.so.1.53.0 (0x00007f60b8c93000)
    libboost_system-mt.so.1.53.0 => /usr/lib64/libboost_system-mt.so.1.53.0 (0x00007f60b8a8f000)
    libboost_serialization-mt.so.1.53.0 => /usr/lib64/libboost_serialization-mt.so.1.53.0 (0x00007f60b8823000)
    librt.so.1 => /usr/lib64/librt.so.1 (0x00007f60b861b000)

Uh oh, we're picking up GEOS 3.6 not 3.7. If we want to leave both 3.6 and 3.7 permanently installed, we would have to re-order the /etc/ld.so.conf.d directory to put 3.7 ahead of 3.6 in the order of libraries found by the linker. But since that would just cause all users of GEOS to be upgraded to 3.7, we might as well make the situation explicit by removing the 3.6 library and its dependencies.

# root
rpm -e geos36 postgis24_10 postgis24_10-client

Now we can run the upgrade.

# postgres
/usr/pgsql-11/bin/pg_upgrade \
    --old-datadir=/var/lib/pgsql/10/data/ \
    --old-bindir=/usr/pgsql-10/bin  \
    --new-datadir=/var/lib/pgsql/11/data/  \
    --new-bindir=/usr/pgsql-11/bin

And we can start the new database.

# root
systemctl start postgresql-11

And run the SQL level extension update.

# postgres
psql -c 'alter extension postgis update' -d postgis
psql -c 'select postgis_full_version()' -d postgis

Fixes Needed

Symlinks in the RPM

All the modules should have symlinks to their prior versions set up so that pg_upgrade can work its magic.

  • postgis-2.4.so -> postgis-2.5.so
  • rtpostgis-2.4.so -> rtpostgis-2.5.so
  • postgis_topology-2.4.so -> postgis_topology-2.5.so

Linker Magic

Getting the new module to link to the new version of GEOS involves either:

  • Patching the build to use an explicit link flag (eg -llibgeos-2.5.so) which will essentially version lock the postgis-2.5.so module to that version of GEOS. That means the "system-wide" install of GEOS looks increasingly like a PostGIS-only install, since now GEOS cannot be upgraded without also upgrading PostGIS.
  • Ensuring that when geos37 is installed it has priority with the linker. Contra the documentation, the file appearing last in the /etc/ld.so.conf.d directory does not appear first in the list of libraries to link, so the geos37 entry is actually appearing after the geos36 one. Reverse ordering those files would work.
@220kts

This comment has been minimized.

Copy link

220kts commented Dec 30, 2018

Many thanks, your explanation saved the day for us. We run yum-cron and our applications broke this morning after a yum update of the PostGIS installation. FYI for anyone else who may experience the same or similar problem:

ERROR: could not load library "/usr/pgsql-10/lib/postgis-2.4.so"

yum history info (transaction id):

Transaction performed with:
Installed rpm-4.11.3-35.el7.x86_64 @base
Installed yum-3.4.3-161.el7.centos.noarch @base
Installed yum-metadata-parser-1.1.4-10.el7.x86_64 @anaconda
Installed yum-plugin-fastestmirror-1.1.31-50.el7.noarch @base
Packages Altered:
Dep-Install geos37-3.7.0-1.rhel7.x86_64 @pgdg10
Updated postgis24_10-2.4.5-1.rhel7.x86_64 @pgdg10
Update 2.4.6-2.rhel7.x86_64 @pgdg10
Updated postgis24_10-client-2.4.5-1.rhel7.x86_64 @pgdg10
Update 2.4.6-2.rhel7.x86_64 @pgdg10
Updated postgis24_10-devel-2.4.5-1.rhel7.x86_64 @pgdg10
Update 2.4.6-2.rhel7.x86_64 @pgdg10
Updated postgis24_10-utils-2.4.5-1.rhel7.x86_64 @pgdg10
Update 2.4.6-2.rhel7.x86_64 @pgdg10

As noted in the original post here, the PostGIS installation was apparently looking for geos37 but found geos36. The fix in our case was just rpm -e geos36 and then systemctl restart postgresql-10.

@LorrnelGroup

This comment has been minimized.

Copy link

LorrnelGroup commented Jun 27, 2019

If anyone is having issues installing 24_10 you now have to ensure epel repo is OFF before the install
sudo nano /etc/yum.repos.d/epel.repo
--change enabled to 0

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.