Using schema other than public in PostGIS?

Question

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, but is it a good idea?

My concerns are:

1. Besides setting the owner, I may need to pay attention to things on the Privileges tab when creating this new schema (through pgAdmin III);
2. I may not 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); and
3. I may run into 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).

Answer 1

When you spatially-enable a PostGIS database, the relevant functions, SRS table, and views are placed in the public schema, as you state. That does not mean that all or any of your own spatial tables need to be in the same public schema. PostGIS will still work on all spatial data in the "new" schemas.

In fact, I usually place my application-specific tables in a separate schema. That way, if you need to do a major version upgrade to PostGIS, you can keep your application-specific table backups and restores as a separate procedure from the one that replaces the spatial tools.

So, I think you're doing well. Finally, in case you didn't do it already, it's a good idea to add the new schema to the search path:

ALTER DATABASE my_db SET search_path = gc, public;

Answer 2

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.)

Answer 3

This is now addressed on the official site in a page titled Move PostGIS extension to a different schema. The correct method is to install the extension into public. This is the only option. The extension no longer supports relocation. The next thing is to run the following commands, (copied from the site),

UPDATE pg_extension 
  SET extrelocatable = TRUE 
    WHERE extname = 'postgis';

ALTER EXTENSION postgis 
  SET SCHEMA postgis;

Answer 4

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.

Answer 5

We use the public schema for temporary analysis / dev table results, then then go into more organized schemas (folders?) for permanent use.