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

On 08/29/2014 09:17 PM, John P. Rouillard wrote:
> 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.
>[...]

This is something that is of interest to me as well.

Not only only is it less cluttered to have multiple trackers 
in a single database but it also makes it easier to collect
aggregate stats across the multiple trackers.

I made the modifications suggested above, and it seems to do
what it is supposed to.  I created an issue with an attached 
patch file:
  http://issues.roundup-tracker.org/issue2550852
The patch file was generated from the current development 
revision but it applied (and seems to wotk) ok with 
Roundup-1.5.0.

With the modification, one tells Roundup to use a Postgresql
schema by following the database name with a dot and the schema 
name in the RDBMS.NAME value in the config.ini file, eg:

   [rdbms]
   name = roundup.tracker1

If the name does not have the schema part, roundup will work as 
it did before.  With the schema part Roundup will drop, create 
or use that schema in the named database.  When doing a 
"roundup-admin init", the database must already exist and the 
roundup user must have been granted "create" privilege in the 
database (required to create a new schema).

Obviously, since I am very new to Roundup, the patch needs vetting 
before it is used seriously.  And it is without doc updates or 
tests -- I hoped to get some feedback that the approach was ok and 
confirmation that such a feature would be useful before investing 
a lot more time.

------------------------------------------------------------------------------
Want excitement?
Manually upgrade your production database.
When you want reliability, choose Perforce
Perforce version control. Predictably reliable.
http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk