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