Обсуждение: vacuum full questions
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?
Kevin Kempter <kevink@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
"Kevin Grittner" <Kevin.Grittner@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
Kevin Grittner wrote: > Kevin Kempter <kevink@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.