One database serveral schemas and group roles

From: <joachim(dot)sommer(at)o2online(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: One database serveral schemas and group roles
Date: 2007-04-05 12:03:23
Message-ID: 200704051203.l35C3NRl013779@mail03.o2online.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hallo List,

I am bit stuck as a postgresql newbie with the definition of schemas. I am setting up a geodatabase with postgis under windows plattform - wapp strategy - and I would like to have several schemas within one database. Schema names should corresponde to the name of a group roles each having several single users.

1. Is it possible to work with group roles and schemas in this context ?

2. What is the typical workflow when someone sets up a database with different schema in (one) database ?

3. Is the following workflow suitable ?
3.1 createdb -U postgres -T postgis_template mygeodatabase
3.2 pg_dump --file=mygeodatabase.sql --format=p --schema=public --schema-only -U postgres
3.3 Open mygeodatabase.sql in editor search and replace schema 'public' with 'newschema'
3.4 Import new schema to mygeodatabase with psql -d mygeodatabase -f c:\my\path\mygeodatabase.sql -U postgres (or new user)

and then how to import new data to newschema.mygeodatabase ?

Are they further issues to keep in mind (e. g. set search_path to newuser) ?

I think there must be better ways to create new schema and to import data in my geodatabase.

How does it suits to postgis ? See example...
select AddGeometryColumn (<schema_name>, <table_name>, <column_name>, <srid>, <geometrytype>, <dimension>);

Thanks a lot for corresponding
Joe

Browse pgsql-general by date

  From Date Subject
Next Message Ashish Karalkar 2007-04-05 12:06:14 Re: Granting permissions
Previous Message Albe Laurenz 2007-04-05 12:00:27 Re: Granting permissions