0

I've been trying to migrate a PostgreSQL database on version 9.0.1 with PostGIS objects to Postgres version 12.9 (both on linux) and I've been encountering numerous errors. I believe the source version of PostGIS is version 1.5 however when I execute the query SELECT PostGIS_Full_Version(); in the source db I get the following

Error: function postgis_full_version() does not exist

The only reason I suspect it is version 1.5 is because doing a "regular" pg_dump copy (as if there are no postgis objects) results in seeing the following error in the log:

ERROR: could not access file "$libdir/postgis-1.5": No such file or directory

The destination PostGIS version is 3.0.3.

In previous migrations from this same source instance I've never had any issues using the available postgis_restore.pl script. One difference with this database is there is a postgis schema, also I have a feeling this database might have been previously migrated from an even older version of PostgreSQL/PostGIS as it has the word _Legacy in the database name.

Common examples of errors seen in the log include:

ERROR: operator does not exist: geometry postgis.&& geometry

ERROR: type "postgis.box2d" does not exist

invalid command \N

Also, any table with PostGIS datatypes do not copy over. I perform all the usual steps like adding the PostGIS extension and topology extension. I also tried migrating this database to PostgreSQL 10, but got the same result. So my hunch is maybe I should pg_dump the postgis schema first and then try the postgis_restore.pl script and see if all the tables/schemas copy over? I'm fairly new to PostgreSQL so I am kind of at a loss here. I've also tapped my local Esri expert here and they didn't really know what to say.

Improve this question
3
  • 1
    Upgrading will be difficult. I think there is no point in upgrading from one EOL version to another EOL (pg12 will be EOL in a few months). I recommend considering upgrading to a newer version than 12. Upgrading will be slow and via dump. I remember in some releases (something like 2.0) upgrading postgis itself was non-trivial. Commented Aug 2, 2024 at 6:30
  • 1
    In the basic scenario, create extension postgis on the new version of the database, migrate the structure of user tables, but do not touch the postgis functions/operators/create type (in 9.0 there was no create extension, so pg_dump will try to dump the entire postgis of the old version and this will not be able to import, as expected), when the script is ready to migrate the database schema without errors - migrate with the data of these tables. Commented Aug 2, 2024 at 6:31
  • @melkij So by doing that, I will be able to move those user tables with the postgis/spatial data types? I'm confused as to why pg_dump/postgis_restore.pl can't already handle that Commented Aug 2, 2024 at 12:53

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.