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