pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Database size
14+ messages / 10 participants
[nested] [flat]

* Database size
@ 2002-07-17 13:01 Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
  2002-07-17 15:42 ` Re: Database size Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 14+ messages in thread

From: Elielson Fontanezi @ 2002-07-17 13:01 UTC (permalink / raw)
  To: pgsql-admin

Hello folks!

	Is there any way to know the database size of a postgres 7.2 db?
	
	Thanks!

Elielson,



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2002-07-17 13:01 Database size Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
@ 2002-07-17 15:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 14+ messages in thread

From: Bruce Momjian @ 2002-07-17 15:42 UTC (permalink / raw)
  To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: pgsql-admin

Elielson Fontanezi wrote:
> Hello folks!
> 
> 	Is there any way to know the database size of a postgres 7.2 db?

Yes, use contrib/oid2name and 'du', or contrib/dblink.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* database size
@ 2003-05-28 07:54 Anagha Joshi <ajoshi@nulinkinc.com>
  2003-05-28 12:11 ` Re: database size Daniel Rubio <drubior@tinet.org>
  0 siblings, 1 reply; 14+ messages in thread

From: Anagha Joshi @ 2003-05-28 07:54 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org <'pgsql-admin@postgresql.org'>

Hi,
How can I know size of the specific database in Postgres?
 
Thx. in advance.
-Anagha

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: database size
  2003-05-28 07:54 database size Anagha Joshi <ajoshi@nulinkinc.com>
@ 2003-05-28 12:11 ` Daniel Rubio <drubior@tinet.org>
  0 siblings, 0 replies; 14+ messages in thread

From: Daniel Rubio @ 2003-05-28 12:11 UTC (permalink / raw)
  To: Anagha Joshi <ajoshi@nulinkinc.com>; +Cc: pgsql-admin

In the contrib directory exists the database_size function (dbsize 
directory, i think) ...

And if you want to automate the process of retrieving all your databases 
size

http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=15493

Anagha Joshi wrote:
> Hi,
> 
> How can I know size of the specific database in Postgres?
> 
>  
> 
> Thx. in advance.
> 
> -Anagha
> 


-- 
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio@oasi.org
********************************************************




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Database size
@ 2004-09-12 05:56 Andrew Janian <ajanian@scottrade.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Andrew Janian @ 2004-09-12 05:56 UTC (permalink / raw)
  To: psql-admin@postgresql.org

I have recently inherited a Postgres 7.2 DB.  I noticed that the size of the database on the disk is about 100 times the size of the nightly dumps that get done.  I looked into it and realized that after millions of rows are deleted each month there was no vacuum full performed on the DB.  I also noticed that the disk space was not recovered to the OS.  I assumed that was because there was no vacuum full being performed.  I am currently performing a vacuum full and noticing that no space is being recovered.  Is it normal for a DB to be that much bigger than it's dumps?

Thanks,

Andrew Janian




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Database size
@ 2006-11-06 20:48 Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 20:59 ` Re: Database size Chris Hoover <revoohc@gmail.com>
  2006-11-06 20:59 ` Re: Database size Richard Broersma Jr <rabroersma@yahoo.com>
  2006-11-06 21:01 ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
  0 siblings, 3 replies; 14+ messages in thread

From: Nikola Radakovic @ 2006-11-06 20:48 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
@ 2006-11-06 20:59 ` Chris Hoover <revoohc@gmail.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Chris Hoover @ 2006-11-06 20:59 UTC (permalink / raw)
  To: Nikola Radakovic <achillea@hi.t-com.hr>; +Cc: pgsql-admin

select pg_database_size('<dbname>')/1024/1024;

http://www.postgresql.org/docs/8.1/interactive/functions-admin.html

On 11/6/06, Nikola Radakovic <achillea@hi.t-com.hr> 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
>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
@ 2006-11-06 20:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Richard Broersma Jr @ 2006-11-06 20:59 UTC (permalink / raw)
  To: Nikola Radakovic <achillea@hi.t-com.hr>; pgsql-admin

> I would be very grateful if someone could answer me, 
> where to find instructions how to set the size ( in megabytes ) for
> particular database.

I don't understand the question, are you looking to limit the size of your database?

Regards,

Richard Broersma Jr.




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
@ 2006-11-06 21:01 ` Mike Goldner <mgoldner@agmednet.com>
  2006-11-06 22:29   ` Re: Database size Nikola Radakovic <achillea@hi.t-com.hr>
  2 siblings, 1 reply; 14+ messages in thread

