From mariobehring@yahoo.com Mon Jan  15 14:23:36 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 7E09B9FB302
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:23:36 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 85575-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:23:20 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from web30013.mail.mud.yahoo.com (web30013.mail.mud.yahoo.com
	[209.191.69.30]) by postgresql.org (Postfix) with SMTP id CED169FB258
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2007 10:23:25 -0400 (AST)
Received: (qmail 6704 invoked by uid 60001); 15 Jan 2007 14:23:24 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=j+4YrNPk8xiku7EJfrvzfX8LgvRdeEj1mq/6fzTQA1CuNbpLTpgF92/ZmB2BHqJLr4TO11AYw4rQFiyuOy81iieJI706w7RPoBPG8qgmTXkeOy5tT859YeQa1eJ/nbY8kF8BH9E4zbial0AiVOYn/LpVNvHw55X2doUp2x2664s=;
X-YMail-OSG: 
 Wt8DUycVM1ncvkn91Vv9kz.jCBkleW3KfzC8AsLEwOQbQmSTZk5yRPaYlnbYymhv9OnQsy14jRKM2jadu_CEjqAVZl96nogxH2Z0_wZMceD3bp28_txMrwiaRyrlmMnbzwg9k.nD2Q--
Received: from [200.161.5.135] by web30013.mail.mud.yahoo.com via HTTP;
	Mon, 15 Jan 2007 06:23:24 PST
X-Mailer: YahooMailRC/368.3 YahooMailWebService/0.6.132.7
Date: Mon, 15 Jan 2007 06:23:23 -0800 (PST)
From: Mario Behring <mariobehring@yahoo.com>
Subject: vacuum process taking more than 33 hours
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1589075351-1168871003=:5826"
Message-ID: <2529.5826.qm@web30013.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/46
X-Sequence-Number: 27198

--0-1589075351-1168871003=:5826
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi all,=0A=0AI've executed a VACUUM FULL on a database 33GB in size. The pr=
ocess was going fine until it reached a index (I guess it's an index) and t=
here it stopped for more than 30 hours...........the whole weekend......=0A=
=0AI've canceled it but I desperately need to free some space at the server=
's disk. I was thinking about using the TRUNCATE statement at the table I k=
now to be the largest one. I have some questions though:=0AWill the TRUNCAT=
E free disk space?Can I run this command and let the openNMS software runni=
ng at the same time? The ONMS product will certainly try to execute INCLUDE=
S and such.If the TRUNCATE indeed free disk space, what should I do next, r=
eindex, vacuum ??=0A=0AAlso, what else can I do to avoid this kind of probl=
em? Would a REINDEX fix the problem and let the VACUUM command finish?=0A=
=0APlease advise.=0A=0AThank you.=0A=0ABest regards,=0A=0AMario Behring=0A=
=0A=0A=0A=0A =0A___________________________________________________________=
_________________________=0ATV dinner still cooling? =0ACheck out "Tonight'=
s Picks" on Yahoo! TV.=0Ahttp://tv.yahoo.com/
--0-1589075351-1168871003=:5826
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:arial,helvetica,sans-serif;font-size:10p=
t"><div>Hi all,<br><br>I've executed a VACUUM FULL on a database 33GB in si=
ze. The process was going fine until it reached a index (I guess it's an in=
dex) and there it stopped for more than 30 hours...........the whole weeken=
d......<br><br>I've canceled it but I desperately need to free some space a=
t the server's disk. I was thinking about using the TRUNCATE statement at t=
he table I know to be the largest one. I have some questions though:<br><ul=
><li>Will the TRUNCATE free disk space?</li><li>Can I run this command and =
let the openNMS software running at the same time? The ONMS product will ce=
rtainly try to execute INCLUDES and such.</li><li>If the TRUNCATE indeed fr=
ee disk space, what should I do next, reindex, vacuum ??<br></li></ul><br>A=
lso, what else can I do to avoid this kind of problem? Would a REINDEX fix =
the problem and
 let the VACUUM command finish?<br><br>Please advise.<br><br>Thank you.<br>=
<br>Best regards,<br><br>Mario Behring<br></div></div><br>=0A=0A<hr size=3D=
1>Finding fabulous fares is fun.<br><a href=3D"http://farechase.yahoo.com/p=
romo-generic-14795097;_ylc=3DX3oDMTFtNW45amVpBF9TAzk3NDA3NTg5BF9zAzI3MTk0OD=
EEcG9zAzEEc2VjA21haWx0YWdsaW5lBHNsawNxMS0wNw--=0A">Let Yahoo! FareChase sea=
rch your favorite travel sites</a> to find flight and hotel bargains.</body=
></html>
--0-1589075351-1168871003=:5826--



From ajs@crankycanuck.ca Mon Jan  15 14:33:17 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 4F3EC9FB302
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:33:17 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 71335-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:33:00 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140])
	by postgresql.org (Postfix) with ESMTP id 24C5D9FB221
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2007 10:32:58 -0400 (AST)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]
	helo=phlogiston.dydns.org)
	by main2.mycybernet.net with esmtp (Exim 4.62)
	(envelope-from <ajs@phlogiston.dyndns.org>) id 1H6T2w-00066L-Ma
	for pgsql-sql@postgresql.org; Mon, 15 Jan 2007 09:42:50 -0500
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id D8AD04051; Mon, 15 Jan 2007 09:32:47 -0500 (EST)
Date: Mon, 15 Jan 2007 09:32:47 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: vacuum process taking more than 33 hours
Message-ID: <20070115143247.GA13011@phlogiston.dyndns.org>
References: <2529.5826.qm@web30013.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2529.5826.qm@web30013.mail.mud.yahoo.com>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/47
X-Sequence-Number: 27199

