Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm currently setting up a fresh install of PostGIS 2.0.2 and PostgreSQL 9.1.6 on Ubuntu. I've recently come across some information indicating that using the public schema to store all data is not a good idea.
For this reason, I've set up a schema called data and made myself the owner. Here are my questions:

  1. Besides setting the owner, do I need to pay attention to anything on the Privileges tab when creating this new schema (through pgAdmin III)?
  2. Could I get the same benefits by storing my data in the public schema and dumping all data into a separate schema before doing a backup/restore (this would save a few keystrokes when using ogr2ogr)?
  3. Will I run into any trouble by not having the default PostGIS tables and views in my new data schema (they are in the public schema within the same database)?

Thank you!

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted
  1. One of the possible organizational strategies you can build with schemas is to allow a user to run rampant in one schema, but be unable to foul things up in another. So if you want to use schemas in this way, that can be done in the privileges tab of pgAdmin. But it's not required that you do that, so if you just want to maintain the same privileges across multiple schemas, that's fine.

  2. Based on the articles you linked to, the problem with keeping everything in public is that when you dump data, you're likely to get system tables and relationships mixed in with your data. If you move all your data to a new schema, you don't have to worry about that ever again.

  3. No trouble at all. (For proof, notice that you don't have to specify public.spatial_ref_sys when you want to search the SRS table.)

share|improve this answer
add comment

One additional tip (maybe you already came across this). You'll probably want to add the "data" schema to the user's default search_path. Something like:

ALTER USER <your_user_name> SET search_path=public,data,$USER; 

Regarding your point 2, sometimes you need to restore when you no longer have access to the original DB. (That's one of the reasons for backups...) so you might not have the chance to move your data to a separate schema when you actually need it.

share|improve this answer
    
Great tip. Thank you! –  Paul Dec 5 '12 at 22:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.