Thread: Block Corruption issue..

Hi,

On 05/07/15 05:49, Sachin Srivastava wrote:
> Dear Concner,
>
> Could you help us solving  the below error which we are getting during
> taking pg_dump.
>
> pg_dump: SQL command failed
>
> pg_dump: Error message from server: ERROR:  invalid page header in block
> 14521215 of relation pg_tblspc/18140340/PG_9.1_201105231/18140346/18140757
>
> pg_dump: The command was: COPY evidence.partline (feature_id, version,
> base_type_id, domain_class_id, domain_subclass_id, status_id, deleted,
> modification_time, has_children, is_evidence, data_immediate, data_lazy,
> parent_line_id, start_point_id, end_point_id, start_offset, end_offset,
> geometry) TO stdout;

Well, it seems the data file got corrupted, for some reason. There's a
number of questions that need to be answered first:

1) ISTM you're running PostgreSQL 9.1 - correct? Which version exactly?
    What operating system?

2) Which relation uses that particular filenode? Look for pg_class rows
    with relfilenode 18140757.

3) Can you reconstruct the relation from other sources?

4) How did the corruption happen? Did the machine crash in the past, or
    something like that?

5) How many cases of corruption are there? Is that a single corrupted
    block, or are there more? I'd assume this is not the only corrupted
    block, and other blocks may be corrupted in different ways.

    You may use for example pageinspect to check that - for example
    use page_header / heap_page_items to walk the pages, and take note
    of pages that fail. It's not 100% solution, but it might give you a
    better idea of how much corruption you're facing.

    The easiest way to get rid of the error is to zero the page(s) that
    cause the pg_dump errors. That page will then be considered empty,
    so you'll loose that data (and you'll have to reconstruct that
    somehow - from a previous backup. for example).

>
> Regards,
>
> Sachin Srivastava
>
>
> ------------------------------------------------------------------------
>
> DISCLAIMER:
>
> This email may contain confidential information and is intended only for
> the use of the specific individual(s) to which it is addressed. If you
> are not the intended recipient of this email, you are hereby notified
> that any unauthorized use, dissemination or copying of this email or the
> information contained in it or attached to it is strictly prohibited. If
> you received this message in error, please immediately notify the sender
> at Cyient and delete the original message.

Sure ...

--
Tomas Vondra                   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Sachin Srivastava wrote:
> Could you help us solving  the below error which we are getting during taking pg_dump.
> 
> pg_dump: SQL command failed
> 
> pg_dump: Error message from server: ERROR:  invalid page header in block 14521215 of relation
> pg_tblspc/18140340/PG_9.1_201105231/18140346/18140757
> 
> pg_dump: The command was: COPY evidence.partline (feature_id, version, base_type_id, domain_class_id,
> domain_subclass_id, status_id, deleted, modification_time, has_children, is_evidence, data_immediate,
> data_lazy, parent_line_id, start_point_id, end_point_id, start_offset, end_offset, geometry) TO
> stdout;

There is a corrupt block in this table.

This is a good starting point:
https://wiki.postgresql.org/wiki/Corruption

If you have a backup, use it!

One simple option is to set "zero_damaged_pages" and then pg_dump
(http://www.postgresql.org/docs/9.4/static/runtime-config-developer.html#GUC-ZERO-DAMAGED-PAGES),
but don't forget that this *will* lose some data.

Yours,
Laurenz Albe