Обсуждение: Best way to limit database sizes
| 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 |
>>> 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
| 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> |
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
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
-----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-----
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
| 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> |