Robert,
You can get as complex as you like, but one great use of schemas (I think) has
been to manage backing up data - especially over the internet, another is to
manage user access at a more granular level.
if you put your static data (ie background maps) into one schema, then when
dumping you can use the -N flag to avoid dumping that schema. By doing this we
avoid backing up several hundred megabytes every night, but new research
location data is backed up daily - only a few megabytes
similarly, for some projects you might have data on users and access controls,
but when analysing the data this isn't necessary, so putting important (perhaps
research) data into it's own schema makes it easy to export from a database
server to a local machine for analysis.
Dont forget to
ALTER DATABASE mydatabase SET search_path TO mymainschema, myotherschema,
someotherstuff, public;
cheers
Ben
On 22/09/2011, at 6:17 PM, Robert Buckley wrote:
I have just read this explaining about how to structure data and functions
within postgresql
http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html
...The public schema is where the PostGIS functions and system
tables get installed, so if you dump that schema you get all those
definitions in your dump. If those definitions are mixed in amongst your
data, loading them into a fresh database gets tricky: are the paths to the
libraries the same? are there function name clashes? (The
utils/postgis_restore.pl script attempts manfully to strip out PostGIS
components from a dump file to allow a clean restore, but it is hard to get
100% performance.)
If, on the other hand, all your data is neatly separated into its own
schema, you can neatly backup just that schema and avoid having PostGIS
system information mixed in with your data. That means you can easily restore
your data into any version of PostGIS and PostgreSQL that you like. So
upgrades are easy easy easy.
Remember: Store your data in a schema other than public.
Basically Paul recommends saving geodata in a different schema to the postgis
functions.
My questions are...
1 if the data is located in a different schema which does not have the
800 odd postgis functions, are the functions still available to the data?
2 are cross schema queries allowed?
3 does it also make sense to seperate non-spatial tables into their own
schemas?
thanks for any advice,
Rob
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
