Re: Recovering database from crashed HD (bad sectors)

Michael Paquier wrote:
> On Sat, Jul 18, 2015 at 6:59 PM, Amitabh Kant <amitabhkant(at)gmail(dot)com> wrote:
> > A development box hard disk failed which was running a PG instance with
> > multiple databases on it. I got the data recovered with some bad sector
> > errors. Ran another instance of PG (same version), and was to able to take
> > dump (using pg_dump) of all but one database. For one database I am getting
> > the following error:
> >
> > pg_dump -Fc alpha_45 > alpha_45.dump
> > pg_dump: [archiver (db)] connection to database "alpha_45" failed: FATAL:
> > could not open file "base/525035/11678": No such file or directory
> >
> > These are the only two files in the directory similar to the one above:
> > /usr/local/pgsql/data/base/525035/11678_fsm
> > /usr/local/pgsql/data/base/525035/11678_vm
> >
> > Is there any hope of recovering this DB, or should I start looking into
> > restoring from old backups? Data loss is not a concern, I just would like to
> > know if I should even try working on it. I looked into this page
> > http://www.postgresql.org/docs/9.1/static/runtime-config-developer.html, and
> > tried using "zero_damaged_pages = on" in postgresql.conf, but it was of no
> > help.
>
> Visibly your data is already lost in alpha_45... Hence if you can
> recover this database from an older backup you had better do it. Now
> perhaps other folks here have other recommendations and strategies
> though :)

If the data since the last backup is very valuable, there are certainly
other avenues to explore, but it's an ad-hoc job that takes time and
someone knowledgeable enough directly in charge of the job. It's not
something I would attempt over a mailing list, for sure.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services