Re: Incomplete pg_dump operation - Mailing list 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@vfemail.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