pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
To: Hans Guijt <hg@terma.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Finding out free space?
Date: Tue, 14 Nov 2006 16:25:35 +0000
Message-ID: <4559EDFF.9010107@mailnetwork.co.uk> (raw)
In-Reply-To: <000701c707fa$bd3209a0$ab14010a@nlpc17>
References: <000701c707fa$bd3209a0$ab14010a@nlpc17>

I'm not too sure where the default Postgres data directory is, but to 
find out how much free space a database is using, you need to know it's 
OID.  Run the query:

SELECT OID, Datname FROM pg_database;

to get a list of database names and their corresponding OIDs.  Then you 
can do (standard Unix command)

du -sh $PGDIR/$OID

(where $PGDIR is the data directory root for PGSQL, and $OID is the oid 
of the database).  eg. for database postgres on my system (note 
/endeavour/dbstore is my PG data root):

[root@sydney /]# echo "SELECT OID, Datname FROM 
pg_database;"|/usr/local/pgsql/bin/psql -d postgres -U postgresql

  oid  |   datname
-------+-------------
 10793 | postgres
........

[root@sydney /]# du -sh /endeavour/dbstore/base/10793
3.6M    /endeavour/dbstore/base/10793

To get the amount of freespace used in PGSQL as a whole, use "du -sh 
$PGDIR" (replace $PGDIR with your PG data root.)

Hope this gets you started.  To find your cluster root, you could do a 
"find / -name PG_VERSION", as these files will only be under your data 
root - this highest-level one returned will be your data root.

Andy.

Hans Guijt wrote:
> I need to provide an alarm function that will alert my customer when 
> the amount of free space in a database has reached some critical 
> threshold. This problem is not entirely trivial, since free space can 
> be located both in the file system and in the database itself.
>
> I'm not looking for a particularly accurate function (and suspect that 
> one is probably not feasible anyway) - anything that comes to within a 
> gigabyte of the actual value is fine with me. Basically I need to 
> figure out two things:
>
> 1. Where does Postgres stores its datafiles? This will allow me to 
> figure out how much space is still available on the filing system.
>
> 2. How much space is unused within the data files themselves?
>
> I'd appreciate a pointer on where I should look for this sort of 
> information.
>
>
> Regards,
>
> Hans Guijt
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
> !DSPAM:37,4559da5e40419901994390!
>
>


-- 
Andy Shellam
NetServe Support Team

the Mail Network
"an alternative in a standardised world"

p: +44 (0) 121 288 0832/0839
m: +44 (0) 7818 000834




view thread (3+ messages)  latest in thread

Message-ID: <4559EDFF.9010107@mailnetwork.co.uk>
Permalink:  ../4559EDFF.9010107@mailnetwork.co.uk/
Also on:    postgresql.org/message-id/4559EDFF.9010107@mailnetwork.co.uk

 · 

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: andy.shellam-lists@mailnetwork.co.uk, hg@terma.com
  Subject: Re: Finding out free space?
  In-Reply-To: <4559EDFF.9010107@mailnetwork.co.uk>
  References: <000701c707fa$bd3209a0$ab14010a@nlpc17> <4559EDFF.9010107@mailnetwork.co.uk>

* 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