Re: PostgresDB - Advise on possible data corruption

Thanks jerry I will check that.

What if it's a table?

Thanks and Regards,
Utkarsh Porwal

-----Original Message-----
From: Jerry Sievers [mailto:gsievers19(at)comcast(dot)net]
Sent: Thursday, September 03, 2015 12:31 AM
To: Porwal, Utkarsh
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] 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