On Mon, Jan 15, 2007 at 06:23:23AM -0800, Mario Behring wrote:
> Hi all,
> 
> I've executed a VACUUM FULL on a database 33GB in size. The process
> was going fine until it reached a index (I guess it's an index) and
> there it stopped for more than 30 hours...........the whole
> weekend......

It may not have been doing anything.  VACUUM FULL needs to take an
exclusive lock on each table it is processing.  It may have been
waiting for that lock.

> I've canceled it but I desperately need to free some space at the
> server's disk. I was thinking about using the TRUNCATE statement at
> the table I know to be the largest one. I have some questions
> though:

TRUNCATE and VACUUM are different beasts.  VACUUM recovers space from
deleted or updated rows.  If you have done neither of those things,
then it won't recover any space.  TRUNCATE is like DELETE on
steriods: it simply removes all the data from your table.  

TRUNCATE will indeed recover disk, although I can't remember whether
it actually returns that disk space to the operating system, or
whether it remains allocated for the table in question by postgres. 
If the latter, a VACUUM FULL on the table in question ought to be
enough to get you the space back.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
		--Philip Greenspun



From ezequiel.pellettieri@gmail.com Mon Jan  15 14:44:51 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 437809FB30F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:44:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 36332-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:44:24 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.224])
	by postgresql.org (Postfix) with ESMTP id 0F1FD9FB221
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2007 10:44:24 -0400 (AST)
Received: by wr-out-0506.google.com with SMTP id i28so1132614wra
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2007 06:44:23 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=nNFOXDgtj93B7x7WKxjOgG7B4fpK/JPDRADnoz4PgNwdRM+3jZf95InaByKrJ7KI+pfq2YWyiS3Q6cZtjMe/DFXsKoKd+TCjGa3+tYzQytJvlgQZGE+XaeXdJiPO29Klw0BO5AK9/0RRJSxaGPSLRU8Pd2XjwOJ1mJfQbw1i/X0=
Received: by 10.78.149.13 with SMTP id w13mr2577904hud.1168872256457;
	Mon, 15 Jan 2007 06:44:16 -0800 (PST)
Received: by 10.78.77.12 with HTTP; Mon, 15 Jan 2007 06:44:15 -0800 (PST)
Message-ID: <86aad3420701150644j5beef392q7007324f3d7008a@mail.gmail.com>
Date: Mon, 15 Jan 2007 14:44:15 +0000
From: "Ezequiel Luis Pellettieri" <ezequiel.pellettieri@gmail.com>
To: "Andrew Sullivan" <ajs@crankycanuck.ca>
Subject: Re: vacuum process taking more than 33 hours
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20070115143247.GA13011@phlogiston.dyndns.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_58636_482927.1168872255605"
References: <2529.5826.qm@web30013.mail.mud.yahoo.com>
	<20070115143247.GA13011@phlogiston.dyndns.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/48
X-Sequence-Number: 27200

------=_Part_58636_482927.1168872255605
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Andrew,

Why don't you try droping all indexes exept but the constraints ones for
each table.
then you'll free aprox half of DB size. then re-create indexes,  one by one.
and finally you can perform a full vacuum.

that worked for me. :)

cheers

pelle.-

