Thread: [GENERAL] table corruption
Hi,
we have a table with around 1.6 billion rows having quite lot of big binary data toasted.
Today we started getting:
WIB > ERROR: invalid page in block 1288868309 of relation base/96031/96201
Which is a toast reltype.
I know that zero_damaged_pages and vacuum (or restore the table from backup) will help, but I want to ask if there is a way to identify affected rows/datafiles, so we can 'fix' only the affected data using the backup/source data, instead of restoring the whole table?
Thank you
Regards
P.
On Mon, Oct 23, 2017 at 7:44 AM, Peter Hunčár <hunci@hunci.sk> wrote: > I know that zero_damaged_pages and vacuum (or restore the table from backup) > will help, but I want to ask if there is a way to identify affected > rows/datafiles, so we can 'fix' only the affected data using the > backup/source data, instead of restoring the whole table? You might find the latest version of amcheck helpful here: https://github.com/petergeoghegan/amcheck It's not really written with repair in mind, since that's such a can of worms, but it might still help you. -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Mon, Oct 23, 2017 at 9:35 AM, Peter Geoghegan <pg@bowt.ie> wrote: > On Mon, Oct 23, 2017 at 7:44 AM, Peter Hunčár <hunci@hunci.sk> wrote: >> I know that zero_damaged_pages and vacuum (or restore the table from backup) >> will help, but I want to ask if there is a way to identify affected >> rows/datafiles, so we can 'fix' only the affected data using the >> backup/source data, instead of restoring the whole table? > > You might find the latest version of amcheck helpful here: > https://github.com/petergeoghegan/amcheck > > It's not really written with repair in mind, since that's such a can > of worms, but it might still help you. > > -- > Peter Geoghegan > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Nice to see it included in 10! https://www.postgresql.org/docs/10/static/amcheck.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> Nice to see it included in 10! > https://www.postgresql.org/docs/10/static/amcheck.html The reason that I pointed to the Github version rather than the contrib version is that only the Github version currently has the "heapallindexed" check. That seems likely to be by far the most important check here. -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general