pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to limit database sizes
8+ messages / 5 participants
[nested] [flat]
* Best way to limit database sizes
@ 2008-07-03 16:07 Dev <dev_hyd2001@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: Dev @ 2008-07-03 16:07 UTC (permalink / raw)
To: pgsql-admin
Hi,
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?
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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-03 16:28 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Dev <dev_hyd2001@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2008-07-03 16:28 UTC (permalink / raw)
To: pgsql-admin; dev_hyd2001@yahoo.com
>>> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-03 17:48 Dev <dev_hyd2001@yahoo.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 8+ messages in thread
From: Dev @ 2008-07-03 17:48 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-03 18:31 Montaseri <montaseri@gmail.com>
parent: Dev <dev_hyd2001@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Montaseri @ 2008-07-03 18:31 UTC (permalink / raw)
To: dev_hyd2001@yahoo.com; +Cc: pgsql-admin
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
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-03 18:31 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Dev <dev_hyd2001@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2008-07-03 18:31 UTC (permalink / raw)
To: pgsql-admin; dev_hyd2001@yahoo.com
>>> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-03 19:10 Dimitri Fontaine <dfontaine@hi-media.com>
parent: Dev <dev_hyd2001@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Dimitri Fontaine @ 2008-07-03 19:10 UTC (permalink / raw)
To: dev_hyd2001@yahoo.com; +Cc: 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)
iEYEARECAAYFAkhtJDcACgkQlBXRlnbh1bkQlACfY825U1pq7Livch4sPSEgj1x6
iCMAoMN9Wjxb3KFOHbOmj2oi5sqnvyUn
=IeBI
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-03 19:17 Tino Schwarze <postgresql@tisc.de>
parent: Dimitri Fontaine <dfontaine@hi-media.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tino Schwarze @ 2008-07-03 19:17 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to limit database sizes
@ 2008-07-04 07:42 Dev <dev_hyd2001@yahoo.com>
parent: Tino Schwarze <postgresql@tisc.de>
0 siblings, 0 replies; 8+ messages in thread
From: Dev @ 2008-07-04 07:42 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-07-04 07:42 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-03 16:07 Best way to limit database sizes Dev <dev_hyd2001@yahoo.com>
2008-07-03 16:28 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 17:48 ` Dev <dev_hyd2001@yahoo.com>
2008-07-03 18:31 ` Montaseri <montaseri@gmail.com>
2008-07-03 18:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 19:10 ` Dimitri Fontaine <dfontaine@hi-media.com>
2008-07-03 19:17 ` Tino Schwarze <postgresql@tisc.de>
2008-07-04 07:42 ` Dev <dev_hyd2001@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox