0

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.

  • 1
    I generally use 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. – Paul Ramsey Oct 2 '18 at 16:54
  • I am having the same issue as you did. What do you mean that you forgot to migrate a template database? – aprc272 Jun 19 at 17:13
  • Here is more information about template databases: postgresql.org/docs/current/manage-ag-templatedbs.html – david.perez Jun 20 at 6:36
1

The problem is that I forgot to migrate a template database. Thereafter pg_upgrade upgrades successfully.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.