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
> >
