Re: PostgresDB - Advise on possible data corruption - Mailing list pgsql-admin

Hi All,

On further investigation, it is found that the issue with inserts is with table A. However the error of blocks point to
apg_toast table associated with Table A (this has been confirmed). 

Also, from the errors I see that a total of 70 blocks are complaining about errors and block size is 8KB.

Any advise to take it further? I was thinking of reindexing the toast table but it doesn't have a index . Also I fail
tounderstand why inserts will have a problem since it will write to a new location.  

Thanks and Regards,
Utkarsh Porwal


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Porwal, Utkarsh
Sent: Thursday, September 03, 2015 12:40 AM
To: Jerry Sievers
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PostgresDB - Advise on possible data corruption

Thanks jerry I will check that.

What if it's a table?

Thanks and Regards,
Utkarsh Porwal



-----Original Message-----
From: Jerry Sievers [mailto:gsievers19@comcast.net]
Sent: Thursday, September 03, 2015 12:31 AM
To: Porwal, Utkarsh
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PostgresDB - Advise on possible data corruption

"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