Upgrading PostgreSQL and PostGIS from 9.1 to 12.20 with Legacy Extension Support
Recently we did an upgrade of the legacy PostgreSQL 9.1 database of 1 TB size with PostGIS 1.5 version to PostgreSQL 12 version with PostGIS version 3.4.2.
Upgrading a PostgreSQL database along with its PostGIS extensions can be a daunting task, especially when dealing with legacy versions. In this guide, I will walk you through the process I used to upgrade the PostgreSQL 9.1 database with PostGIS 1.5 to PostgreSQL 12.20, while also upgrading the PostGIS extension to version 3.4.2. This process includes steps to safely transition through intermediate versions of both PostgreSQL and PostGIS to ensure compatibility and maintain the integrity of your spatial data.
Here are the high level steps to perform the upgrade. To simulate the existing environment, install and configure the PostgreSQL 9.1 with PostGIS 1.5
Install PostgreSQL 9.1
| 1 2 3 4 5 6 | sudo apt install curl ca-certificates sudo install -d /usr/share/postgresql-common/pgdg sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' sudo apt update sudo apt-get install postgresql-server-dev-9.1 postgresql-9.1 |
Install PostGIS 1.5
Next, I installed PostGIS 1.5 by compiling it from source:
| 1 2 3 4 5 6 7 | sudo apt-get install gcc build-essential libproj-dev libjson-c-dev libxml2-dev libxml2-utils xsltproc libgeos* wget http://download.osgeo.org/postgis/source/postgis-1.5.8.tar.gz tar xfvz postgis-1.5.8.tar.gz cd postgis-1.5.8 ./configure CFLAGS="-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I/usr/include/postgresql/9.1/server -I/root/postgis-1.5.8/liblwgeom" --prefix=/usr/share/postgresql/9.1/contrib/postgis-1.5.8/ make make install |
Create PostGIS extension
| 1 2 3 4 5 6 7 8 9 10 11 12 | cd /usr/share/postgresql/9.1/contrib/postgis-1.5 mars=# i postgis.sql SET postgres@ip-xxx-xx-x-x:/usr/share/postgresql/9.1/contrib/postgis-1.5$ psql -d mars psql (9.1.24) Type "help" for help. mars=# SELECT PostGIS_Version(); postgis_version --------------------------------------- 1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (1 row) mars=# q |
Installation is completed, now we will move to the next steps of actual upgrading.
Install and Upgrade PostGIS Extension from 1.5 to 2.2
Upgrading the PostgreSQL engine to a new version doesn’t automatically upgrade the PostGIS extension. To ensure compatibility, there will be a series of steps which upgrade PostGIS and PostgreSQL to intermediate versions. Here are the steps we followed to perform the upgrade.
Install and upgrade to PostGIS 2.2
Upgrade the PostGIS extension to the latest version supported by PostgreSQL 9.1 as outlined in https://trac.osgeo.org/postgis/wiki/PostGISObsoleteVersionsMatrix.
The latest available PostGIS package is version 2.2 which supports PostgreSQL 9.1.
| 1 2 3 4 5 | export LD_LIBRARY_PATH=/usr/lib/postgresql/9.1/lib/:$LD_LIBRARY_PATH export PATH=/usr/lib/postgresql/9.1/bin/:$PATH ./configure CFLAGS="-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I/usr/include/gdal -I/usr/include/postgresql/9.1/server -I/root/postgis-2.2.0/liblwgeom -I/root/postgis-2.2.0/raster/rt_core/" --prefix=/usr/share/postgresql/9.1/contrib/postgis-2.2/ make CFLAGS="-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I/usr/include/gdal -I/usr/include/postgresql/9.1/server -I/root/postgis-2.2.0/liblwgeom -I/root/postgis-2.2.0/raster/rt_core/" make install |
Upgrade PostGIS from 1.5 to 2.2
Given the PostGIS version change is a major version change from PostGIS 1.5 to 2.2, a full dump and restore of the database were required to perform the extension upgrade.
| 1 2 | pg_dump -Fc -b -v -f "/tmp/mars.backup" mars perl postgis_restore.pl "/tmp/mars.backup" | psql mars 2> /tmp/errors.txt |
Upgrade PostgreSQL to 9.5
PostgreSQL 9.5 is the intermediate version of PostgreSQL which supports PostgreGIS 2.2. Install PostgreSQL 9.5 with PostGIS 2.2.
| 1 | sudo apt-get install postgresql-server-dev-9.5 postgresql-9.5 |
Install PostGIS 2.2 in PostgreSQL 9.5 extensions
| 1 2 3 4 5 | export LD_LIBRARY_PATH=/usr/lib/postgresql/9.5/lib/:$LD_LIBRARY_PATH export PATH=/usr/lib/postgresql/9.5/bin/:$PATH ./configure CFLAGS="-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I/usr/include/gdal -I/usr/include/postgresql/9.5/server -I/root/postgis-2.2.0/liblwgeom -I/root/postgis-2.2.0/raster/rt_core/" --prefix=/usr/share/postgresql/9.5/contrib/postgis-2.2/ make make install |
Upgrade PostgreSQL 9.1 to 9.5
| 1 2 | /usr/lib/postgresql/9.5/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.5/bin -d /var/lib/postgresql/9.1/main -D /var/lib/postgresql/9.5/main --check /usr/lib/postgresql/9.5/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.5/bin -d /var/lib/postgresql/9.1/main -D /var/lib/postgresql/9.5/main |
Upgrade PostGIS to 2.5 and PostgreSQL to 12.20
The PostGIS 2.5 version is the highest supported version by PostgreSQL 9.5 and PostGIS 2.5 is the lowest supported version by PostgreSQL 12. So the first step is to upgrade PostGIS from 2.2 to 2.5 version.
PostGIS does not support direct upgrade from 2.2 to 2.5 so need to install 2.3, 2.4 and 2.5 versions and upgrade the extension step by step. Here I am directly jumping to 2.5 installation.
| 1 | sudo apt-get install postgresql-9.5-postgis-2.5 |
Install PostgreSQL 12
| 1 2 | sudo apt-get install postgresql-12 postgresql-client-12 postgresql-contrib-12 postgresql-12-postgis-2.5 sudo apt-get install postgresql-12-postgis-3 |
Upgrade PostgreSQL 9.5 to 12
| 1 2 | /usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/9.5/bin -B /usr/lib/postgresql/12/bin -d /var/lib/postgresql/9.5/main -D /var/lib/postgresql/12/main --check /usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/9.5/bin -B /usr/lib/postgresql/12/bin -d /var/lib/postgresql/9.5/main -D /var/lib/postgresql/12/main |
Upgrade PostGIS Extensions to 3.4.2
Lastly, I upgraded the PostGIS extensions to the latest version which is compatible with PostgreSQL 12
| 1 2 | mars=# SELECT postgis_extensions_upgrade(); mars=# SELECT postgis_full_version(); |
This step completes the upgrade process, and the database is now running on PostgreSQL 12.20 with PostGIS 3.4.2.
Conclusion
Upgrading a PostgreSQL database with PostGIS extensions is a meticulous process, requiring careful planning and execution. By following the steps outlined above, you can ensure a smooth transition to newer versions while preserving the integrity of your spatial data. Always remember to backup your database before proceeding with major upgrades.
