Обсуждение: Database size in Postgresql

Hi ,

I have a question, I am suppose to create database with limited size. Is there any way I can set limit to my database created in postgresql.

As per my surfing through various blogs, I found, its not possible directly.This is my approach please let me know if I am going right:

- Since in postgresql (or may be other also), when a database is created, a path to a directory is given in which all data is stored.
- If I restrict the size of this directory then by database size will be automatically controlled.

please let me know if this is right and is there any more simpler way to reach this approach?

Thanks,
Kunal

---

The i’m Talkathon starts 6/24/08.  For now, give amongst yourselves. Learn More

Kunal Gupta wrote:
> Hi ,
>
> I have a question, I am suppose to create database with limited size. Is
> there any way I can set limit to my database created in postgresql.

Not reasonably no.

> - Since in postgresql (or may be other also), when a database is
> created, a path to a directory is given in which all data is stored.
> - If I restrict the size of this directory then by database size will be
> automatically controlled.

And you might possibly corrupt data when you hit the barrier.

Joshua D. Drake

On Thu, Jun 19, 2008 at 7:18 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Kunal Gupta wrote:
>>
>> Hi ,
>>
>> I have a question, I am suppose to create database with limited size. Is
>> there any way I can set limit to my database created in postgresql.
>
> Not reasonably no.
>
>> - Since in postgresql (or may be other also), when a database is created,
>> a path to a directory is given in which all data is stored.
>> - If I restrict the size of this directory then by database size will be
>> automatically controlled.
>
> And you might possibly corrupt data when you hit the barrier.

You could always regularly poll the database for the size and generate
a notice or email for you to deal with if it's above a certain
threshold.

postgres=> select pg_database_size('test') as dbsize;
  dbsize
-----------
 609975524

Or, if you need to do do this in bulk,

postgres=> select datname as database, pg_database_size(datname) as
size from pg_database order by database;

I'm not sure how quickly the size statistics get updated, maybe only
after a VACUUM, but this will work as a reactive policy. There's not a
whole lot you can do to enforce proactive quotas in postgres, and as
Joshua mentioned, you might run into bad things.

Peter