19

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).
  • 1
    Check out the new answer here gis.stackexchange.com/a/270522/6052 – Evan Carroll Feb 6 '18 at 20:55
  • 3
    Yes, it is still valid. The main point being it is cleaner, as you separate user data from system data and functions. – John Powell Apr 28 '18 at 19:01
  • I am not a PostGIS user but I suspect that the best answer to your question may now be gis.stackexchange.com/a/270522/115 so if you agree I would encourage you to move your Accept checkmark to it. – PolyGeo Apr 29 '18 at 0:41
  • 1
    This question should be two questions. The accepted answer does not answer the question as written. This question should be re-opened as it's NOT a duplicate of this question, which is asking whether the PostGIS objects can themselves work with objects not in the public schema. That other question is about installing the PostGIS extension objects into a schema other than public. These are two different things! – Kenny Evitt Aug 9 '18 at 15:16
5

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;
  • 2
    This does NOT answer the question as written; it should NOT be the accepted answer. It is clearly asking about "not having the default PostGIS tables and views in my new data schema (they are in the public schema within the same database)". This answer is useful, and it's what I was looking for, but it's not directly related to the question. – Kenny Evitt Aug 9 '18 at 15:22
20

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;

  • Thanks Martin. Does it mean I am able to use spatial functions on tables in a custom schema other than "public"? – alextc Feb 21 '14 at 3:38
  • How do you add data to another schema, ie. not public? Adding data with eg. shp2psql to test.table still puts data in public? – knutole Jul 3 '15 at 9:54
  • @knutole. As I mentioned I used ArcCatalog to import/new feature classes. I am not sure if you can use shp2psql to add data to a custom schema other than public. – alextc Oct 3 '17 at 3:54
  • 1
    This should be the accepted answer. – Kenny Evitt Aug 10 '18 at 14:22
12
  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.)

6

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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