[postgis-users] Install postgis on a different schema

[Paragon Corporation]

Puneet,
> I have multiple future projects that have project-specific data, but also
use shared data. Inspired by the above post, I was thinking the following
variation --

> 1. One database called 'mydb'

> 2. A schema called 'public' that holds the 'spatial_ref_sys' table and all
the PostGIS functions

> 3. A schema called 'shared' that holds all the tables that are shared by
the projects

> 4. A schema for each project, hence, 'project1', 'project2', etc.

> Questions:

a. Is the above strategy sound?  YES

b. Does every spatial db instance have to have its own copy of PostGIS
functions and 'spatial_ref_sys' table? The reason I ask -- if I need to
update the PostGIS functions, do I need to do that in one place, or in every
db?  

YES, so if you upgrade -- you need to upgrade each DB separately (but not
each schema)

c. Will multiple schemas in the same db be able to use the PostGIS functions
installed in the 'public' schema of that db? Seems so from the above
strategy.

YES.  Very common practice.  Note you can backup schemas separately if you
want each backup to only have data for a given project.  

--
d. The notes above refer to setting the search_path and the session_path.
Where is that done?

You can do it in postgresql.conf or at the database level.  The database
level is better I think

ALTER DATABASE mydb SET search_path = public, shared


The above will have it set for good, except if you restore a db, you need to
remember to rerun the above command.

Leo