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
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? |