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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
