Обсуждение: PostgresDB - Advise on possible data corruption
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
"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
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
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
Anyone?? -----Original Message----- From: Porwal, Utkarsh Sent: Thursday, September 03, 2015 8:15 PM To: Porwal, Utkarsh; Jerry Sievers Cc: pgsql-admin@postgresql.org Subject: RE: [ADMIN] PostgresDB - Advise on possible data corruption 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
Porwal, Utkarsh wrote: >>> 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 >> On further investigation, it is found that the issue with inserts is with table A. However the error >> of blocks point to a pg_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 to understand why inserts will have a problem since it will write to a new >> location. > Anyone?? An INSERT in a database table is not an append to a file. If your toast table is corrupted and you don't have a backup, your best bet is to salvage all the data you can by selectively dumping as much of the table as possible. Expect data loss. Yours, Laurenz Albe