[postgis-users] Install postgis on a different schema

[Ben Madin]

Puneet,

Alternatively, I use postgis across a number of databases, but when I install it:

create schema postgis;

set search_path postgis, other schemas;

\i postgis.sql -- or you could set the search_path in the postgis_sql file - there used to be some problems here with schema qualified table / functions names in the postgis.sql, but I think they are mainly gone now.

set search_path otherschemas, postgis; -- and alter database etc.

Then when I backup I can just avoid backing up this schema using the appropriate flag. We replicate across dev and production servers, and backup offsite, and so want to reduce the size of the backup as much as possible. Obviously if you have custom projections...

(for the same reason, I also normally have another schema (gis?) for basically static gis data, rather than dynamic project data, as it runs to gigabytes, and doesn't need daily backup)

(And yes, we are looking at more sophisticated backup strategies (using WAL) but haven't quite gotten there yet)

cheers

Ben




On 25/11/2009, at 2:29 , Paragon Corporation wrote:

> 
> 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...