Upgrading PostGIS on Centos 7

New features and better performance get a lot of attention, but one of the relatively unsung improvements in PostGIS over the past ten years has been inclusion in standard software repositories, making installation of this fairly complex extension a "one click" affair.

Once you've got PostgreSQL/PostGIS installed though, how are upgrades handled? The key is having the right versions in place, at the right time, for the right scenario and knowing a little bit about how PostGIS works.

Install PostgreSQL 10 and PostGIS 2.4

To exercise an upgrade scenario we install slightly older versions of PostgreSQL and PostGIS.

Starting from a bare Centos 7 box, you'll want to update all your packages and add the EPEL repository.

sudo bash
# as root
# update all installed software
yum update -y
# add repository for extra packages
yum install -y epel-release

Then install PostgreSQL 10 and PostGIS 2.4 from the PostgreSQL Global Developer Group (pgdg) yum repository.

# as root
# install repository package for pgsql 10
rpm -ivh https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
# install pgsql 10
yum install -y postgresql10-server postgis24_10 postgis24_10-client unzip

Note that installing the PostGIS package brings along quite a large list of dependencies, including:

• geos37 or geos36, a computational geometry library
• proj49 or proj50, a reprojection library
• gdal11 or gdal24, a raster format and processing library
• json-c, a Javascript format parsing library

If you experience problems during an upgrade, it is often due to inconsistencies in these library versions, which is discussed in the "Problems" section below.

Enable the PostgreSQL service, initialize the data area, and start your server.

# as root
# enable service
systemctl enable postgresql-10
# initialize data area
/usr/pgsql-10/bin/postgresql-10-setup initdb
# start server
systemctl start postgresql-10

Just to prove that we're doing a safe upgrade of the spatial components, create a spatially enabled database.

# as postgres
su - postgres
createdb postgis
psql -c 'create extension postgis' -d postgis

Now add a table of spatial data from Natural Earth to your database.

# as postgres
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

So, software installed and data loaded -- we are ready to upgrade!

Upgrade Scenario #1: PostgreSQL 10 to 11, PostGIS 2.4 to 2.5

Perhaps the most common upgrade path will be a simultaneous upgrade of PostgreSQL and PostGIS to the latest released versions. In our case to PostgreSQL 11 and PostGIS 2.5.

First, we stop the server, then install the latest software.

# as root
# stop the server
systemctl stop postgresql-10
# install repository package for new version
rpm -ivh https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
# install new versions
yum install -y postgis25_11 postgresql11-server postgis25_11-client

With the server stopped and the new software in place, we are ready to run an in-place upgrade.

# as postgres
su - postgres
# initialize a new data area
/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data/
# run the upgrade
/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

With the data upgrade complete, we can remove the old software.

# check what you have installed
rpm -qa | grep postgis
rpm -qa | grep geos
rpm -qa | grep proj
# remove the old versions of postgis
rpm -e postgis24_10 postgis24_10-client
# if you have an older version of geos, remove it now
# so you only have the latest version
rpm -e geos36

Finally, start the new server.

# as root
systemctl start postgresql-11

Update PostGIS extension to new version.

# as postgres
psql -c 'alter extension postgis update' -d postgis

Test the version update.

# as postgres
psql -c 'select postgis_full_version()' -d postgis
psql -c "select st_asgeojson(geom) from places where name = 'New York'" -d postgis

Upgrade Scenario #2: PostgreSQL 10, PostGIS 2.4 to 2.5

PostGIS runs on a different upgrade cycle from PostgreSQL, and database upgrades can involve more system changes than extension upgrades, so it is not uncommon to want to upgrade PostGIS while leaving your PostgreSQL version unchanged.

This scenario runs into an interesting problem -- you cannot simultaneously have the postgis24_10 and postgis25_10packages installed.

The solution is surprisingly straightforward -- just uninstall the old version first.

# as root
# stop the server
systemctl stop postgresql-10
# remove the old versions
rpm -e postgis24_10 postgis24_10-client
# install the new versions
yum install -y postgis25_10 postgis25_10-client

If there are older versions of dependencies lying around, remove those too.

# as root
# if you have an older version of geos, remove it now
# so you only have the latest version
rpm -e geos36

Restart the server!

# as root
# restart the server
systemctl start postgresql-10

Then remember to update the extension in SQL.

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

Problems?

Depending on what package versions you are upgradingfrom, and are upgradingto, you may occasionally have some difficulties during upgrades.

Old Library Conflicts

Here's a pg_upgrade log file entry that we saw during an upgrade with an old library in place.

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

New versions of the GEOS library frequently have new functionality added, that leads to new symbols in the library. If PostGIS has been compiled against a newer version, but your system still has an older version installed, it's possible the old version will be checked for the symbol before the new version, and this kind of error will ensue.

The solution? Make sure you only have one version of the dependent library in place, the latest one.

# as root
# if you have an older version of geos, remove it now
# so you only have the latest version
rpm -e geos36

Missing Symlinks in New Package

During the upgrade to a new version of the postgis-2.5.so module, PostgreSQL temporarily requires access to both the old and new versions. Packagers dodge around this requirement by including a "symbolic link" file in their package that aliases the new module file to the old module name.

Here is a pg_upgrade error that showed up in that scenario.

could not load library "$libdir/rtpostgis-2.4":
ERROR:  could not access file "$libdir/rtpostgis-2.4":
No such file or directory

In this case the upgrade process wanted to load the rtpostgis-2.4.so module, and it wasn't available. However, the newer version was available, and for the purposes of the upgrade we can create a symbolic link to trick the upgrade process.

# as root
cd /usr/pgsql-11/lib
# point the old name to the new file
ln -s rtpostgis-2.4.so rtpostgis-2.5.so