From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: pg_dump: Error message from server: ERROR: compressed data is corrupt
Date: Fri, 23 Apr 2010 13:54:42 +0200
Message-ID: <201004231454.43104.achill@matrix.gatewaynet.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
In-Reply-To: <201004231454.43104.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox