pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mike Goldner <mgoldner@agmednet.com>
To: Nikola Radakovic <achillea@hi.t-com.hr>
Cc: pgsql-admin@postgresql.org
Subject: Re: Database size
Date: Mon, 06 Nov 2006 17:44:56 -0500
Message-ID: <1162853096.5202.50.camel@localhost.localdomain> (raw)
In-Reply-To: <1162852178.18320.9.camel@localhost.localdomain>
References: <1162846082.16473.5.camel@localhost.localdomain>
	<1162846879.5202.34.camel@localhost.localdomain>
	<1162849299.16935.16.camel@localhost.localdomain>
	<1162849972.5202.37.camel@localhost.localdomain>
	<1162852178.18320.9.camel@localhost.localdomain>

On Mon, 2006-11-06 at 16:29 -0600, Nikola Radakovic wrote:
> Setting user's quota on my linux machine wont affect database
> quota.It will have impact on users directories.So, it must
> be a trick how to set PostgreSQL quota. Or how to link PostgreSQL
> database with directories which already have set quotas.
> 
> 
> 
> 
> On Mon, 2006-11-06 at 16:52 -0500, Mike Goldner wrote:
> > On Mon, 2006-11-06 at 15:41 -0600, Nikola Radakovic wrote:
> > > Or, briefly how to set Postgresql user quota?
> > > 
> > > On Mon, 2006-11-06 at 16:01 -0500, Mike Goldner wrote:
> > > > Nikola,
> > > > 
> > > > I'm not exactly sure what you are asking.  
> > > > 
> > > > The size of the database is usually determined by the amount of data
> > > > stored and not determined beforehand.  
> > > > 
> > > > Are you refering of "tablespaces", as other DBMSs use them?  If so,
> > > > Postgres has a tablespace command, but it simply points to a filesystem
> > > > location.  There is no "size" specified.
> > > > 
> > > > The size of the database is simply limited by the available filesystem
> > > > capacity (I suppose there may be some restrictions at the high-end).
> > > > 
> > > > Mike
> > > > 
> > > > On Mon, 2006-11-06 at 14:48 -0600, Nikola Radakovic wrote:
> > > > > Dear community,
> > > > > 
> > > > > I would be very grateful if someone could answer me, 
> > > > > where to find instructions how to set the size ( in megabytes ) for
> > > > > particular database.
> > > > > 
> > > > > 
> > > > > best regards,
> > > > > N.Radakovic
> > > > > 
> > > > > 
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 1: if posting/reading through Usenet, please send an appropriate
> > > > >        subscribe-nomail command to majordomo@postgresql.org so that your
> > > > >        message can get through to the mailing list cleanly
> > > 
> > 
> > Nikola,
> > 
> > Setting quotas is operating system-dependent.  If you are using linux,
> > take a look at http://yolinux.com/TUTORIALS/LinuxTutorialQuotas.html
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org


Assuming that you are running Postgres on a linux system, you could do
the following:

1) Create a partition with quotas enabled (via standard linux OS
procedures).  The postgres user is the user for which quotas should be
enforced since postgresql always runs under the postgres uid.

2) Create a postgres tablespace using the newly created partition: 

psql> CREATE TABLESPACE dbspace LOCATION '/data/dbs';

3) Create the database on that tablespace:

psql> CREATE DATABASE sales OWNER salesapp TABLESPACE dbspace;

I have not tried this myself.

As a side note, I'd say that limiting the size of a database to an
arbitrary amount is problematic.  Should a user actually exhaust the
disk quota, Postgres will be unable to commit the transaction.
Furthermore, it is difficult to recover from such a situation without
major effort.

-- 
Mike Goldner
Vice President Networks and Technology
AG Mednet, Inc.
The Pilot House
Lewis Wharf
Boston, MA  02110
617.854.3225 (office)
617.909.3009 (mobile)



view thread (14+ messages)  latest in thread

Message-ID: <1162853096.5202.50.camel@localhost.localdomain>
Permalink:  ../1162853096.5202.50.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1162853096.5202.50.camel@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mgoldner@agmednet.com, achillea@hi.t-com.hr
  Subject: Re: Database size
  In-Reply-To: <1162853096.5202.50.camel@localhost.localdomain>
  References: <1162846082.16473.5.camel@localhost.localdomain> <1162846879.5202.34.camel@localhost.localdomain> <1162849299.16935.16.camel@localhost.localdomain> <1162849972.5202.37.camel@localhost.localdomain> <1162852178.18320.9.camel@localhost.localdomain> <1162853096.5202.50.camel@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox