Re: Finding out free space?

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