PostgreSQL 15 came out just last week. To celebrate the arrival of PostgreSQL 15, I will revisit the number one problem people have with PostGIS, how to upgrade your PostGIS enabled cluster, without installing an old PostGIS version.
In a previous article
Using pg upgrade to upgrade PostGIS without installing older version
I demonstrated a trick for upgrading to a newer PostgreSQL instance from PostGIS 2.2 - 2.whatever without having to install the older version of PostGIS in your new PostgreSQL service.
This is a revisit of that article, but with considerations for upgrading from a 2 series to a 3 series.
Fear not. Going from PostGIS 2+ to PostGIS 3+ can still be done without installing the old PostGIS 2+ in your new cluster. Additionally once you are on PostGIS 3.1+, you should never have to do symlink or copy hacks to upgrade say PostGIS 3.1 to PostGIS 3.4. At least not for the same major version.
If per chance you are on PostGIS 1 and trying to jump all the way to PostGIS 3+, then sadly you need to do a pg_dump of your old database/cluster and pg_restore into your new cluster.
The steps are CentOS/RHEL specific, but the general principals of what you need to do are the same regardless of if you are on Windows, FreeBSD/Unix, Linux, or some other OS.
For the rest of this, I'll assume you've already got PostgreSQL 9.6 installed with PostGIS 2.4, and you want to upgrade it to PostgreSQL 11 or higher on the same server. I have a database on PostgreSQL 9.6 with a postgis 2.4 installed in it called
gisdb
.
For those with dependent apps. A lot has changed in catalogs from PostgreSQL 11 - 15. I've found that for most applications you can safely upgrade PostgreSQL 9.6 to 11. From PostgreSQL 12 on, you may need to upgrade your database drivers such as JDBC, .NET, PHP, and possibly even rewrite some code. So if you are < PostgreSQL 11, test your apps heavily with newer PostgreSQL before attempting a PostgreSQL 12 or higher upgrade. If you use pgAdmin, you'll definitely want to be above 6.10 if you plan to upgrade to PostgreSQL 15. Just install the latest version of pgAdmin which at time of this writing is 6.14.
Patch update
It's always good to be up to date on your patches before attempting a PostgreSQL major upgrade.
sudo yum update
sudo yum upgrade
Confirm your PostgreSQL and PostGIS versions
Before you begin your upgrade journey, log into your old cluster and confirm what you are running. For this example, I'm going to connect to my
gisdb
database which has PostGIS already installed. You can do this with psql or pgAdmin. I'm doing with psql right on the server
su postgres
psql -d gisdb -p 5432
In the psql console
-- repeat for each postgisy extension you have
ALTER EXTENSION postgis UPDATE;
SELECT version(), postgis_full_version();
Your output should look something like below (the fact it shows need upgrade is a bug in 2.4, you can ignore)
version | postgis_full_version
-------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.24 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
| POSTGIS="2.4.9" PGSQL="96" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.3.1" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.3, released 202
1/04/27" LIBXML="2.9.1" LIBJSON="0.11"
LIBPROTOBUF="1.0.2" (core procs from "2.4.9" need upgrade)
TOPOLOGY (topology procs from "2.4.9" need upgrade)
RASTER (raster procs from "2.4.9" need upgrade) (sfcgal procs from "2.4.9" need upgrade)
(1 row)
Remove postgis_sfcgal extension prior to upgrade
If you see SFCGAL in your output, it means you have postgis_sfcgal extension installed. Coming from PostGIS < 3.1, you need to remove it prior to upgrade and reinstall it after you upgrade. The reason for this is that postgis_sfcgal library file (the .so, .dll, .whatever) used to be part of the postgis library file. These were split in PostGIS 3.1. So the symlink trick we will show later will not work here because the new postgis library file is missing the sfcgal functions. Luckily postgis_sfcgal is rarely tied to data since it's a functions only library.
While still in psql console do the below. If for some reason you have views, indexes etc, tied to this extension, the drop command will fail. If it succeeds, you are in good shape.
DROP EXTENSION postgis_sfcgal;
Stop old cluster
If you are still in psql then exit out as follows
\quit
Exit out of postgres user account too
Then stop your 9.6 service and disable it from starting up
Next step is to install the new version of PostgreSQL. Follow these steps:
For the rest of these I'll assume you are logged in as root. If you are in as an unprivileged that has sudo rights, to get into root shell do:
sudo -i
Figure out which CentOS/Red Hat OS version you have:
cat /etc/redhat-release
Mine returns: CentOS Linux release 7.9.2009 (Core)
You'll need the Enterprise Linux Extra Packages (epel) repo since yum.postgresql.org gets dependencies from there. This you might already have installed when you installed your older PostgreSQL. If not you can do:
yum install epel-release
For older CentOS/RedHat you may need to manually add from master mirror http://dl.fedoraproject.org/pub/epel.:
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Initialize your PostgreSQL 15 data cluster. If you are using CentOS 7 or above, you will need to do it the system V systemctl way:
/usr/pgsql-15/bin/postgresql-15-setup initdb
Older systems, get the nicer approach
service postgresql-15 initdb
Let's pretend we have postgis-2.4 installed in PostgreSQL 15. Prior to PostGIS 3.0, the lib files included the minor version, thus requiring this annoying linking hack I am about to explain.
If you are on PostGIS 3+, the lib files should all end in -3 (with no minor version).
All PostgreSQL extensions are bound to the PostgreSQL version they are compiled for.
That means we can't copy postgis-2.4 from our PostgreSQL 9.6, however WE CAN copy our postgis*-3 and call it postgis*-2.4. We've dropped the functions that are not present in the postgis-3 lib so that pg_upgrade won't complain.
One other thing that changed in PostGIS 3.0, is the standardizing of the lib file to match the postgis_raster extension name, so the linking is a little bit of a suprise here that trips most people.
If you had postgis_topology or address_standardizer extensions installed in your databases, you'll need to repeat for those as well as follows:
cp /usr/pgsql-15/lib/postgis_topology-3.so /usr/pgsql-15/lib/postgis_topology-2.4.so
-- in older versions address_standardizer did not have a version as part of lib, it now does
cp /usr/pgsql-15/lib/address_standardizer-3.so /usr/pgsql-15/lib/address_standardizer.so
The postgis_tiger_geocoder extension is a pure plpgsql/no c libraries so doesn't need any special treatment.
Do the Upgrade
Now we do the upgrade. This you need to do as the postgres user. So before flipping to postgres user, I'll create a space for postgres to do the upgrade.
su postgres
cd ~/
And then upgrade, here I am using --link mode to reduce downtime. If you for whatever reason need to be able to run your old PostgreSQL service to check on things, remove the --link mode
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_clog to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/pgsql/9.6/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for hash indexes ok
Checking for extension updates notice
Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
If you see an error something like 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:
/var/lib/pgsql/15/data/pg_upgrade_output.d/20221017T053014.835/loadable_libraries.txt
Don't panic. Read the file listing the missing libraries. Common problems are e.g. you have pgrouting extension installed, and that is minor versioned. Look at the old cluster and new cluster to see what the files should be called, and do the same:
Install the extension if it is missing:
sudo yum install pgrouting_15
ls /usr/pgsql-15/lib/*pgrouting*
ls /usr/pgsql-9.6/lib/*pgrouting*
In my case I have pgrouting-3.0 in 9.6, but pgrouting-3.2 in PG 15. To fix I did:
exit #get back in as root
systemctl start postgresql-15
systemctl enable postgresql-15
su postgres
-- repeat this for each database with PostGIS installed
\connect gisdb
ALTER EXTENSION postgis UPDATE;
You'll get a notice like below, and we are going to do what it says.
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
HINT: type `SELECT postgis_extensions_upgrade();`
to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
SELECT postgis_extensions_upgrade();
-- you'll get some NOTICES about backend which you can ignore
SELECT postgis_full_version();
-- should output
At this point you can safely drop postgis_raster, note the below will fail if you have raster data, so is safe to run.
DROP EXTENSION postgis_raster;
And if you were using postgis_sfcgal, you can reinstall it as follows:
CREATE EXTENSION postgis_sfcgal;
You should run the generated by pg_upgrade. This will take care of upgrading other extensions you might have,
and also is all you really need if you are going from a PostGIS 3.* to PostGIS 3.* install
If you are confident all is good, you can safely uninstall the old PostgreSQL as follows:
yum remove postgresql96
That should cascade and remove all postgresql96 extensions, server, and contrib. You'll get a confirm message like below:
Dependencies Resolved
===================================================================================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================================================================================
Removing:
postgresql96 x86_64 9.6.24-1PGDG.rhel7 @pgdg-9.6 7.5 M
Removing for dependencies:
pgrouting_96 x86_64 3.0.4-1.rhel7 @pgdg-9.6 5.3 M
postgis24_96 x86_64 2.4.9-3.rhel7 @pgdg-9.6 118 M
postgresql96-contrib x86_64 9.6.24-1PGDG.rhel7 @pgdg-9.6 2.0 M
postgresql96-server x86_64 9.6.24-1PGDG.rhel7 @pgdg-9.6 19 M
Transaction Summary
===================================================================================================================================================================================================================================================
Remove 1 Package (+4 Dependent packages)
Installed size: 152 M
Is this ok [y/N]: y
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
