Fear not. There is still a way to upgrade from 2.1 to 2.5 without installing an older version of PostGIS in your new PostgreSQL instance. To do so, you need to add a step and that is to remove the functions in 2.1 that are backed by this renamed lib function. In upcoming PostGIS 3.0, we've added this function back and have it throw an error so that even coming from PostGIS 2.1, you can upgrade just the same as you do from later versions.
For this exercise, I'm going to do it on a CentOS 7 Box, I created and installed PostGIS 2.1 on following instructions similar to what I detailed
in An almost idiot's guide to install PostgreSQL 9.3, PostGIS 2.1 and pgRouting with Yum
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.3 installed with PostGIS 2.1, and you want to upgrade it to PostgreSQL 11 on the same server. I have a PostgreSQL 9.3 with a postgis 2.1 installed in it called gisdb.
Drop functions that won't migrate
The first step is to log into your PostgreSQL 9.3 service and for each database that has PostGIS installed, drop the functions that won't migrate.
su postgres
psql
Repeat the below for each postgis database you have
\connect gisdb;
ALTER EXTENSION postgis DROP FUNCTION GeomFromEWKB(bytea);
DROP FUNCTION GeomFromEWKB(bytea);
ALTER EXTENSION postgis DROP FUNCTION ST_GeomFromEWKB(bytea);
DROP FUNCTION ST_GeomFromEWKB(bytea);
The ALTER EXTENSION postgis DROP FUNCTION
clauses drop the functions from the postgis
extension so postgis extension no longer depends on them.
The actual removal of the functions happens with plain-old drop DROP FUNCTION. If you get an error when doing the DROP FUNCTION.. it would be because you are using them in a view or some other function. It will tell you what objects depend on them when you try to drop. You'll need to set aside the scripts for these, drop them and restore them after you upgrade. During the postgis 2.5 upgrade process, these functions will be restored with the new backing function, so no code needs changing that uses them.
Then stop your 9.3 service
service postgresql-9.3 stop
systemctl equivalent
systemctl stop postgresql-9.3
Install PostgreSQL 11 and PostGIS 2.5
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.6.1810 (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
Go to https://yum.postgresql.org/repopackages.php and copy the link corresponding to your OS version and PostgreSQL 11
rpm -ivh https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
Now confirm there is postgis from pgdg11 and its postgis25
yum list | grep postgis
postgis2_93.x86_64 2.1.8-1.rhel7 @pgdg93
postgis.x86_64 2.0.7-1.el7 epel
postgis-docs.x86_64 2.0.7-1.el7 epel
postgis-utils.x86_64 2.0.7-1.el7 epel
postgis21_93.x86_64 2.1.8-1.rhel7 pgdg93
postgis21_93-client.x86_64 2.1.8-1.rhel7 pgdg93
postgis21_93-debuginfo.x86_64 2.1.8-1.rhel7 pgdg93
postgis21_93-devel.x86_64 2.1.8-1.rhel7 pgdg93
postgis21_93-utils.x86_64 2.1.8-1.rhel7 pgdg93
:
postgis25_11.x86_64 2.5.1-2.rhel7 pgdg11
postgis25_11-client.x86_64 2.5.1-2.rhel7 pgdg11
postgis25_11-debuginfo.x86_64 2.5.1-2.rhel7 pgdg11
postgis25_11-devel.x86_64 2.5.1-2.rhel7 pgdg11
postgis25_11-docs.x86_64 2.5.1-2.rhel7 pgdg11
postgis25_11-utils.x86_64 2.5.1-2.rhel7 pgdg11
:
Install PostgreSQL 11
yum install postgresql11 postgresql11-server postgresql11-libs postgresql11-contrib postgresql11-devel
Install PostGIS 25 for PostgreSQL 11
yum install postgis25_11
It will install a lot of stuff (48+ dependencies)
Initialize your PostgreSQL 11 data cluster. If you are using a newer CentOS like CentOS 7 or above, you will need to do it the new ugly system V systemctl way:
/usr/pgsql-11/bin/postgresql-11-setup initdb
Older systems, get the nicer approach
service postgresql-11 initdb
Let's pretend we have postgis-2.1 installed in PostgreSQL 11. All PostgreSQL extensions are bound to the version they are compiled for. That means we can't copy postgis-2.1 from our PostgreSQL 9.3, however WE CAN copy our postgis-2.5 and call it postgis-2.1. We've dropped the functions that are not present in the postgis-2.5 lib so that pg_upgrade won't complain.
cp /usr/pgsql-11/lib/postgis-2.5.so /usr/pgsql-11/lib/postgis-2.1.so
cp /usr/pgsql-11/lib/rtpostgis-2.5.so /usr/pgsql-11/lib/rtpostgis-2.1.so
If you had postgis-sfcgal, postgis_topology, or address_standardizer extensions installed in your databases, you'll need to repeat for those as well. Note the address_standardizer we took off the -2.5.so in 2.5, so it's just 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.
mkdir /home/postgres
chown postgres /home/postgres
su postgres
cd /home/postgres
And then upgrade
/usr/pgsql-11/bin/pg_upgrade \
-b /usr/pgsql-9.3/bin/ -B /usr/pgsql-11/bin/ \
-d /var/lib/pgsql/9.3/data -D /var/lib/pgsql/11/data \
--link
The output should look like this:
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
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_" ok
Checking for incompatible "line" data type ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions 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 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
ok
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.3/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
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Checking for hash indexes ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
If any of the above fails and you get to the part where it says
you must re-initdb
Destroy the new cluster and recreate using below
exit
rm -rf /var/lib/pgsql/11/data
/usr/pgsql-11/bin/postgresql-11-setup initdb
Now bring up the PostgreSQL 11 service:
exit #get back in as root
service postgresql-11 start
su postgres
psql
-- repeat this for each database with PostGIS installed
\connect gisdb
ALTER EXTENSION postgis UPDATE;
-- you'll get some WARNINGs about backend which you can ignore
SELECT postgis_full_version();
-- should output
postgis_full_version
-----------------------------------------------------------------
POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110"
GEOS="3.7.0-CAPI-1.11.0 673b9939" PROJ="Rel. 4.9.3, 15 August 2016"
GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
(1 row)
- After you have upgraded all your database, you can delete the *2.1.sos you created
rm /usr/pgsql-11/lib/postgis-2.1.so
rm /usr/pgsql-11/lib/rtpostgis-2.1.so
Run the analyze script:
./analyze_new_cluster.shDelete the old cluster:
./delete_old_cluster.sh
