Recover from corrupted database due to failing disk

From: Gionatan Danti <g(dot)danti(at)assyoma(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Recover from corrupted database due to failing disk
Date: 2016-11-02 19:02:16
Message-ID: 3cf7d8fa35d4ef67cf618b2e07580175@assyoma.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear all,
some days ago I was tasked to recover a production database from a
failing single-disk (!) system. I initially planned to restore from
backups but, due to the bad disk, backups (done via pg_dumpall) were
failing and nobody cared to notice (!!). Bottom line, the system was
failing and no backup were in place (!!!). I perfectly understand this
is (very) bad management, but I am now tasked to somehow recover from
this situation.

This is a very old installation. Some details:
- o.s. CentOS 5.6 x86_64
- postgresql version postgresql-8.1.22-1.el5_5.1

By using ddrescue, I successfully migrated the entire old failing disk
to a healty one and solve the first problem (failing disk).

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.

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?

This is my current action plan:
- stop postgresql;
- make a backup copy the entire /var/lib/pgsql directory
- set zero_damaged_pages=on and increase maintenance_work_mem (to let
vacuum operate faster);
- start postgresql;
- execute a "vacuumdb -a"
- if necessary, execute a "reindexdb -a"

It sounds reasonable? Do you have any suggestions/warnings?
Thanks.

--
Danti Gionatan
Supporto Tecnico
Assyoma S.r.l. - www.assyoma.it
email: g(dot)danti(at)assyoma(dot)it - info(at)assyoma(dot)it
GPG public key ID: FF5F32A8

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joanna Xu 2016-11-02 19:49:19 Questions on Post Setup MASTER and STANDBY replication - Postgres9.1
Previous Message Joshua D. Drake 2016-11-02 17:40:19 Re: Hardware recommendations?