PostgresDB - Advise on possible data corruption

["Porwal, Utkarsh" <utkarsh.porwal@xxxxxxx>]

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
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 don’t 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 shouldn’t 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