Skip site navigation (1) Skip section navigation (2)

Re: Incomplete pg_dump operation

From: peter(at)vfemail(dot)net
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Incomplete pg_dump operation
Date: 2010-02-09 02:26:17
Message-ID: 20100209023133.CB26F632D25@mail.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-novice
You are lightyears ahead of me, Mr. Lane.  

Your conceptual framework of identifying the offensive data record and deleting it makes a lot of sense to me.  I am, unfortunately, illiterate about what commands I need to execute to make that happen.  

Every "select * from news limit N" command, for N = 0 to 9999999999, appears to generate no response.  

None of these commands return any message of any kind:

     select * from news limit 1000000000
     select * from news limit 1000
     select * from news limit 100
     select * from news limit 10
     select * from news limit 10111
     select * from news limit 2196359751
     select * from news limit 4000000000
     select * from news limit 300000000
     select * from news limit 30000000
     select * from news limit 9999999999
     select * from news limit 999999999
     select * from news limit 99999999
     select * from news limit 9999999
     select * from news limit 999999
     select * from news limit 99999
     select * from news limit 9999
     select * from news limit 999
     select * from news limit 99
     select * from news limit 9
     select * from news limit 1
     select * from news limit 0

Have I misunderstood what you told me to do?  

I will investigate the pg_filedump command tomorrow morning.  

-------

At 06:04 PM 2/8/2010, Tom Lane wrote:
>peter(at)vfemail(dot)net writes:
>> After su'ing to pgsql, I executed a:
>
>>     pg_dump -t xyz database_name > /usr/local/pgsql/db_backups/database_name-Manual-Xyz-Table-Dump
>
>> command, and this message was returned:
>
>>     pg_dump: ERROR:  could not open relation with OID 2196359751
>>     pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
>>     pg_dump: Error message from server: ERROR:  could not open relation with OID 2196359751
>>     pg_dump: The command was: COPY public.xyz ({various field names}) TO stdout;
>
>> That's the same OID other error messages have complained about.  
>
>Hmm.  AFAICS, a COPY OUT operation should only try to read the given
>table (well, and some system tables, but those have small OIDs).  The
>idea that comes to mind now is that you have a corrupt TOAST pointer
>--- or at least data that the code is taking as a TOAST pointer ---
>in the xyz table.  If that contained 2196359751 in its va_toastrelid
>field, that would produce the observed symptoms.
>
>If this theory is correct then there is one damaged row, or maybe
>even just one damaged field in a row, somewhere in xyz.  (Or maybe
>there is more damage after the first one, but anyway it's probably
>somewhat localized.)  If you can delete the damaged row(s) then you
>should be able to dump the rest of the data, which will be a big
>step forward from where you are now.
>
>You should be able to home in on the location of the damaged row by
>doing "select * from xyz limit N" for various N and seeing what's
>the largest N that doesn't fail.  Then "select ctid from xyz
>offset N limit 1" should give you the ctid of the damaged row
>--- confirm by seeing that "select * from xyz where ctid = 'whatever'"
>does fail.
>
>After that, you could try "delete from xyz where ctid = 'whatever'" but
>I expect that this will fail just like selecting it does.  What you'll
>probably have to do is stop the postmaster and manually zero the block
>containing the row with dd or similar tool.  If you search the postgres
>archives for previous discussions of recovering from corrupted data, you
>should find lots of details about this type of process.  It doesn't
>come up often enough for anyone to have tried to automate it though.
>
>It might also be interesting to get a dump of the damaged block
>(see pg_filedump) so we can try to get an idea of exactly what
>happened.
>
>                        regards, tom lane



In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-02-09 03:11:22
Subject: Re: Incomplete pg_dump operation
Previous:From: Irvin GuyettDate: 2010-02-09 02:10:57
Subject: How to continue Installation if stalled? and using Joomla?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group