2007/1/15, Andrew Sullivan <ajs@crankycanuck.ca>:
>
> On Mon, Jan 15, 2007 at 06:23:23AM -0800, Mario Behring wrote:
> > Hi all,
> >
> > I've executed a VACUUM FULL on a database 33GB in size. The process
> > was going fine until it reached a index (I guess it's an index) and
> > there it stopped for more than 30 hours...........the whole
> > weekend......
>
> It may not have been doing anything.  VACUUM FULL needs to take an
> exclusive lock on each table it is processing.  It may have been
> waiting for that lock.
>
> > I've canceled it but I desperately need to free some space at the
> > server's disk. I was thinking about using the TRUNCATE statement at
> > the table I know to be the largest one. I have some questions
> > though:
>
> TRUNCATE and VACUUM are different beasts.  VACUUM recovers space from
> deleted or updated rows.  If you have done neither of those things,
> then it won't recover any space.  TRUNCATE is like DELETE on
> steriods: it simply removes all the data from your table.
>
> TRUNCATE will indeed recover disk, although I can't remember whether
> it actually returns that disk space to the operating system, or
> whether it remains allocated for the table in question by postgres.
> If the latter, a VACUUM FULL on the table in question ought to be
> enough to get you the space back.
>
> A
>
> --
> Andrew Sullivan  | ajs@crankycanuck.ca
> The fact that technology doesn't work is no bar to success in the
> marketplace.
>                 --Philip Greenspun
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>



-- 
Saludos cordiales. Ezequiel L. Pellettieri

------=_Part_58636_482927.1168872255605
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Andrew, <br><br>Why don&#39;t you try droping all indexes exept but the constraints ones for each table.<br>then you&#39;ll free aprox half of DB size. then re-create indexes,&nbsp; one by one. and finally you can perform a full vacuum.
<br><br>that worked for me. :)<br><br>cheers<br><br>pelle.-<br><br><div><span class="gmail_quote">2007/1/15, Andrew Sullivan &lt;<a href="mailto:ajs@crankycanuck.ca">ajs@crankycanuck.ca</a>&gt;:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On Mon, Jan 15, 2007 at 06:23:23AM -0800, Mario Behring wrote:<br>&gt; Hi all,<br>&gt;<br>&gt; I&#39;ve executed a VACUUM FULL on a database 33GB in size. The process<br>&gt; was going fine until it reached a index (I guess it&#39;s an index) and
<br>&gt; there it stopped for more than 30 hours...........the whole<br>&gt; weekend......<br><br>It may not have been doing anything.&nbsp;&nbsp;VACUUM FULL needs to take an<br>exclusive lock on each table it is processing.&nbsp;&nbsp;It may have been
<br>waiting for that lock.<br><br>&gt; I&#39;ve canceled it but I desperately need to free some space at the<br>&gt; server&#39;s disk. I was thinking about using the TRUNCATE statement at<br>&gt; the table I know to be the largest one. I have some questions
<br>&gt; though:<br><br>TRUNCATE and VACUUM are different beasts.&nbsp;&nbsp;VACUUM recovers space from<br>deleted or updated rows.&nbsp;&nbsp;If you have done neither of those things,<br>then it won&#39;t recover any space.&nbsp;&nbsp;TRUNCATE is like DELETE on
<br>steriods: it simply removes all the data from your table.<br><br>TRUNCATE will indeed recover disk, although I can&#39;t remember whether<br>it actually returns that disk space to the operating system, or<br>whether it remains allocated for the table in question by postgres.
<br>If the latter, a VACUUM FULL on the table in question ought to be<br>enough to get you the space back.<br><br>A<br><br>--<br>Andrew Sullivan&nbsp;&nbsp;| <a href="mailto:ajs@crankycanuck.ca">ajs@crankycanuck.ca</a><br>The fact that technology doesn&#39;t work is no bar to success in the marketplace.
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--Philip Greenspun<br><br>---------------------------(end of broadcast)---------------------------<br>TIP 3: Have you checked our extensive FAQ?<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <a href="http://www.postgresql.org/docs/faq">
http://www.postgresql.org/docs/faq</a><br></blockquote></div><br><br clear="all"><br>-- <br>Saludos cordiales. Ezequiel L. Pellettieri

------=_Part_58636_482927.1168872255605--



From ajs@crankycanuck.ca Mon Jan  15 14:53:55 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 98E259FB32A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:53:55 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 91185-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 15 Jan 2007 10:53:18 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140])
	by postgresql.org (Postfix) with ESMTP id B45D09FB221
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2007 10:53:23 -0400 (AST)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]
	helo=phlogiston.dydns.org)
	by main2.mycybernet.net with esmtp (Exim 4.62)
	(envelope-from <ajs@phlogiston.dyndns.org>) id 1H6TMl-0007kk-3a
	for pgsql-sql@postgresql.org; Mon, 15 Jan 2007 10:03:19 -0500
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id 4B8FA41A2; Mon, 15 Jan 2007 09:53:16 -0500 (EST)
Date: Mon, 15 Jan 2007 09:53:16 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: vacuum process taking more than 33 hours
Message-ID: <20070115145316.GC13011@phlogiston.dyndns.org>
References: <2529.5826.qm@web30013.mail.mud.yahoo.com>
	<20070115143247.GA13011@phlogiston.dyndns.org>
	<86aad3420701150644j5beef392q7007324f3d7008a@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <86aad3420701150644j5beef392q7007324f3d7008a@mail.gmail.com>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/49
X-Sequence-Number: 27201

On Mon, Jan 15, 2007 at 02:44:15PM +0000, Ezequiel Luis Pellettieri wrote:
> Hi Andrew,
> 
> Why don't you try droping all indexes exept but the constraints ones for
> each table.

I don't have the problem, so I don't know whether that will help.

> then you'll free aprox half of DB size. then re-create indexes,  one by one.
> and finally you can perform a full vacuum.
> 
> that worked for me. :)

On some versions of Postgres, it will, but AFAIK the latest versions
don't have the same index bloat problems.  Moreover, if your vacuum
regime is right, you should _never_ have to VACUUM FULL.

A


-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
		--attr. John Maynard Keynes