How do I successfully migrate a (very) old PostgreSQL database with PostGIS objects to a new version of PostgreSQL and PostGIS?
0
votes
0
answers
61
views
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.
Asked by Eric Swiggum
(694 rep)
Aug 2, 2024, 12:44 AM