How to migrate a PostgreSQL database with the postgis schema?

Question

I've been tasked to migrate a bunch of postgres databases from version 10.5 to 16. Most contain the PostGIS extension (ver. 2.4.4), I've successfully migrated a few via the postgis_restore.pl script. However, I am having difficulties when a database has the postgis schema present. I don't understand why these "gis" databases have the postgis schema and some do not. I don't know the history of these databases perhaps they moved from an even older version of postgres/postgis, hence the presence of the postgis schema.

I'm getting the classic error during migration:

ERROR: type "postgis.geometry" does not exist

So far the only solution has been for me to create the table structures and tinker with the the_geom column before migrating with postgis_restore.pl. This is OK when there are only a few objects that need created. But this becomes burdensome when I have to create dozens of objects due to this issue. Is there an easier way to handle this? I thought about pg_dump the postgis schema with all the tables, operators, functions, but should I be doing that?

Answer 1

I can only guess what your problem is, but I guess it has to do with functions. Recent versions of PostgreSQL set the search_path empty during restore for security reasons. That makes functions fail if they depend on the current setting of search_path. That will for example hit you if that function is used in an index definition.

You can find out which function is the problem from the context of the error message.

If I guessed right, the remedy is to explicitly set an appropriate search_path on such functions before you upgrade:

ALTER FUNCTION whatever() SET search_path = postgis;

The best practice is to explicitly set a safe search_path on all your functions.

• Eric Swiggum: Thx for your quick response, I tried the search_path method but encountered the same issue. I then tried the move PostGIS extension to a different schema after creating the shell of the db and was able to postgis_restore all objects. However, this raises another question, should I undo the schema move of PostGIS once complete, effectively creating postgis objects under the public schema like normal? Or would this cause issues since there are user objects that reference objects in the postgis schema?
• Laurenz Albe: There is too little information to know what is going on, so I had to guess. If you explore what exactly causes the failure, I might be able to answer.