Thread: Database Size Limiting

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","postgres2" 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

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

My understanding is databases reside within 'database clusters' in
postgresql. Is this right? Can I run multiple database clusters within
one postgresql/postmaster server? Then could I use filesystem quotas for
each cluster?


Thank you!


-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Thursday, May 27, 2004 2:12 PM
To: Campano, Troy
Cc: Postgres general mailing list
Subject: Re: [GENERAL] Database Size Limiting

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

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

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/current/static/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

Is it possible to have a database spread across two data files?
So if it fills up filesystem1 then we could add a second filesystem and
allow it to grow onto this second filesystem?


Thanks!


-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, May 27, 2004 3:23 PM
To: Joshua D. Drake
Cc: Campano, Troy; Postgres general mailing list
Subject: Re: [GENERAL] Database Size Limiting

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/current/static/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

----- 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

Campano, Troy wrote:
> Is it possible to have a database spread across two data files?
> So if it fills up filesystem1 then we could add a second filesystem and
> allow it to grow onto this second filesystem?

If you're using Linux there is a tool called the Linux Volume Manager
(LVM) which does this. You can change volume sizes dynamically, add new
disks etc.

I think all modern operating-systems offer something similar.

--
   Richard Huxton
   Archonet Ltd