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
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 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.