[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Install postgis on a different schema
From:       Ben Madin <ben () remoteinformation ! com ! au>
Date:       2009-11-26 5:14:33
Message-ID: 7BC6F620-5758-4680-A4CB-AE42B43CC58A () remoteinformation ! com ! au
[Download RAW message or body]

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@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@remoteinformation.com.au



							Out here, it pays to know...


_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]