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? Thank you! On Tue, Aug 6, 2013 at 9:14 PM, Sergey Konoplev <gray...@gmail.com> wrote: > On Tue, Aug 6, 2013 at 4:17 PM, Carlos Henrique Reimer > <carlos.rei...@opendb.com.br> 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.kla...@gmail.com> > > 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.rei...@opendb.com.br > >>> <mailto:carlos.rei...@opendb.com.br> > >> > >> > >> > >> -- > >> Adrian Klaver > >> adrian.kla...@gmail.com > > > > > > > > > > -- > > Reimer > > 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br > > > > -- > 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...@gmail.com > -- Reimer 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
