Re: Data in postgres 'trackers' database, 'helpdesk' schema.

Hi Tom:

In message
<[email protected]>, Tom
Ekberg writes:
>We have currently have 28 trackers each having its own database in
>our main postgres database server. The database namespace on that
>server is getting a bit cluttered. I'd like to have one database,
>say 'trackers', and put data for a tracker into its own schema in
>that database. My first attempt at doing that for one of our
>trackers was 'not entirely successful'.

I can see this being difficult, but what did you actually try?

If I was trying to do it, my first step would be to generate a
database called "tracker".

Create a role (user) for each tracker to use. Allow each role full
access to the tracker database.

In psql attach to the database and "CREATE SCHEMA role;" for each role
(user).

Log in as one of the roles and and verify that "SHOW search_path;"
reports "$user,public".

Then try running:

   REVOKE CREATE ON SCHEMA public FROM PUBLIC;

to prevent any tracker from manipulating the public schema keeping
them in their own schema.

Then try initializing the database.

However, I'll bet this will fail at this point. IIRC roundup really
wants to create/delete databases and its check to see if the database
is initialized is really just a check to see if it can connect to the
db. If it can it will offer to delete the db and reinitialize it
nuking all your schema changes.

So my guess is you need to change the code in
backends/back_postgres.py to replace create/drop database to
create/drop schema instead. Plus you will have to play with
backends/back_postgres:db_exists(). Maybe using something like:

 SELECT schema_name FROM information_schema.schemata WHERE \
     schema_name = 'name';

(suggested from:
http://stackoverflow.com/questions/7016419/postgresql-check-if-schema-exists)

to see if the schema exists in the database.

I am not sure this is the best way to do it, but it's probably one
way.

So I don't have a good answer on doing this I'm afraid.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/