Hi,

******* I have a single Postgres server which will be hostingmultiple databases belonging to different users. What would be the recommendation to limit the size of each of the databases?

I've considered creating a tablespace in a directory owned by the user , soI can use Linux quotas to prevent higher disk usage , but this turned out be a bad thought, as all the files are anyway owned by the postgres user and so disk quotas won't have any effect.

Now I'm contemplating having a cron job which checks the size of each tablespace belonging to a user and somehow informing the admin about db's getting over the threshold size.

Is there anyone who has tried a better* way to limit the db sizes ?

cheers

/Dev

>>> Dev <dev_hyd2001@yahoo.com> wrote:
> I have a single Postgres server which will be hosting
multiple
> databases belonging to different users. What would be the
recommendation to
> limit the size of each of the databases?

Have you considered running a different cluster for each user?
Each should be run with a different OS user ID, and it would be
pretty easy to limit the size of each.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

yes, thats an option but currently the last resort! Doesn't it get difficult to manage so many instances of servers ? Also don't we then need to keep track of different port numbers for each user/group ?

/Dev


--- On Thu, 7/3/08, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Subject: Re: [ADMIN] Best way to limit database sizes
To: pgsql-admin@postgresql.org, dev_hyd2001@yahoo.com
Date: Thursday, July 3, 2008, 6:28 PM

>>> Dev <dev_hyd2001@yahoo.com> wrote:
> I have a single Postgres server which will be hosting
multiple
> databases belonging to different users. What would be the
recommendation to
> limit the size of each of the databases?

Have you considered running a different cluster for each user?
Each should be run with a different OS user ID, and it would be
pretty easy to limit the size of each.

-Kevin

How about a trigger that for every say 1000 or 10000 update or inserts (or
timely if cron job) will use pg_stats or pg_statistics to decide if a role
(user) privileges should be modified to read only (or not)

Cheers
Medi

On Thu, Jul 3, 2008 at 10:48 AM, Dev <dev_hyd2001@yahoo.com> wrote:

> yes, thats an option but currently the last resort! Doesn't it get
> difficult to manage so many instances of servers ? Also don't we then need
> to keep track of different port numbers for each user/group ?
>
> /Dev
>
>
> --- On *Thu, 7/3/08, Kevin Grittner <Kevin.Grittner@wicourts.gov>* wrote:
>
> From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
> Subject: Re: [ADMIN] Best way to limit database sizes
> To: pgsql-admin@postgresql.org, dev_hyd2001@yahoo.com
> Date: Thursday, July 3, 2008, 6:28 PM
>
> >>> Dev <dev_hyd2001@yahoo.com> wrote:
> > I have a single Postgres server which will be hosting
> multiple
> > databases belonging to different users. What would be the
> recommendation to
>
> > limit the size of each of the databases?
>
> Have you considered running a different cluster for each user?
> Each should be run with a different OS user ID, and it would be
> pretty easy to limit the size of each.
>
> -Kevin
>
>
>

>>> Dev <dev_hyd2001@yahoo.com> wrote:
> --- On Thu, 7/3/08, Kevin Grittner <Kevin.Grittner@wicourts.gov>
wrote:
>> Have you considered running a different cluster for each user?

> Doesn't it get difficult to manage so many instances of servers ?

We haven't found it to be so. xargs is your friend, fed by find or
cat.

> Also don't we then need to keep
> track of different port numbers for each user/group ?

Yes.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 3 juil. 08 à 18:07, Dev a écrit :
> I've considered creating a tablespace in a directory owned by the
> user , so I can use Linux quotas to prevent higher disk usage , but
> this turned out be a bad thought, as all the files are anyway owned
> by the postgres user and so disk quotas won't have any effect.

What if you put each tablespace on a LVM partition of a control sized,
extensible?

- --
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkhtJDcACgkQlBXRlnbh1bkQlACfY825U1pq7L ivch4sPSEgj1x6
iCMAoMN9Wjxb3KFOHbOmj2oi5sqnvyUn
=IeBI
-----END PGP SIGNATURE-----

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Thu, Jul 03, 2008 at 09:10:47PM +0200, Dimitri Fontaine wrote:

> >I've considered creating a tablespace in a directory owned by the
> >user , so I can use Linux quotas to prevent higher disk usage , but
> >this turned out be a bad thought, as all the files are anyway owned
> >by the postgres user and so disk quotas won't have any effect.
>
> What if you put each tablespace on a LVM partition of a control sized,
> extensible?

This is a desaster waiting to happen. I'd say never let a database get
out of disk space. Rather implement soft limits like regular cron jobs
and make them pay for overusage.

Also note that DB size on disk and amount of data stored in DB might be
a lot different because of bloating issues, indices etc.

Tino..oO(But LVM/tablespace should work.)

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Thanx for all the suggestions. I'm planning to go ahead with the trigger approach and locking databases for writes when it goes over the threshold! Setting hard limits looks like a bad idea !

cheers

/Dev


--- On Thu, 7/3/08, Tino Schwarze <postgresql@tisc.de> wrote:
From: Tino Schwarze <postgresql@tisc.de>
Subject: Re: [ADMIN] Best way to limit database sizes
To: pgsql-admin@postgresql.org
Date: Thursday, July 3, 2008, 9:17 PM

On Thu, Jul 03, 2008 at 09:10:47PM +0200, Dimitri Fontaine wrote:

> >I've considered creating a tablespace in a directory owned by the

> >user , so I can use Linux quotas to prevent higher disk usage , but
> >this turned out be a bad thought, as all the files are anyway owned
> >by the postgres user and so disk quotas won't have any effect.
>
> What if you put each tablespace on a LVM partition of a control sized,
> extensible?

This is a desaster waiting to happen. I'd say never let a database get
out of disk space. Rather implement soft limits like regular cron jobs
and make them pay for overusage.

Also note that DB size on disk and amount of data stored in DB might be
a lot different because of bloating issues, indices etc.

Tino..oO(But LVM/tablespace should work.)

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin