Обсуждение: pg_dump: Error message from server: ERROR: compressed data is corrupt
Hello, just coming back from a rescue marathon on this remote server i was telling you. As i said, the last problem was while doing a pg_dump dynacom (dynacom is my db'd name) i kept getting pg_dump: SQL command failed pg_dump: Error message from server: ERROR: compressed data is corrupt pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentno.... so i tried dumping the individual mail_entity table, which resulted in the following errors: pg_dump -t mail_entity > /dev/null pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 2 for toast value 133502683 pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentno, entdate, entrecvdate, entsendername, entsenderaddr,entmess ageid, en; srv:~> From the 76000 rows in that table i found the damaged columns by using the following technique (which requires O(log(n)) selects btw lol!!!) SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/10000= 0; SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/10000= 1; .... until i found the correct subset of 10000 rows SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/10000= 7; then SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/1000= 70; SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/1000= 71; ... and so on untill i found the two (it was two of them) bad rows. i did entdata='' for those two rows, and the selects could be done without issues. however, after that, the pg_dump -t reverted back to the aforementioned error. Now doing this gives: srv:~> pg_dump -t mail_entity > /dev/null pg_dump: SQL command failed pg_dump: Error message from server: ERROR: compressed data is corrupt pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentno, entdate, entrecvdate, entsendername, entsenderaddr,entmessageid, en; srv:~> Then i did # CREATE TABLE mail_entity2 AS SELECT * FROM mail_entity; which went fine but, for some crazy reason, pg_dump on mail_entity2 also results to an error: srv:~> pg_dump -t mail_entity2 > /dev/null pg_dump: SQL command failed pg_dump: Error message from server: ERROR: compressed data is corrupt pg_dump: The command was: COPY public.mail_entity2 (msgno, entno, entparentno, entdate, entrecvdate, entsendername, entsenderaddr,entmessageid, e; srv:~> Is there anything i could do to take this very much wanted dump? -- Achilleas Mantzios
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > Then i did > # CREATE TABLE mail_entity2 AS SELECT * FROM mail_entity; > which went fine > but, for some crazy reason, pg_dump on mail_entity2 also results to an error: > srv:~> pg_dump -t mail_entity2 > /dev/null > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: compressed data is corrupt The create/select would not have bothered to decompress any inline-compressed datums, so the corrupted data went right over to the new table. I think you'll have to engage in another round of divide-and-conquer to locate the specific row or rows with a corrupted value. regards, tom lane
Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
От
Achilleas Mantzios
Дата:
Στις Friday 23 April 2010 17:05:46 ο/η Tom Lane έγραψε: > Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > > Then i did > > > # CREATE TABLE mail_entity2 AS SELECT * FROM mail_entity; > > > which went fine > > > but, for some crazy reason, pg_dump on mail_entity2 also results to an error: > > srv:~> pg_dump -t mail_entity2 > /dev/null > > pg_dump: SQL command failed > > pg_dump: Error message from server: ERROR: compressed data is corrupt > > The create/select would not have bothered to decompress any > inline-compressed datums, so the corrupted data went right over to the > new table. I think you'll have to engage in another round of > divide-and-conquer to locate the specific row or rows with a corrupted > value. We just removed one memory SIM, and the problem went away. It must have been a problem caused by on the fly memory hardware error rather than actual corrupted data on the disk. I have a decent dump now, thank you a lot. > > regards, tom lane > -- Achilleas Mantzios