Re: Incomplete pg_dump operation

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