Sample Header Ad - 728x90

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