Re: "Quota"

On 6/27/07, Hannes Dorbath <light(at)theendofthetunnel(dot)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