Postgres Pro
Downloads
Home   >   mailing lists

Thread: "Quota"

"Quota"

From
Hannes Dorbath
Date:
What would be a solution to prevent a single user/schema to fill the
disk using PostgreSQL? Is it a good idea to use separate table spaces?

--
Regards,
Hannes Dorbath

Re: "Quota"

From
"Albe Laurenz"
Date:
Hannes Dorbath wrote:
>
> What would be a solution to prevent a single user/schema to fill the
> disk using PostgreSQL? Is it a good idea to use separate table spaces?

Table spaces are the only solution I can think of.
If you create a table space on a different file system, then a table in
that table space can only fill up that file system.

Yours,
Laurenz Albe

Re: "Quota"

From
Hannes Dorbath
Date:
On 27.06.2007 15:18, Albe Laurenz wrote:
> Hannes Dorbath wrote:
>> What would be a solution to prevent a single user/schema to fill the
>> disk using PostgreSQL? Is it a good idea to use separate table spaces?
>
> Table spaces are the only solution I can think of.
> If you create a table space on a different file system, then a table in
> that table space can only fill up that file system.

OK, as my file system implements a native per directory quota that sound
good. Thanks.


--
Regards,
Hannes Dorbath

Re: "Quota"

From
"Dawid Kuroczko"
Date:
On 6/27/07, Hannes Dorbath <light@theendofthetunnel.de> wrote:
> What would be a solution to prevent a single user/schema to fill the
> disk using PostgreSQL? Is it a good idea to use separate table spaces?

I am afraid currently you are stuck with tablespaces as a quoting tool.

Of course having a filesystem per user per quota is not feasible in
most circumstances.  I am contemplating using XFS filesystem's
quota to achieve per-directory quota.  Basically what you need is
use xfs_quota command.  Here's manual excerpt about enabling it:

       Enabling project quota on an XFS filesystem (restrict files in
log file directories to only using 1 gigabyte of space).

            # mount -o prjquota /dev/xvm/var /var
            # echo 42:/var/log >> /etc/projects
            # echo logfiles:42 >> /etc/projid
            # xfs_quota -x -c 'projects -c logfiles' /home
            # xfs_quota -x -c 'limit -p bhard=1g logfiles' /home

I haven't used it yet, but it does look promising (other than that,
there's ZFS if you are a Sun shop ;-))


Nooow, as we are saying, XFS has yet another nice thing: xfs_fsr
command which does online filesystem level defragmentation (for
example as a nightly job).  It does mix nicely with PostgreSQL's
1-GB table files.. :)

   Regards,
      Dawid

Re: "Quota"

From
Hannes Dorbath
Date:
On 27.06.2007 16:58, Dawid Kuroczko wrote:
> On 6/27/07, Hannes Dorbath <light@theendofthetunnel.de> wrote:
>> What would be a solution to prevent a single user/schema to fill the
>> disk using PostgreSQL? Is it a good idea to use separate table spaces?
>
> I am contemplating using XFS filesystem's quota to achieve per-directory quota.  Basically what you need is use
xfs_quotacommand. 

That is exactly what I have now, I was just wondering if that's a bad idea.

> Nooow, as we are saying, XFS has yet another nice thing: xfs_fsr
> command which does online filesystem level defragmentation (for
> example as a nightly job).  It does mix nicely with PostgreSQL's
> 1-GB table files.. :)

Yup, already in my crontab ;)

Thanks.


--
Regards,
Hannes Dorbath