PostgresDB - Advise on possible data corruption

From: "Porwal, Utkarsh" <utkarsh(dot)porwal(at)emc(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: PostgresDB - Advise on possible data corruption
Date: 2015-09-02 18:04:04
Message-ID: 182E8934F4D0094A94B8A0E5A48B8F5A355ADAD4@MX105CL01.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jerry Sievers 2015-09-02 19:00:48 Re: PostgresDB - Advise on possible data corruption
Previous Message Ames, Danielle 2015-09-02 16:20:55 Index Bloat after Reindex