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.