From: Jerry Sievers <gsievers19@comcast.net>
To: Porwal\, Utkarsh <utkarsh.porwal@emc.com>
Cc: pgsql-admin\@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: PostgresDB - Advise on possible data corruption
Date: Wed, 02 Sep 2015 14:00:48 -0500
Message-ID: <86r3mgir33.fsf@jerry.enova.com> (raw)
In-Reply-To: <182E8934F4D0094A94B8A0E5A48B8F5A355ADAD4@MX105CL01.corp.emc.com>
References: <182E8934F4D0094A94B8A0E5A48B8F5A355ADAD4@MX105CL01.corp.emc.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
"Porwal, Utkarsh" <utkarsh.porwal@emc.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.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gsievers19@comcast.net, utkarsh.porwal@emc.com
Subject: Re: PostgresDB - Advise on possible data corruption
In-Reply-To: <86r3mgir33.fsf@jerry.enova.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox