[postgis-users] MacPorts/PostGIS - Installation in different Schema

[Ryan Hofschneider]

On Jan 31, 2008, at 9:05 AM, Stefan Schwarzer wrote:

> I would like to know if somebody could tell me how I can - by using  
> the PostGIS MacPort - install the postgis functions not in the  
> schema "public", but in another schema (to separate it from the  
> tables which are being inserted into the public schema).

I've taken a similar (but different) approach to the one Kevin Neufeld  
described. I load all the PostGIS functions/objects into a separate  
schema called postgis, and load all of the user-defined stuff as well  
as the geometry_columns and spatial_ref_sys tables in the public schema.

This was driven by a need for the database owner account (which is not  
a database superuser) to be able to dump and reload the database  
without involving a database administrator that has superuser  
privileges. (Registering the PostGIS functions that are C-based  
requires superuser privileges.)

I have used PostGIS under this configuration with success, though  
admittedly I do not stress its abilities. And although I have not had  
to use the other PostgreSQL extensions simultaneously with each other  
and do not know if it would work, putting each extension under its own  
schema seems to be the ideal way to scope them rather than putting  
them all into public.

Below is how I set up PostGIS in its separate schema, assuming a  
database named foobar, and a non-superuser account database owner  
named foobar.

Cheers,
Ryan

% createdb -U postgres foobar
% psql -U postgres foobar
CREATE LANGUAGE plpgsql;
CREATE SCHEMA postgis;
SELECT set_config('search_path', 'postgis', false);
\i /usr/share/pgsql/contrib/lwpostgis.sql
GRANT USAGE ON SCHEMA postgis TO foobar;
DROP TABLE geometry_columns;
DROP TABLE spatial_ref_sys;
ALTER DATABASE foobar OWNER TO foobar;
ALTER SCHEMA public OWNER TO foobar;
ALTER DATABASE foobar SET search_path = public,postgis;

Then as the foobar user I re-create the geometry_columns and  
spatial_ref_sys tables (copying the relevant portions from  
lwpostgis.sql).

Dumping the database looks like:
pg_dump -U foobar -n public > dump.sql

Reloading the database looks like:
% psql -U foobar
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO PUBLIC;
\i dump.sql