Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id C1D089FB251 for ; Tue, 14 Nov 2006 12:23:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 40118-07 for ; Tue, 14 Nov 2006 12:23:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from perth.mailnetwork.co.uk (perth.mailnetwork.co.uk [80.175.28.46]) by postgresql.org (Postfix) with ESMTP id B24D69FB232 for ; Tue, 14 Nov 2006 12:23:30 -0400 (AST) Received: from user-5442a090.lns1-c8.dsl.pol.co.uk ([84.66.160.144]:1542 helo=[192.168.1.2]) by perth.mailnetwork.co.uk with esmtpa (Exim 4.63) (envelope-from ) id 1Gk14M-0001ug-24; Tue, 14 Nov 2006 16:23:30 +0000 Message-ID: <4559EDFF.9010107@mailnetwork.co.uk> Date: Tue, 14 Nov 2006 16:25:35 +0000 From: "Andy Shellam (Mailing Lists)" User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: Hans Guijt CC: pgsql-admin@postgresql.org Subject: Re: Finding out free space? References: <000701c707fa$bd3209a0$ab14010a@nlpc17> In-Reply-To: <000701c707fa$bd3209a0$ab14010a@nlpc17> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/141 X-Sequence-Number: 23668 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