Re: REINDEX takes half a day (and still not complete!)

On Sat, Apr 30, 2011 at 4:07 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 04/23/2011 03:44 PM, Robert Haas wrote:
>>
>> On Apr 17, 2011, at 11:30 AM, Phoenix Kiula<phoenix(dot)kiula(at)gmail(dot)com>
>>  wrote:
>>
>>>
>>> Postgres is 8.2.9.
>>>
>>>
>>
>> An upgrade would probably help you a lot, and as others have said it
>> sounds like your hardware is failing, so you probably want to deal with that
>> first.
>>
>> I am a bit surprised, however, that no one seems to have mentioned using
>> CLUSTER rather than VACUUM or REINDEX. Sometimes that's worth a try...
>>
>
> Don't know if it was for this reason or not for not mentioning it by others,
> but CLUSTER isn't so great in 8.2.  The whole "not MVCC-safe" bit does not
> inspire confidence on a production server.

To everyone. Thanks so much for everything, truly. We have managed to
salvage the data by exporting it in bits and pieces.

1. First the schema only
2. Then pg_dump of specific small tables
3. Then pg_dump of timed bits of the big mammoth table

Not to jinx it, but the newer hardware seems to be doing well. I am on
9.0.4 now and it's pretty fast.

Also, as has been mentioned in this thread and other discussions on
the list, just doing a dump and then fresh reload has compacted the DB
to nearly 1/3rd of its previously reported size!

I suppose that's what I am going to do on a periodic basis from now
on. There is a lot of DELETE/UPDATE activity. But I wonder if the
vacuum stuff really should do something that's similar in function?
What do the high-end enterprise folks do -- surely they can't be
dumping/restoring every quarter or so....or are they?

Anyway, many many thanks to the lovely folks on this list. Much appreciated!