Database Size Limiting

[Campano, Troy]

Is there a way to limit the size that an individual database can take
up?

My understanding is that all your databases go into a file system which
is your 'catalog cluster'.

But all the databases can grow until that filesystem is full.

Is there a way to limit how big a database can be? Can you allocated
space when the database is set up so you can say db1 can only be 100 MB?



Thank you!

Troy Campano

[Richard Huxton]

Campano, Troy wrote:

Is there a way to limit the size that an individual database can take
up?

My understanding is that all your databases go into a file system which
is your 'catalog cluster'.

But all the databases can grow until that filesystem is full.

Is there a way to limit how big a database can be? Can you allocated
space when the database is set up so you can say db1 can only be 100 MB?

Well, you've got two main options:
1. Run multiple instances of PG
If you run each as its own user "postgres1","po stgres2" etc then you can
use the standard user permissions to control resource usage.

2. Place each database on its own volume
Mount one volume per database, move and symlink that database to the
volume. If you volume is only 100MB then the database will stop there.

Of course, what you probably want to do is monitor each folder (tip -
the folder names in .../data/base are the OIDs of databases) and warn at
75MB and disallow access if usage is 125MB for more than 24 hours.
That's something outside of PG's control, but someone will have done it.

Oh - final option would be one of the virtual server setups which let
you allocate resource limits per vserver.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Joshua D. Drake]

Hello,

You could use Filsystem quotas but I strongly suggest against it as it
will probably bring about database corruption.

A better solution would be to monitor the space being allocated so you
can add storage as required.

Sincerely,

Joshua D. Drake
Campano, Troy wrote:

-->

Is there a way to limit the size that an individual database can take up?

My understanding is that all your databases go into a file system which
is your ‘catalog cluster’.

But all the databases can grow until that filesystem is full.

Is there a way to limit how big a database can be? Can you allocated
space when the database is set up so you can say db1 can only be 100 MB?



Thank you!

*Troy Campano*

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

Joshua D. Drake wrote:

Hello,

You could use Filsystem quotas but I strongly suggest against it as it
will probably bring about database corruption.

I thought we handled that? I'm not saying it'd be pleasant, but I think
it's no worse than a power-outage.

Hmm, can't find anything in the manuals to back that up. I can find
postings to the mailing lists though, and no-one is complaining PG ate
their data.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

Joshua D. Drake wrote:

Hello,

You could use Filsystem quotas but I strongly suggest against it as it
will probably bring about database corruption.

Ah - manual reference
http://www.postgresql.org/docs/curre...disk-full.html

"24.2. Disk Full Failure

The most important disk monitoring task of a database administrator is
to make sure the disk doesn't grow full. A filled data disk may result
in subsequent corruption of database indexes, but not of the tables
themselves. If the WAL files are on the same disk (as is the case for a
default configuration) then a filled disk during database initialization
may result in corrupted or incomplete WAL files. This failure condition
is detected and the database server will refuse to start up. "

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ben-Nes Michael]

Database Size LimitingI wonder if this will work:

each DB should assigned a diffrent user with set of premissions ( includinginsert ).

a script that can revoke the inseret privilage will emarge from time to time to check the DB size.

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: [phone]
Fax: [phone]
http://www.canaan.net.il
--------------------------

----- Original Message -----
From: Campano, Troy
To: Postgres general mailing list
Sent: Thursday, May 27, 2004 8:07 PM
Subject: [GENERAL] Database Size Limiting
Is there a way to limit the size that an individual database can take up?

My understanding is that all your databases go into a file system which is your 'catalog cluster'.

But all the databases can grow until that filesystem is full.

Is there a way to limit how big a database can be? Can you allocated space when the database is set up so you can say db1 can only be 100 MB?

Thank you!

Troy Campano