pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Finding out free space?
3+ messages / 3 participants
[nested] [flat]

* Finding out free space?
@ 2006-11-14 14:39  Hans Guijt <hg@terma.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Hans Guijt @ 2006-11-14 14:39 UTC (permalink / raw)
  To: pgsql-admin

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





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

* Re: Finding out free space?
@ 2006-11-14 16:25  Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
  parent: Hans Guijt <hg@terma.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Andy Shellam (Mailing Lists) @ 2006-11-14 16:25 UTC (permalink / raw)
  To: Hans Guijt <hg@terma.com>; +Cc: pgsql-admin

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




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

* Re: Finding out free space?
@ 2006-11-15 04:04  Robert Treat <xzilla@users.sourceforge.net>
  parent: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Robert Treat @ 2006-11-15 04:04 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>; Hans Guijt <hg@terma.com>

If you're using a recent database (8.1+), you can get this same info by using 
the various built in size functions  
http://www.postgresql.org/docs/8.1/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

You can find out the base postgresql directory doing "show data_directory".

Getting the free space of your relations is a bit trickier, probably 
pgstattuple in contrib is your best bet, though if you have your users 
running autovacuum they shouldnt be carrying too much dead space. 

HTH.

On Tuesday 14 November 2006 11:25, Andy Shellam (Mailing Lists) wrote:
> 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!

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL



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


end of thread, other threads:[~2006-11-15 04:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-14 14:39 Finding out free space? Hans Guijt <hg@terma.com>
2006-11-14 16:25 ` Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
2006-11-15 04:04   ` Robert Treat <xzilla@users.sourceforge.net>

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