Hi,
Could finally fix it. Used the binary search approach to identify the wrong tuples and removed them by ctid, 9 rows were removed and all of them belonged to the same block. I believe it is not easy to identify the root cause for the corruption but does any one have some directions I could follow to identify the root cause in order to prevent it to happen again?
On Tue, Aug 6, 2013 at 9:14 PM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote:
On Tue, Aug 6, 2013 at 4:17 PM, Carlos Henrique Reimer
<carlos.reimer@xxxxxxxxxxxxx> wrote:
> I have tried to drop the index and the reindex procedure but both fail with
> the same exit code.
>
> Copied the data directory to another partition on same HD but same results.
>
> Next change window will install PG 8.2.23 in another Windows box and copy
> the data directory to the new box.
>
> Hope the error will not be propagated to the new box.
If it wont help try to find out which rows lead to the failure, and
copy your data from this table to a new one with the same structure
filtering this rows. Then drop the old one and rename the new one. You
might also need to drop all the FKs preliminary before doing this and
restore them after.
To find out which rows are bad use manual binary search
(http://en.wikipedia.org/wiki/Binary_search_algorithm) by PK.
To copy data use CREATE TABLE newone (LIKE ...) and then INSERT INTO
newone SELECT ... WHERE id NOT IN (...).
>
>
> Reimer
>
>
> On Mon, Aug 5, 2013 at 10:42 AM, Adrian Klaver <adrian.klaver@xxxxxxxxx>
> wrote:
>>
>> On 08/05/2013 06:24 AM, Carlos Henrique Reimer wrote:
>>>
>>> Hi,
>>>
>>> Yes, I agree with you that it must be upgraded to a supported version
>>> but as the developer has not homologated the system to some new PG
>>> versions yet I need to find out some way to fix it with 8.2.
>>>
>>> Will try to install PG in another windows box, copying the data
>>> directories over the network and see if I can at least take a pg_dump
>>> from the database as it is currently not possible.
>>>
>>> Another possibility is to copy the data directory from the windows box
>>> to a linux with PG 8.2 and start the database there, does this approach
>>> has any possibility of success?
>>
>>
>> No. The files are not binary compatible across OS and architectures.
>>
>> You mentioned that creating indexes on this table fails.
>>
>> Have you tried reindexing or dropping the index to see if that helps?
>>
>>
>>>
>>>
>>> Thank you!
>>>
>>>
>>>
>>> Reimer
>>> 47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx
>>> <mailto:carlos.reimer@xxxxxxxxxxxxx>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@xxxxxxxxx
>
>
>
>
> --
> Reimer
> 47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@xxxxxxxxx
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx
