0

I have a Postgres 9.6 database that is corrupted. The hardware it was running on went bad, and there is at least one corrupted page in the DB. When running pg_dump I get the following error:

pg_dump: WARNING:  page verification failed, calculated checksum 24925 but expected 25309
pg_dump: Dumping the contents of table "my_table" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  invalid page in block 250471 of relation base/16384/754450

This particular table is easy to fix even if data is missing, so what I'd like to do is to zero out that page entirely and continue with the DB. But I could not find a way on how to do that, how do I tell Postgres to ignore the error, delete the data in that page and go on? pg_dump itself doesn't seem to have an option like this.

I also would like to know if there are more checksum errors, but the tool I found for this is only present since Postgres 11 and not in my older version.

How do I fix the bad pages by removing all the data in them, so that the database can continue to run and pg_dump can run again?

Improve this question

1 Answer 1

Reset to default
1

Set zero_damaged_pages to on, then PostgreSQL will treat each page with a damaged header like an empty page, and you can dump the table.

Improve this answer
4
  • That worked, though I also get new errors then about "missing chunk number 0 for toast value [...]". I can fix those by truncating the entire table, but finding out only the affected rows is rather tedious.
    – Fabian
    Commented Jul 18, 2023 at 12:22
  • Pretty easy with a loop like DO $$DECLARE k bigint; BEGIN FOR k IN SELECT id FROM tab LOOP BEGIN PERFORM badcol::text FROM tab WHERE id = k; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Broken row with id = %', k; END; END LOOP; END;$$;. Update the broken value in the table to NULL or something.
    – Laurenz Albe
    Commented Jul 18, 2023 at 12:36
  • Thank you for the suggestion, unfortunately that doesn't seem to trigger the error. A PERFORM * FROM bad_table just runs though without complaint for some reason. And I don't know which column is the problem, there are quite a few large columns in that table that could be stored in a TOAST table.
    – Fabian
    Commented Jul 18, 2023 at 13:19
  • Right. I didn't write PERFORM *. Try all these columns then.
    – Laurenz Albe
    Commented Jul 18, 2023 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.