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;

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 through a dummy version
ALTER EXTENSION postgis
  UPDATE TO "2.4.1next";

-- Force a second upgrade to remove references to the dummy version
ALTER EXTENSION postgis
  UPDATE TO "2.4.1";

Note the use of the extension version that includes next.

The next version step is needed in order to upgrade all the schema qualified function references to the new schema location. next is designed to allow upgrading a postgis extension to a version it is already on.