Re: Recover from corrupted database due to failing disk

Il 03-11-2016 00:21 Jim Nasby ha scritto:
> On 11/2/16 2:02 PM, Gionatan Danti wrote:
>
> 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.

I understand that (unfortunately) user data *will* be corrupted/lost.
However, having no backup, I think the customer *must* accept that...

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

The whole dump/restore approach surely is the most sensible one.
However, I am concerned that if the dump have some undetected problems
leading to a failed restore, I had to recover from the raw files (which
I would like to avoid). Moreover, the expected remaining lifetime of
such a database is 2/3 months only, as a new production system should be
installed shortly. This is why I would prefer to use vacuum/reindex and
avoid a full dump/restore.

Thank you very much Jim.

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