Re: Incomplete pg_dump operation
От | peter@vfemail.net |
---|---|
Тема | Re: Incomplete pg_dump operation |
Дата | |
Msg-id | 20100209023133.CB26F632D25@mail.postgresql.org обсуждение исходный текст |
Ответ на | Re: Incomplete pg_dump operation (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Incomplete pg_dump operation
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Irvin Guyett"Дата:
Сообщение: How to continue Installation if stalled? and using Joomla?