From: Mike Goldner @ 2006-11-06 21:01 UTC (permalink / raw)
  To: Nikola Radakovic <achillea@hi.t-com.hr>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 21:01 ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
@ 2006-11-06 22:29   ` Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 22:44     ` Re: Database size Scott Marlowe <smarlowe@g2switchworks.com>
  2006-11-06 22:44     ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
  0 siblings, 2 replies; 14+ messages in thread

From: Nikola Radakovic @ 2006-11-06 22:29 UTC (permalink / raw)
  To: pgsql-admin

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
> 




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 21:01 ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
  2006-11-06 22:29   ` Re: Database size Nikola Radakovic <achillea@hi.t-com.hr>
@ 2006-11-06 22:44     ` Scott Marlowe <smarlowe@g2switchworks.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Scott Marlowe @ 2006-11-06 22:44 UTC (permalink / raw)
  To: Nikola Radakovic <achillea@hi.t-com.hr>; +Cc: pgsql-admin

On Mon, 2006-11-06 at 16:29, 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.

PostgreSQL itself has no ability to set quotas.

You can limit the amount of data a particular table space uses, either
by drive size or quota.  But for individual users, I don't think there's
a solution.



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 21:01 ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
  2006-11-06 22:29   ` Re: Database size Nikola Radakovic <achillea@hi.t-com.hr>
@ 2006-11-06 22:44     ` Mike Goldner <mgoldner@agmednet.com>
  2006-11-06 23:00       ` Re: Database size Richard Broersma Jr <rabroersma@yahoo.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Mike Goldner @ 2006-11-06 22:44 UTC (permalink / raw)
  To: Nikola Radakovic <achillea@hi.t-com.hr>; +Cc: pgsql-admin

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)



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
  2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 21:01 ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
  2006-11-06 22:29   ` Re: Database size Nikola Radakovic <achillea@hi.t-com.hr>
  2006-11-06 22:44     ` Re: Database size Mike Goldner <mgoldner@agmednet.com>
@ 2006-11-06 23:00       ` Richard Broersma Jr <rabroersma@yahoo.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Richard Broersma Jr @ 2006-11-06 23:00 UTC (permalink / raw)
  To: Mike Goldner <mgoldner@agmednet.com>; Nikola Radakovic <achillea@hi.t-com.hr>; +Cc: pgsql-admin

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

These links supplement the above comments:
http://www.postgresql.org/docs/8.1/interactive/disk-full.html
http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html

Regards,

Richard Broersma Jr.



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Database size
@ 2006-11-06 21:49 Richard Broersma Jr <rabroersma@yahoo.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Richard Broersma Jr @ 2006-11-06 21:49 UTC (permalink / raw)
  To: Nikola Radakovic <achillea@hi.t-com.hr>; +Cc: pgsql-admin

> I appreciate your help, You don't understand me because my English is
> awful.
> I would try to paraphrase the sentence-maybe I succeed to correctly
> express myself.I want to limit database space  for each database user on
> my system.For example, One user can hold one or more databases, but I
> want to limit him in order that he isn't able to store data which will
> exceed some limit(in megabytes) which I'll set.
> Something like in webhosting, where each user has limited database space
> on usage.

I see what you are trying to achieve. Also, dont forget to reply-all when you reply to email from
the postgresql lists so that everyone on the lists are copied with your emails. This way everyone
on the list can participate in this discussion.  This is important since, I can not think of an
answer for your question. :o)

Regards,

Richard Broersma Jr.



^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2006-11-06 23:00 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-17 13:01 Database size Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2002-07-17 15:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-05-28 07:54 database size Anagha Joshi <ajoshi@nulinkinc.com>
2003-05-28 12:11 ` Re: database size Daniel Rubio <drubior@tinet.org>
2004-09-12 05:56 Database size Andrew Janian <ajanian@scottrade.com>
2006-11-06 20:48 Database size Nikola Radakovic <achillea@hi.t-com.hr>
2006-11-06 20:59 ` Chris Hoover <revoohc@gmail.com>
2006-11-06 20:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-06 21:01 ` Mike Goldner <mgoldner@agmednet.com>
2006-11-06 22:29   ` Nikola Radakovic <achillea@hi.t-com.hr>
2006-11-06 22:44     ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-11-06 22:44     ` Mike Goldner <mgoldner@agmednet.com>
2006-11-06 23:00       ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-06 21:49 Re: Database size Richard Broersma Jr <rabroersma@yahoo.com>

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