Re: Recover from corrupted database due to failing disk

On 11/2/16 2:02 PM, Gionatan Danti wrote:
> However, backup continue to fail with "invalid page header in block"
> message. Morever, I am very near the xid wraparound limit and, as vacuum
> fails due to the invalid blocks, I expect a database shutdown (triggered
> by the 1M transaction protection) within some days.

That means at least some of the Postgres files have been damaged
(possibly due to the failing disk). Postgres will complain when it sees
internal data structures that don't make sense, but it has no way to
know if any of the user data has been screwed up.

> From my understanding, both problem *should* be solved enabling
> "zero_damaged_pages" and executing a "vacuumdb -a". Is this expectation
> correct? Will a "reindexdb -a" necessary?

I wouldn't trust the existing cluster that far. Since it sounds like you
have no better options, you could use zero_damaged_pages to allow a
pg_dumpall to complete, but you're going to end up with missing data. So
what I'd suggest would be:

stop Postgres
make a copy of the cluster
start with zero_damaged_pages
pg_dumpall
stop and remove the cluster (make sure you've got that backup)
create a new cluster and load the dump
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461