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 ?

  • 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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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