Thread: PostgreSQL users on webhosting
hello, we have some webhosting servers and we can start postgresql support... I need to create for one webhosting account one postgresql account, which will have access only to databases created byh this postgresql account. I know, that it is no problem in mysql... thanx, miso
In a typical setup, you might do:
Edit pg_hba.conf to allow connections to the database "sameuser" which
is a special word meaning that the user can only connect to a database
of the same name.
Then, for each webhosting account you make (let's say the user is named
"foo" with password "bar"), execute the following SQL:
=# CREATE DATABASE foo;
=# CREATE USER foo WITH PASSWORD 'bar';
That works for most situations.
However, for truly good seperation, I recommend that you run a seperate
instance of postgresql (with a seperate $PGDATA directory) for each
user, and run it under the UID of that user. It requires a little more
disk space per account, but in a dollar amount it's virtually zero with
today's disk prices. You will be able to tie the user into filesystem
quotas, etc., much more easily, and also you could tune the DBs to the
individual users if needed.
Regards,
Jeff Davis
On Tue, 2005-01-04 at 14:06 +0100, Michal Hlavac wrote:
> hello,
>
> we have some webhosting servers and we can start postgresql support...
>
> I need to create for one webhosting account one postgresql account,
> which will have access only to databases created byh this postgresql
> account.
>
> I know, that it is no problem in mysql...
>
> thanx, miso
>
> ---------------------------(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
Jeff Davis wrote: >However, for truly good seperation, I recommend that you run a seperate >instance of postgresql (with a seperate $PGDATA directory) for each >user, and run it under the UID of that user. It requires a little more >disk space per account, but in a dollar amount it's virtually zero with >today's disk prices. You will be able to tie the user into filesystem >quotas, etc., much more easily, and also you could tune the DBs to the >individual users if needed. > > Out of curiosity, what kind of performance hit (whether CPU, memory, disk activity) is incurred with having a lot of postmasters running in this kind of a setup versus one postmaster with lots of databases? We typically run one postmaster for a lot of separate web applications, but I like the notion of a instance-per-user (for both security and maintenance). In the case of having several "big" databases on one server, would tuning stragegies need to keep in mind the settings of other instances, or would you just tune each one as if it were the only one on the box and let the OS deal with memory+disk load of multiple instances? /hope this question makes sense, waiting for coffee to kick in
On Jan 5, 2005, at 9:49 AM, Alan Garrison wrote: > Out of curiosity, what kind of performance hit (whether CPU, memory, > disk activity) is incurred with having a lot of postmasters running in > this kind of a setup versus one postmaster with lots of databases? We > typically run one postmaster for a lot of You'd have to have separate shared buffers for each which would eat away from the filesystem cache. Not to mention overhead of having many more PG's running (in terms of just processes htat need to be managed and memory used by each). You'd also have to have the users connect to PG on an alternate port and that may irritate some. In a web hosting (multi-user) environment it may make more sense. but you could also just give each user his own db and only allow sameuser access to each db via pg_hba.conf. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
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.
(2) You can do a certain amount of favoritism, i.e. you can allocate a
lot of resources to your best customers, and less to the low-paying
customers.
Costs:
(1) The databases can't use eachother's shared memory. That will mean
that the databases with high activity can't cache data in the shared
memeory of a database with low activity.
(2) The RAM from the extra processes for each user. If a database has 0
connections, it's still using memory for the postmaster.
(3) Each instance will require about 30MB of disk for the $PGDATA
directory. In contrast, using the one-instance method it only requires
5MB for an additional DB (approximate).
Overall, I'd say it would be difficult to run seperate instances if
you're trying to have hundreds of people on the same server. If you have
a more managable number you could do it quite effectively I think. I
would recommend lowering the per-instance shared memory so that the OS
could buffer more (mitigating cost #1).
If you can't run multiple instances, just consider the risks and
understand that you should try to limit the users somehow.
Regards,
Jeff Davis
On Wed, 2005-01-05 at 09:49 -0500, Alan Garrison wrote:
> Jeff Davis wrote:
>
> >However, for truly good seperation, I recommend that you run a seperate
> >instance of postgresql (with a seperate $PGDATA directory) for each
> >user, and run it under the UID of that user. It requires a little more
> >disk space per account, but in a dollar amount it's virtually zero with
> >today's disk prices. You will be able to tie the user into filesystem
> >quotas, etc., much more easily, and also you could tune the DBs to the
> >individual users if needed.
> >
> >
> Out of curiosity, what kind of performance hit (whether CPU, memory,
> disk activity) is incurred with having a lot of postmasters running in
> this kind of a setup versus one postmaster with lots of databases? We
> typically run one postmaster for a lot of separate web applications, but
> I like the notion of a instance-per-user (for both security and
> maintenance). In the case of having several "big" databases on one
> server, would tuning stragegies need to keep in mind the settings of
> other instances, or would you just tune each one as if it were the only
> one on the box and let the OS deal with memory+disk load of multiple
> instances?
>
> /hope this question makes sense, waiting for coffee to kick in
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
On Wed, 2005-01-05 at 13:52 -0500, Jeff wrote:
[snip]
> You'd have to have separate shared buffers for each which would eat
> away from the filesystem cache. Not to mention overhead of having
> many more PG's running (in terms of just processes htat need to be
> managed and memory used by each).
True. Those are the costs.
> You'd also have to have the users
> connect to PG on an alternate port and that may irritate some.
>
You can also use unix local domain sockets. I'm not sure if that gains
much, but most web hosts don't allow tcp/ip connections anyway.
> In a web hosting (multi-user) environment it may make more sense. but
> you could also just give each user his own db and only allow sameuser
> access to each db via pg_hba.conf.
>
I would like to add that there are risks associated with doing that. If
one user fills up the disk (like with an infinite loop in a web app)
than that affects all database users, since it's running as the
"postgres" user. No more commits can happen at all.
Regards,
Jeff Davis
how about to have only one DB with multiple DB shcemas and assign a DB user per schema? Will this solution use the multiple CPUs ? - I think it should.... this is my 2cents. --- Jeff Davis <jdavis-pgsql@empires.org> 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. > (2) You can do a certain amount of favoritism, i.e. you can > allocate a > lot of resources to your best customers, and less to the low-paying > customers. > > Costs: > (1) The databases can't use eachother's shared memory. That will > mean > that the databases with high activity can't cache data in the > shared > memeory of a database with low activity. > (2) The RAM from the extra processes for each user. If a database > has 0 > connections, it's still using memory for the postmaster. > (3) Each instance will require about 30MB of disk for the $PGDATA > directory. In contrast, using the one-instance method it only > requires > 5MB for an additional DB (approximate). > > Overall, I'd say it would be difficult to run seperate instances if > you're trying to have hundreds of people on the same server. If you > have > a more managable number you could do it quite effectively I think. > I > would recommend lowering the per-instance shared memory so that the > OS > could buffer more (mitigating cost #1). > > If you can't run multiple instances, just consider the risks and > understand that you should try to limit the users somehow. > > Regards, > Jeff Davis > > > On Wed, 2005-01-05 at 09:49 -0500, Alan Garrison wrote: > > Jeff Davis wrote: > > > > >However, for truly good seperation, I recommend that you run a > seperate > > >instance of postgresql (with a seperate $PGDATA directory) for > each > > >user, and run it under the UID of that user. It requires a > little more > > >disk space per account, but in a dollar amount it's virtually > zero with > > >today's disk prices. You will be able to tie the user into > filesystem > > >quotas, etc., much more easily, and also you could tune the DBs > to the > > >individual users if needed. > > > > > > > > Out of curiosity, what kind of performance hit (whether CPU, > memory, > > disk activity) is incurred with having a lot of postmasters > running in > > this kind of a setup versus one postmaster with lots of > databases? We > > typically run one postmaster for a lot of separate web > applications, but > > I like the notion of a instance-per-user (for both security and > > maintenance). In the case of having several "big" databases on > one > > server, would tuning stragegies need to keep in mind the settings > of > > other instances, or would you just tune each one as if it were > the only > > one on the box and let the OS deal with memory+disk load of > multiple > > instances? > > > > /hope this question makes sense, waiting for coffee to kick in > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > __________________________________ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com
On Wed, 2005-01-05 at 12:34 -0800, 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. > (2) You can do a certain amount of favoritism, i.e. you can allocate a > lot of resources to your best customers, and less to the low-paying > customers. (3) Different versions of PostgreSQL on the same box. Some hosting customers have applications that require older versions..and some developers want the latest version. (ie, we have a few customers running 8.0 RC2..and will want 8.0 the day it's ready). I find this flexibility a great reason why multiple instances are good for a hosting company. -Robby -- /*************************************** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | robby@planetargon.com * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now supporting PHP5 --- ****************************************/
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.
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
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@empires.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 >
Alex Turner <armtuk@gmail.com> writes:
> ... With the
> advent of Tablespace in pg 8.0, is it possible to set a user's default
> tablespace?
ALTER USER user1 SET default_tablespace = foo;
regards, tom lane
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@empires.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@postgresql.org so that your
> message can get through to the mailing list cleanly
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@empires.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@empires.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@postgresql.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
You might be interested to see a previous thread started by me on that very subject: <http://archives.postgresql.org/pgsql-general/2004-04/msg00365.php> I got one reply that was very informative by William White: <http://archives.postgresql.org/pgsql-general/2004-04/msg00366.php> The other reply suggested the multiple-postmaster idea: <http://archives.postgresql.org/pgsql-general/2004-04/msg00380.php> Anyway, it seems like you'd want to take some things into consideration first. One potential problem is that if you lose power, most journaling filesystems only journal the metadata. That might mean that the backing file of the loopback filesystem might get corrupted, even if you're doing journaling on both the host filesystem and the loopback filesystem. You may need to turn on actual data journaling for the host filesystem in order to prevent that. Correct me if I'm wrong on this point. However, data journaling on the host system can of course cause significant performance problems. Also, I'm not sure what the performance impact of running pgsql through a loopback filesystem would be (could be a lot since databases are normally bound by the i/o performance). Another potential problem is that let's say you gave 100MB to someone, and then the database starts to get really big, it may be difficult to give them more space without doing a full dump/reload. Not that big of an issue since disk space is so cheap now, but something to consider. Regards, Jeff Davis On Sun, 2005-01-09 at 21:05 -0500, Keith C. Perry wrote: > 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@empires.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 > >
I'd just like to add that having all the users in one database has
another minor disadvantage:
If you want to make use of PITR for your clients, and client A shares a
postgres instance with client B, and client A asks to be time warped,
then you have to time warp both, since both are in the same db instance.
Granted, that's sort of a minor issue, but something to consider.
It could become an issue if you have many clients sharing that database
and it would cause downtime for all those other clients while you're
warping back to get someone's lost data.
However, you don't lose all of the benefits of PITR. You still have the
capability to do incremental backups.
Regards,
Jeff Davis