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
