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

On Mon, Apr 18, 2011 at 1:45 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> On Mon, Apr 18, 2011 at 3:38 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Sun, Apr 17, 2011 at 11:19 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>>> Btw, hardware is not an issue. My db has been working fine for a
>>> while. Smaller poorer systems around the web run InnoDB databases. I
>>> wouldn't touch that with a barge pole.
>>>
>>> I have a hardware RAID controller, not "fake". It's a good quality
>>> battery-backed 3Ware:
>>> http://192.19.193.26/products/serial_ata2-9000.asp
>>
>> (please stop top posting)
>>
>> Also, when you run top and hit c what do those various postgres
>> processes say they're doing?  bgwriter, SELECT, VACUMM etc?
>>
>
>
>
>
>
> Thanks. But let me do the "top" stuff later. I think I have a bigger
> problem now.
>
> While doing a PG dump, I seem to get this error:
>
>    ERROR: invalid memory alloc request size 4294967293
>
> Upon googling, this seems to be a data corruption issue!
>
> One of the older messages suggests that I do "file level backup and
> restore the data" -
> http://archives.postgresql.org/pgsql-admin/2008-05/msg00191.php
>
> How does one do this -- should I copy the data folder? What are the
> specific steps to restore from here, would I simply copy the files
> from the data folder back to the new install or something? Cant find
> these steps in the PG documentation.
>
> I'm on PG 8.2.9, CentOS 5, with 8GB of RAM.

I wonder if you've got a drive going bad (or both of them) what does
your RAID card have to say about the drives?

To do a file level backup, setup another machine on the same network,
with enough space on a drive with write access for the account you
want to backup to. Shut down the Postgres server (sudo
/etc/init.d/postgresql stop or something like that) then use rsync
-avl /data/pgdir remoteserver:/newdatadir/ to back it up. you want to
start with that so you can at least get back to where you are now if
things go wrong.

Also, after that, run memtest86+ to make sure you don't have memory errors.