Experiencing "compressed data is corrupted" error

From: Sar <zengu(at)seznam(dot)cz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Experiencing "compressed data is corrupted" error
Date: 2017-04-18 23:59:59
Message-ID: 5nb.IB{A.1c4YgihrFqi.1OzgX}@seznam.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I have a massive table in my database, about 1.5TB and around 3.5 billion
rows. (that is one partition of massive table). When I try to do backup,
just on this one big table, after a while I get following error: 

pg_dump: Dumping the contents of table "ta_low" failed: PQgetResult()
failed.

pg_dump: Error message from server: ERROR:  compressed data is corrupted

pg_dump: The command was: COPY public.ta_low (foreign_id, name, numbers) TO
stdout;

where foreign_id is bigint, name is varchar(10) and extended storage,
numbers is quite long array of ints, also extended storage obviously. The
table works normally, I guess there will be small number of rows that are
corrupted. 

I tried using approach described here: https://no0p.github.io/postgresql/
2013/04/02/postgres-corruption-resolution.html 

However upon creating the checking function and running the query: select
ctid from ta_low where chk(ta_low); I get another error, despite the
exception handling in the function:

ERROR:  compressed data is corrupted

CONTEXT:  PL/pgSQL function chk(anyelement) while storing call arguments
into local variables

********** Error **********

ERROR: compressed data is corrupted

SQL state: XX000

Context: PL/pgSQL function chk(anyelement) while storing call arguments into
local variables

I want to backup this table and possibly replace the corrupted rows,
although it is not top priority, and if single digits of rows are not
working out of the billions its not a big issue. I'm using Postgresql 9.5.
Any advice on how to find/replace/delete the corrupted rows is appreciated,
or how to backup the table. Thank you. 

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Janes 2017-04-19 01:46:32 Re: streaming replication and archive_status
Previous Message Rj Ewing 2017-04-18 22:18:54 Re: full text search on hstore or json with materialized view?