Thread: Database quota

Is there any way to stablish a maximum size for a database? Would
setting each database directory to user:pgsql, 770 and setting a file
system quota for user "user" work? I mean, would postmaster handle
the limit gracefully?

I'm thinking about a PG hosting environment. I looked through the
docs and online books but couldn't find the answer.

Thanks in advance for any tip.


Fernando P. Schapachnik
fschapachnik@vianetworks.com.ar

Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:
> Is there any way to stablish a maximum size for a database? Would
> setting each database directory to user:pgsql, 770 and setting a file
> system quota for user "user" work? I mean, would postmaster handle
> the limit gracefully?

If you can exclude pg_xlog and pg_clog from the quota, I believe that
failure to extend other tables would be reasonably graceful.  Failure to
extend the log files will cause a system stop, however.  (The good news
is that the sizes of the log file directories are fairly predictable.
Log file segments are recycled when no longer needed, so the total space
used doesn't really grow without bound, at least not in 7.2.)

            regards, tom lane

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:
> > Is there any way to stablish a maximum size for a database? Would
> > setting each database directory to user:pgsql, 770 and setting a file
> > system quota for user "user" work? I mean, would postmaster handle
> > the limit gracefully?
>
> If you can exclude pg_xlog and pg_clog from the quota, I believe that
> failure to extend other tables would be reasonably graceful.  Failure to
> extend the log files will cause a system stop, however.  (The good news
> is that the sizes of the log file directories are fairly predictable.
> Log file segments are recycled when no longer needed, so the total space
> used doesn't really grow without bound, at least not in 7.2.)

Taking that into account everything works fine.

Thanks!


Fernando P. Schapachnik
fschapachnik@vianetworks.com.ar

Tom Lane wrote:
> Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:
>
>>Is there any way to stablish a maximum size for a database? Would
>>setting each database directory to user:pgsql, 770 and setting a file
>>system quota for user "user" work? I mean, would postmaster handle
>>the limit gracefully?
>
>
> If you can exclude pg_xlog and pg_clog from the quota, I believe that
> failure to extend other tables would be reasonably graceful.  Failure to
> extend the log files will cause a system stop, however.  (The good news
> is that the sizes of the log file directories are fairly predictable.
> Log file segments are recycled when no longer needed, so the total space
> used doesn't really grow without bound, at least not in 7.2.)

Unless you make a typo in the where clause of a function and update half
a million rows 1200 times instead of 1200 rows out of half a million :(
It was one tranaction so the WAL grew to 312 segments before the
postmaster died due to a full /var/.

The good news, and shining example of the stability of PostgreSQL, was
that after a little space was freed PostgreSQL just restarted and
recycled all the WAL segments. Even dying is done gracefully by PostgreSQL.

Jochem

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> Unless you make a typo in the where clause of a function and update half
> a million rows 1200 times instead of 1200 rows out of half a million :(
> It was one tranaction so the WAL grew to 312 segments before the
> postmaster died due to a full /var/.

You need to update to 7.1.3 or later --- that WAL growth problem is
long gone.

> The good news, and shining example of the stability of PostgreSQL, was
> that after a little space was freed PostgreSQL just restarted and
> recycled all the WAL segments. Even dying is done gracefully by PostgreSQL.

We have users who let us know when it doesn't ;-)

            regards, tom lane