Friday 28 July 2017

Using schema other than public in PostGIS?



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



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;

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...