Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6C2A6634E46 for ; Mon, 5 Apr 2010 03:30:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 18222-08 for ; Mon, 5 Apr 2010 06:29:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f200.google.com (mail-yw0-f200.google.com [209.85.211.200]) by mail.postgresql.org (Postfix) with ESMTP id 5E150634F49 for ; Mon, 5 Apr 2010 03:06:21 -0300 (ADT) Received: by ywh38 with SMTP id 38so568954ywh.29 for ; Sun, 04 Apr 2010 23:06:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:subject:mime-version :content-type:from:in-reply-to:date:cc:content-transfer-encoding :message-id:references:to:x-mailer; bh=DhtfsAP4MvZlBeu5TMOyYQLE3qFbXloliAMMoBP6Pf4=; b=OEquVQ02+Jv6BCAdkqiA6q60nw6I7qWgzPejHkvP+BoFfCBKBNuz2C/h9RHIfD0X7V 4vzbOzJPJgUCgBPl+Aq6Vl2CKtisB7TM2P0raQlnU7YlEuqTlqzYoLvwnrvjNzAGvPX8 zsuV6XSC/PPT2ZU02trJ5w0zLqV9/PjROsxx0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; b=m9cqZdeyZYlJjNoNYKw1G6+zsjjqHRWodQ2xlGRsmOHwNBqABgpFpwRpWCOMVPjnYj ieZAFq7tXgYcorX01pTRQQtOpQWQRp7YOMJZ51TrTTrP3Yiyj0KDBemSl4OGBVn5AMPd 0Lp5jlZ+M/Vyqlkm9jkrvigNUdAHeicRWYi9U= Received: by 10.100.25.38 with SMTP id 38mr11958653any.97.1270447580074; Sun, 04 Apr 2010 23:06:20 -0700 (PDT) Received: from [192.168.1.4] ([122.170.20.97]) by mx.google.com with ESMTPS id 36sm2856800yxh.32.2010.04.04.23.06.16 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sun, 04 Apr 2010 23:06:19 -0700 (PDT) Subject: Re: Execute VACUUM FULL when DB touches a specific size ? Mime-Version: 1.0 (Apple Message framework v1077) Content-Type: text/plain; charset=utf-8 From: Vibhor Kumar In-Reply-To: <4BB9624D.7010600@itech7.com> Date: Mon, 5 Apr 2010 11:36:10 +0530 Cc: Scott Marlowe , pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <4BB94934.3050709@itech7.com> <4BB9624D.7010600@itech7.com> To: Nilesh Govindarajan X-Mailer: Apple Mail (2.1077) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.203 tagged_above=-10 required=5 tests=BAYES_00=-2.599, MIME_QP_LONG_LINE=1.396 X-Spam-Level: X-Archive-Number: 201004/24 X-Sequence-Number: 34118 > 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. Use pg_database_size function to get the size of DB. You can create a plpgsql function, which you can schedule using pgagent=20= Or=20 If you are writing Bash script capture the return value of = pg_database_size function, in some variable and perform vacuum. Thanks & Regards, Vibhor Kumar (PCP & OCP) ITIL V3 Cerftified. On 05-Apr-2010, at 9:38 AM, Nilesh Govindarajan wrote: > On 04/05/10 08:54, Scott Marlowe wrote: >> On Sun, Apr 4, 2010 at 8:21 PM, Nilesh Govindarajan = wrote: >>> Hi, >>>=20 >>> I wish to execute VACUUM FULL when DB touches a specific size. How = to do it >>> ? >>>=20 >>> I'm aware about the caveats of VACUUM FULL. >>>=20 >>=20 >> 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. >=20 >=20 >=20 > --=20 > Nilesh Govindarajan > Site & Server Administrator > www.itech7.com > =E0=A4=AE=E0=A5=87=E0=A4=B0=E0=A4=BE =E0=A4=AD=E0=A4=BE=E0=A4=B0=E0=A4=A4= =E0=A4=AE=E0=A4=B9=E0=A4=BE=E0=A4=A8 ! > =E0=A4=AE=E0=A4=AE =E0=A4=AD=E0=A4=BE=E0=A4=B0=E0=A4=A4: = =E0=A4=AE=E0=A4=B9=E0=A4=A4=E0=A5=8D=E0=A4=A4=E0=A4=AE =E0=A4=AD=E0=A4=B5=E0= =A4=A4=E0=A5=81 ! >=20 > --=20 > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin