Thread: DBs and Schemas
I'm looking for some input on a configuration I'm implementing. The long
term goal is to providing hosting for companies and organizations with a
basic/generic set of applications that use PostgreSQL.
Most of these applications want to be installed in their own database.
Given I'm looking at 6 or so already this would be 6+ databases per
company. Not my idea of an easy to admin situation.
My plan is to make one database (per company/organization) and modify the
installation scripts and the applications so they end up in and using
different schemas (probably named the same as the database would have been
named.)
I currently see see several issues.
1. Modifying the applications to use schemas instead of connecting to
specific databases. Getting the authors to use schemas so I do it
once.
2. PostgreSQL usernames are global to the instance (?) not specific to the
database which means each username has to/should include a grouping
type of prefix/postfix identifier. I think this is so. Please tell me
I'm wrong!
3. Isolation of users in a database from other users of another database.
(Grants, groups, etc.) Just making sure I do it and not cutting them
off from system stuff they need.
And several more, I'm sure.
Most of the reading I've done so far hasn't got into this very deep. (What
did I miss in this arena?) I have done this type of setup before many
years (10+) ago using Oracle but think there are significant differences
in their way and the PostgreSQL way.
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
> 1. Modifying the applications to use schemas instead of connecting to
> specific databases. Getting the authors to use schemas so I do it
> once.
One thing I'd suggest would be to modify the application to issue a
"set search_path = yourschema;" at the beginning, then the rest of the
application wouldn't need to change. That's what I did when I did
something similar.
Adam Ruth
On Jan 6, 2004, at 12:39 PM, Roderick A. Anderson wrote:
> I'm looking for some input on a configuration I'm implementing. The
> long
> term goal is to providing hosting for companies and organizations with
> a
> basic/generic set of applications that use PostgreSQL.
>
> Most of these applications want to be installed in their own database.
> Given I'm looking at 6 or so already this would be 6+ databases per
> company. Not my idea of an easy to admin situation.
>
> My plan is to make one database (per company/organization) and modify
> the
> installation scripts and the applications so they end up in and using
> different schemas (probably named the same as the database would have
> been
> named.)
>
> I currently see see several issues.
>
> 1. Modifying the applications to use schemas instead of connecting to
> specific databases. Getting the authors to use schemas so I do it
> once.
>
> 2. PostgreSQL usernames are global to the instance (?) not specific to
> the
> database which means each username has to/should include a grouping
> type of prefix/postfix identifier. I think this is so. Please
> tell me
> I'm wrong!
>
> 3. Isolation of users in a database from other users of another
> database.
> (Grants, groups, etc.) Just making sure I do it and not cutting
> them
> off from system stuff they need.
>
> And several more, I'm sure.
>
> Most of the reading I've done so far hasn't got into this very deep.
> (What
> did I miss in this arena?) I have done this type of setup before many
> years (10+) ago using Oracle but think there are significant
> differences
> in their way and the PostgreSQL way.
>
>
> TIA,
> Rod
> --
> "Open Source Software - You usually get more than you pay for..."
> "Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
On Tue, 2004-01-06 at 22:06, Adam Ruth wrote: > > 1. Modifying the applications to use schemas instead of connecting to > > specific databases. Getting the authors to use schemas so I do it > > once. > > One thing I'd suggest would be to modify the application to issue a > "set search_path = yourschema;" at the beginning, then the rest of the > application wouldn't need to change. That's what I did when I did > something similar. You can use ALTER DATABASE to set that up permanently, without touching the application. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Thou shalt not avenge, nor bear any grudge against the children of thy people, but thou shalt love thy neighbour as thyself. I am the LORD." Leviticus 19:18
On Tue, 6 Jan 2004, Adam Ruth wrote:
> One thing I'd suggest would be to modify the application to issue a
> "set search_path = yourschema;" at the beginning, then the rest of the
> application wouldn't need to change. That's what I did when I did
> something similar.
Neat idea. I was thinking that most of the applications use a specific id
to access their database so I was going to do this at the user level
(alter user). I think it will depend on the specific application as to
how I'd do this.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
On Tue, 6 Jan 2004, Oliver Elphick wrote:
> You can use ALTER DATABASE to set that up permanently, without touching
> the application.
I'd thought of this but since there will be several applications installed
and some, I am sure, will have same-named tables this could back-fire.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
On Tue, Jan 06, 2004 at 03:34:21PM -0800, Roderick A. Anderson wrote: > On Tue, 6 Jan 2004, Oliver Elphick wrote: > > > You can use ALTER DATABASE to set that up permanently, without touching > > the application. > > I'd thought of this but since there will be several applications installed > and some, I am sure, will have same-named tables this could back-fire. Are you actually going to be doing joins between these applications? If not, why not setup multiple databases, then you can be sure they won't conflict. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
On Jan 6, 2004, at 3:27 PM, Oliver Elphick wrote: > On Tue, 2004-01-06 at 22:06, Adam Ruth wrote: >>> 1. Modifying the applications to use schemas instead of connecting to >>> specific databases. Getting the authors to use schemas so I do it >>> once. >> >> One thing I'd suggest would be to modify the application to issue a >> "set search_path = yourschema;" at the beginning, then the rest of the >> application wouldn't need to change. That's what I did when I did >> something similar. > > You can use ALTER DATABASE to set that up permanently, without touching > the application. If you were combining several databases into one database with several schemas, each application will use a different schema. Thus each application would need to specify which schema it's using when starting up.
On Wed, 7 Jan 2004, Martijn van Oosterhout wrote:
> Are you actually going to be doing joins between these applications?
> If not, why not setup multiple databases, then you can be sure they
> won't conflict.
Well in at least one situation I can think of there will be joins. And
with a minimum of six applications for a company/organization that adds up
to quite a bit of admin work when there are several company/organizations.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"