Spatial and Geographic objects for PostgreSQL

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 the issue of
if you installed PostGIS in a schema other than the one you wanted to
it is not intuitive how to move it to a different 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 generally install postgis in the public schema.

CREATE EXTENSION postgis;

Now I’ll create a new schema to move it and add this schema to search_path

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.
Trying to run UPDATE TO “2.4.1” when you are already on 2.4.1 would trigger an error that you are already on that version.

More Posts

The error ‘postgis.backend’ is already set comes up every so often in PostGIS mailing list.
The issue arises often during or after an upgrade. I’ll go over causes for this I am aware of and how to fix.

The question goes something like this

After upgrading to Postgis 2.3 from 2.1, my server log is
filled with these messages :

“WARNING ‘postgis.backend’ is already set and cannot be changed until
you reconnect”

This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible ST_MapAlgebra or dumping out
their rasters as Pixel valued polygons they then filter
with WHERE val > 90,
where ST_Reclass does the same thing but orders of magnitude faster.