Re: PostgreSQL users on webhosting

If you are using Linux and you want to do things these ways, you can take
advantage loopback files systems. You can carve up a large disk this way and
control the space requirements per client.

Quoting Jeff Davis <jdavis-pgsql(at)empires(dot)org>:

> I must not have been clear. In postgres you can limit people to a
> tablespace (in 8.0 of course). You do this by giving them a database
> with a default tablespace, and only give them permission on that default
> tablespace. That works fine.
>
> The problem is, there is no limit to the size of a tablespace except the
> size of the underlying disk. That means if you create two tablespaces
> for two users on the same disk, than one user can run out the space of
> another user.
>
> A possible solution might be for postgres to somehow limit the size of
> the tablespace, but that might be too much overhead. Another solution is
> to put each user on their own partition, but that is impractical.
>
> Regards,
> Jeff Davis
>
> On Sat, 2005-01-08 at 14:12 -0500, Alex Turner wrote:
> > Not sure what overhead - but Oracle has this ;) Infact Oracle by
> > default puts each user in their own schema, and each user can be
> > assigned a default tablespace as a property of the user. With the
> > advent of Tablespace in pg 8.0, is it possible to set a user's default
> > tablespace?
> >
> > Alex Turner
> > NetEconomist
> >
> >
> > On Fri, 07 Jan 2005 13:03:25 -0800, Jeff Davis <jdavis-pgsql(at)empires(dot)org>
> wrote:
> > > That's an interesting idea. First, you can't (as far as I know) do it
> > > with just schemas to seperate the users. There is no default tablespace
> > > for an object created inside a given schema.
> > >
> > > However, there is a default tablespace for a given database. You can (as
> > > superuser) create a tablespace and permit only a specific user to use
> > > it, and then create a database within that tablespace (so that objects
> > > created in that database use only a specific tablespace). Users can't
> > > create their own tablespace, so they can't create objects out of that
> > > tablespace unless the superuser creates a new tablespace and gives them
> > > permission.
> > >
> > > That seems like it would work quite effectively, except that you need a
> > > bunch of size-limited areas to point the tablespaces at. It would
> > > probably be inconvenient to have many partitions. Although you could,
> > > like you said, put all the "cheap" accounts on one partition, and the
> > > expensive guys on their own disk. Then again, if you're going to single
> > > out accounts, why not just give the special hosting account their own
> > > instance?
> > >
> > > There's no really easy answer. It would be nice if postgres had a "max
> > > size" parameter for tablespaces, and then you could achieve reasoanble
> > > seperation between databases quite easily (while still sharing the
> > > buffers). I'm not sure what the overhead on a feature like that would
> > > be.
> > >
> > > Regards,
> > > Jeff Davis
> > >
> > > On Fri, 2005-01-07 at 10:38 +0100, Csaba Nagy wrote:
> > > > On Wed, 2005-01-05 at 21:34, Jeff Davis wrote:
> > > > > Benefits of multiple instances:
> > > > > (1) Let's say you're using the one-instance method and one of your
> web
> > > > > users is a less-than-talented developer, and makes an infinite loop
> that
> > > > > fills the database with garbage. Not only will that hurt
> performance,
> > > > > but if it fills the disk than no other users can even commit a
> > > > > transaction! If you seperate the instances, you can run each as its
> own
> > > > > uid and control each with quotas, etc.
> > > >
> > > > I wonder if this could not be achieved at least partially by using
> > > > schemas and set each user's schema to different tablespaces with
> > > > different space available on them ? Say, the bulk of the low paying
> > > > customers on a bulk partition, and the important customer on it's own
> > > > partition ? I actually would like to know if this is feasable...
> > > >
> > > > Cheers,
> > > > Csaba.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 9: the planner will ignore your desire to choose an index scan if
> your
> > > > joining column's datatypes do not match
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com