I'm running a vacuum full on a table due to excessive updates/deletes. It's
been running for more than an hour (it's about 3Gig in size)

2 questions:

1) is there any way to gain some visibility per the progress of the vacuum
full

2) can I safely kill the vacuum full and do a dump, drop table, restore
instead?



--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Kempter <kevink [at] consistentstate.com> wrote:

> 1) is there any way to gain some visibility per the progress of the
> vacuum full

None that I know of, short of attaching to the backend process with a
debugger and poking at its guts.

> 2) can I safely kill the vacuum full and do a dump, drop table,
> restore instead?

Killing a VACUUM FULL can leave the table or its indexes more bloated
than when you started, but it should have no other negative impact. I
have generally had to kill my attempts to VACUUM FULL and resort to
other techniques to deal with extreme bloat. If you have room for an
extra copy of the table on disk, CLUSTER is usually a fast, easy way
to rewrite the table without bloat.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Kevin Grittner" <Kevin.Grittner [at] wicourts.gov> wrote:

> Killing a VACUUM FULL

To be clear, cancel the query on the backend, don't kill the process.
Some versions of PostgreSQL can corrupt data if a backend process is
killed.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Grittner wrote:
> Kevin Kempter <kevink [at] consistentstate.com> wrote:

> > 2) can I safely kill the vacuum full and do a dump, drop table,
> > restore instead?
>
> Killing a VACUUM FULL can leave the table or its indexes more bloated
> than when you started, but it should have no other negative impact. I
> have generally had to kill my attempts to VACUUM FULL and resort to
> other techniques to deal with extreme bloat.

Note that cancelling a VACUUM FULL is known to cause a database restart
(a.k.a. PANIC) if the circumstances are right, viz. that the initial
transaction committed. This is an unfixable bug (short of rewriting
vacuum full from scratch) and has been reported in the wild several
times.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin