Re: Vacuum full progress

Hi,

I thought about this approach but this gave big troubles in the past.
Basically the problem of this is that views and functions will still work on
the old_table_bak and not the new_table.

This can work but all views and functions linked to the old_table must be
recreated. Something that needs to be manually done and as any manual
operation exposed to errors.

Maybe this changed in the new PG releases but it was this way in the past.

Thank you!

On Sun, Sep 5, 2010 at 4:46 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Sun, Sep 5, 2010 at 5:09 AM, Carlos Henrique Reimer
> <carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
> > Hi Alban,
> >
> > The need for the vacuum full is because there were a problem with the
> daily
> > schedulled vacuum analyze and autovacuum regarding the max_fsm_pages. As
> it
> > was underestimated the vacuum process was not able to flag the pages to
> be
> > reused.
> >
> > I've cancelled the vacuum full and will think another approach. Maybe a
> > CLUSTER can do the work. Will start a CLUSTER and see if I can check the
> > progress looking the size of the new table relfilenode. It will probably
> > have less than 102 GB.
>
> fastest way if you can afford downtime is something like;
>
> select * into new_table from old_table order by pkcol;
> alter old_table rename to old_table_bak;
> alter new_table rename to old_table;
>
> --
> To understand recursion, one must first understand recursion.
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br