Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M44502=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1ZXDJ5-0005kI-C8
 for pgsql-admin@arkaria.postgresql.org; Wed, 02 Sep 2015 19:02:51 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-admin-owner+M44502=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1ZXDJ4-0007g8-Tf
 for pgsql-admin@arkaria.postgresql.org; Wed, 02 Sep 2015 19:02:50 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <gsievers19@comcast.net>)
 id 1ZXDHQ-0004ee-0K
 for pgsql-admin@postgresql.org; Wed, 02 Sep 2015 19:01:08 +0000
Received: from resqmta-ch2-06v.sys.comcast.net
 ([2001:558:fe21:29:69:252:207:38])
 by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256)
 (Exim 4.84) (envelope-from <gsievers19@comcast.net>)
 id 1ZXDHN-0005jk-63
 for pgsql-admin@postgresql.org; Wed, 02 Sep 2015 19:01:07 +0000
Received: from resomta-ch2-20v.sys.comcast.net ([69.252.207.116])
 by resqmta-ch2-06v.sys.comcast.net with comcast
 id CK0l1r0072XD5SV01K0zoC; Wed, 02 Sep 2015 19:00:59 +0000
Received: from jerry.enova.com ([12.30.159.20])
 by resomta-ch2-20v.sys.comcast.net with comcast
 id CK0o1r00T0ShjPw01K0qFv; Wed, 02 Sep 2015 19:00:57 +0000
Received: by jerry.enova.com (Postfix, from userid 1001)
 id 892617401EC; Wed,  2 Sep 2015 14:00:48 -0500 (CDT)
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
References: <182E8934F4D0094A94B8A0E5A48B8F5A355ADAD4@MX105CL01.corp.emc.com>
Date: Wed, 02 Sep 2015 14:00:48 -0500
In-Reply-To: <182E8934F4D0094A94B8A0E5A48B8F5A355ADAD4@MX105CL01.corp.emc.com>
 (Utkarsh Porwal's message of "Wed, 2 Sep 2015 18:04:04 +0000")
Message-ID: <86r3mgir33.fsf@jerry.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net;
 s=q20140121; t=1441220459;
 bh=UW3/gt5PIGG55eX9u/t0T2Nttt+s9j8VMvKbkIyyoiw=;
 h=Received:Received:Received:From:To:Subject:Date:Message-ID:
 MIME-Version:Content-Type;
 b=n9ohApWHNpj9hixVULh3PFFqERwAhMetbB8r/smcxH+Z1I/mUQtgp37Lsxucq1zT+
 YFgiikIhol5mRfHVgQDrZVSH+m4+85q2wK1g2QJGPRiaeh1gA7p/tzOX7wJTUvH49l
 TmUlAd23Na1vxP1Kt2QCUqFokuzEnHSyQgZXr2rdiycw4MIX4LA4xobXCuYappic3U
 YMQm93VQJIgw1oM/a7hzNxeECose0pYUI6hO0eN5/FAwvg4sGPvVXy3zbJ81+fRlQD
 xjwDjFKSksmA10qmPlEcvoQSHTc41wQx7C00l/3boIoQzX+cbox+kMS5Xf8bk2SN0G
 DQaPLQH3/iXgw==
X-Pg-Spam-Score: -1.5 (-)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

"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 particul=
ar table fails with errors like =13
>
> ERROR: invalid page header in block 9757828 of relation base/2403282/2403=
731

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:48607570=
9 - STATEMENT: insert into <table> (file_data, last_modified_time, last_mod=
ified_by, dynamic,
> category, file_perms, file_mode, file_group, file_owner, file_size, logic=
al_group, last_accessed_time, is_data_encrypted, location_on_device, file_t=
ag, 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=19t have a pg_dump backup but VM level sna=
pshots.
>
> As far as I know snapshots are not a reliable way of backup of database a=
nd hence shouldn=19t be used.
>
> So now effectively we are left with the alternative of restoring the tabl=
e 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 =3D on;
>
> The table is huge so please let me know if anything above or in addition =
will need an outage.
>
> -Utkarsh
>

--=20
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin