[postgis-users] Importing relations from database where postgis extension was created with a different schema

[Regina Obe]

Andrew,

 

I documented the steps here for those who have similar issue in future.

 

 

http://postgis.net/2017/11/07/tip-move-postgis-schema/

 

 

As noted in article the reason we made it not relocatable is mostly so we could schema qualify all the calls to functions that rely on other PostGIS functions.

We needed to do that because during pg_restore and CREATE MATERIALIZED, PostgreSQL doesn't use the search_path designated by the user, but instead replaces it with pg_catalog and the schema of the table/view,.

So any functions that rely on any other functions and need to be called during pg_restore (e.g. table constraints or materialized view creation)  not in the same schema as where the view or table resides will not be able to run.

 

Things such as raster constraint function that point to other functions in PostGIS then failed and prevented tables from restoring.  Similarly MATERIALIZED views also would try to run functions used in the views since they need to reconstitute data and would therefore also fail too.  It also caused issues with Foreign tables since function calls used in foreign table check constraints also  ignored search_path as well. 

 

To allow folks to install PostGIS in the schema they desired, we had to use the magic variable @extschema at .  People were against the idea of enforcing PostGIS to be installed in a particular schema for everyone so this was the compromise.

So we solved most problems except for ones such as other extensions relying on postgis functions – e.g pgRouting, postgis_sfcgal, postgis_topology, postgis_tiger_geocoder functions could conceivably be used in views and since these extensions can't control or find where PostGIS resides, they still will only work if in cases where database search_path is used.

 

The reason why PostgreSQL doesn't allow extensions marked not relocatable to be moved is that once CREATE EXTENSION/ALTER EXTENSION is done, the @extschema@ variable is macro replaced

with the schema the user specified, and once it's replaced, the extension machinery can't differentiate it from the rest of the code.

 

PostGIS during upgrade always rebuilds the functions so that is why using the NEXT version hack works.  The NEXT forces the extension machinery to replace the old schema qualified calls with the new schema qualified calls.

It's pretty convenient we have this hack which was designed to allow development upgrades, but works nicely for this purpose too.

 

Most other extensions don't have this hack so if they are marked non-relocatable they really can't be relocated without great pains.

 

Thanks,

Regina

 

 

 

 

------------------------------------------------
From: Andrew Joseph 


Thanks! that worked perfectly. As to why I thought installing it in the
public schema was preferred, I thought at one point there was a decision
made that CREATE EXTENSION postgis would install to a postgis schema by
default -so when I tested this out and found it wasn't the case I assumed
(wrongly) that there was still some reason to prefer the public schema as
the default installation location (instructions on postgis.net <http://postgis.net>  and in the
documentation show merely executing CREATE EXTENSION postgis;).

Given that it is apparently possible to relocate the schema with the
commands you just provided, I'm curious why it is set to be non relocatable
in the first place. Is there some kind of edge case scenario where one can
corrupt their database by moving the extension necessitating keeping it non
relocatable by default?




 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20171108/6e26dfc4/attachment.html>