Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id F3F24632209
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  5 Apr 2010 03:35:00 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 50171-01-10
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  5 Apr 2010 06:34:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74])
	by mail.postgresql.org (Postfix) with ESMTP id 782AE634D9A
	for <pgsql-admin@postgresql.org>; Mon,  5 Apr 2010 03:29:20 -0300 (ADT)
Received: from [192.168.10.3]
	(ASte-Genev-Bois-153-1-79-201.w86-203.abo.wanadoo.fr
	[86.203.225.201])
	by sd-17937.dedibox.fr (Postfix) with ESMTPA id 534953B8F5;
	Mon,  5 Apr 2010 08:29:19 +0200 (CEST)
Message-ID: <4BB9833B.3080005@lelarge.info>
Date: Mon, 05 Apr 2010 08:29:15 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: Nilesh Govindarajan <lists@itech7.com>
CC: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: Execute VACUUM FULL when DB touches a specific size ?
References: <4BB94934.3050709@itech7.com>
	<j2idcc563d11004042024w10c18f0etb2738c62290a663b@mail.gmail.com>
	<4BB9624D.7010600@itech7.com>
In-Reply-To: <4BB9624D.7010600@itech7.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.185 tagged_above=-10 required=5
	tests=BAYES_40=-0.185
X-Spam-Level: 
X-Archive-Number: 201004/25
X-Sequence-Number: 34119

Le 05/04/2010 06:08, Nilesh Govindarajan a écrit :
> On 04/05/10 08:54, Scott Marlowe wrote:
>> On Sun, Apr 4, 2010 at 8:21 PM, Nilesh Govindarajan<lists@itech7.com> 
>> wrote:
>>> Hi,
>>>
>>> I wish to execute VACUUM FULL when DB touches a specific size. How to
>>> do it
>>> ?
>>>
>>> I'm aware about the caveats of VACUUM FULL.
>>>
>>
>> Write a bash script that cds to the data dir and runs du -s, grabs the
>> value, and if it's over a certain size then run vacuum full.
> 
> That's what I was thinking to do, but how do I distinguish between the
> databases ? The datadir/base seems have directories whose names are
> numbers.
> 

You can find the number with a simple query (SELECT oid FROM pg_database
WHERE datname='your database name') or with the oid2name contrib module.

But that's not the real issue. If you have objects of this database in
other tablespaces, "du -sh" on datadir/base/<oid> won't count them. You
should better use this query:

  SELECT pg_database_size('your database name')

to get the total size of your database.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com