From: Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: vacuum process taking more than 33 hours
Date: Mon, 15 Jan 2007 14:44:15 +0000
Message-ID: <86aad3420701150644j5beef392q7007324f3d7008a@mail.gmail.com> (raw)
In-Reply-To: <20070115143247.GA13011@phlogiston.dyndns.org>
References: <2529.5826.qm@web30013.mail.mud.yahoo.com>
<20070115143247.GA13011@phlogiston.dyndns.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ezequiel.pellettieri@gmail.com, ajs@crankycanuck.ca
Subject: Re: vacuum process taking more than 33 hours
In-Reply-To: <86aad3420701150644j5beef392q7007324f3d7008a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox