Re: PostgresDB - Advise on possible data corruption

"Porwal, Utkarsh" <utkarsh(dot)porwal(at)emc(dot)com> writes:

> Hi Team,
>
> Need some inputs here.
>
> At a customer environment we found an issue where insertion to a particular table fails with errors like 
>
> ERROR: invalid page header in block 9757828 of relation base/2403282/2403731

Figure out whether that file belong to the table itself or an index. If
it's an index and this is an isolated case, then your recovery
strategy could be as simple as rebuilding it.

See pg_class.relfilenode

> 2015-08-31 02:37:53.289 NZST - SessionId:55e2ff7c.6329 - TransId:486075709 - STATEMENT: insert into <table> (file_data, last_modified_time, last_modified_by, dynamic,
> category, file_perms, file_mode, file_group, file_owner, file_size, logical_group, last_accessed_time, is_data_encrypted, location_on_device, file_tag, encoding_format,
> out_of_sync_files, e_salt, revision_id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
>
> To me this sounds like a data corruption.
>
> To make things worse, they dont have a pg_dump backup but VM level snapshots.
>
> As far as I know snapshots are not a reliable way of backup of database and hence shouldnt be used.
>
> So now effectively we are left with the alternative of restoring the table from a possible data corruption.
>
> Could you tell if any of the options can be used and which one?
>
> 1. cluster <index> on <tablename>;
>
> or
>
> 2. SET zero_damaged_pages = on;
>
> The table is huge so please let me know if anything above or in addition will need an outage.
>
> -Utkarsh
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800