current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -4 down vote favorite

My question regards PostgreSQL database size.

How can I create new database with fixed size? (e.g. 5 GB)

Once I have created fixed sized PostgreSQL database, how can I resize it ?

share|improve this question
6  
It sounds like you want database quotas. If so, PostgreSQL doesn't support them. You can create the database on a file system limited to the desired size, but MVCC and write-ahead logging will mean it won't work how you expect. The short version is "you can't". –  Craig Ringer May 6 '14 at 4:23
    
@CraigRinger how about putting the (default) tablespace on a restricted-size volume? This, at least, would remove the WAL from the equation. –  dezso May 6 '14 at 7:09
    
Yeah, but the whole cluster will stop until you free some space, and you can't free space without adding more space first. So it's not going to work well. –  Craig Ringer May 6 '14 at 7:18

1 Answer 1

up vote 0 down vote

As we can read in the comments, there is no direct support in postgresql for this - but you can solve this problem with indirect means, as you can read here: http://bytes.com/topic/postgresql/answers/421532-database-size-limiting

Basically, you have various options to make a separate database instance, or a cluster node, or a partition, and then limit the disk usage on the OS level (f.e. user quotas).

AFAIK in case of a quota exhaust your transactions will be rollbacked, but the db left consistent. But I suggest to doublecheck this before you do some dangerous.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.