Move PostGIS extension to a different schema

As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.

While this change fixed some issues with database restore, it created a new issue for people who already installed the exention into a schema, but now wish to move it to a new schema. Luckily there is a way to do this.

For this exercise, I will install PostGIS in the default schema and then demonstrate how to move it into another schema location.

You can run these steps using psql or pgAdmin or any other PostgreSQL tool you want.

Most people have their default schema set to public so not explicitly specifying an install schema will install postgis in the public schema.

CREATE EXTENSION postgis;

Now lets create a new schema to move it and add this schema to search_path

CREATE SCHEMA postgis;

ALTER DATABASE mydb
 SET search_path = public,postgis;

GRANT USAGE ON SCHEMA postgis to public;

If you are running PostGIS 2.3 or higher, trying to move to a different schema using the usual step will fail with an error:

ALTER EXTENSION postgis
  SET SCHEMA postgis;

ERROR:  extension "postgis" does not support SET SCHEMA

To allow the move do these steps:

-- Temporarily made postis relocatable
UPDATE pg_extension
  SET extrelocatable = true
	WHERE extname = 'postgis';

-- Relocate it
ALTER EXTENSION postgis
  SET SCHEMA postgis;

-- Force an upgrade toh a dummy version
-- For PostGIS 3.5.0+ do
ALTER EXTENSION postgis
  UPDATE TO "ANY";

-- For PostGIS 3.4 and lower use next
ALTER EXTENSION postgis
  UPDATE TO "3.4.3next";

-- Force a second upgrade to do the real upgrade
ALTER EXTENSION postgis UPDATE;

Note the use of the extension version that includes next or ANY.

The next or ANY version step is needed in order to upgrade to a version other than current version because same version to same version updates aren’t allowed by the extension system. The final upgrade does the work of upgrading to the latest installed version.

In the process all functions are reinstalled with the @extschema@ variable in the scripts now referencing the new schema location.

NOTE: if you have postgis_raster and/or postgis_sfcgal installed as well, repeat the same above steps with postgis_raster and postgis_sfcgal. As these two extension will not work properly unless they are installed in the same schema as postgis.