I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X
Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 available, whereas Postgres 10 has PostGis 2.4.4. So I compiled PostGIS 2.4 for Postgres 9.3 and installed it. Then for each database, I've made:
ALTER EXTENSION postgis UPDATE;
In this way, I've migrated to PostGIS 2.4.
But when I run pg_upgrade, I receive this error:
Cannot access the file «$libdir/postgis-2.1»: It doesn't exist the file or the directory.
If I do this:
cd /usr/pgsql-10/lib/
ln -s postgis-2.4.so postgis-2.1.so
It doesn't work either, because if I run pg_ugrade another time:
pg_restore: creating FUNCTION "public.geomfromewkb("bytea")" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 547; 1255 90291 FUNCTION geomfromewkb("bytea") ema pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "LWGEOMFromWKB" in file "/usr/pgsql-10/lib/postgis-2.1.so"<br> Command was: CREATE FUNCTION "public"."geomfromewkb"("bytea") RETURNS "public"."geometry" LANGUAGE "c" IMMUTABLE STRICT AS '$libdir/postgis-2.1', 'LWGEOMFromWKB';
Why does Postgres 9.3 reference PostGIS 2.1 after migrating to 2.4?
The other option I see is to compile PosGIS 2.1 for Postgres 10.
UPDATE 1
After reading this tip, I've done for each database I've done:
psql mydb -c 'ALTER EXTENSION postgis UPDATE;'
psql mydb -c 'ALTER EXTENSION postgis_topology UPDATE;'
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.1/uninstall_legacy.sql
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.4/legacy.sql
with identical results.
SELECT probin, COUNT(*)
FROM pg_catalog.pg_proc
WHERE probin LIKE '%/postgis%'
GROUP BY probin
ORDER BY probin;
returns:
probin | count
---------------------+-------
$libdir/postgis-2.1 | 373
UPDATE 2
This guide explains the internals of PostGIS migration.
Clearly, PostGIS tells it migrates the extension, but doesn't apply the migration script to change the procedures.
ALTER EXTENSION postgis UPDATE TO VERSION '2.4.1'but I'm a bit surprised that applying the 2.4 legacy script doesn't cause the number of functions using the 2.4 library to be non-zero. Something is odd with your setup methinks.