Re: Is it possible to recover the schema from the raw files?

On Sun, Apr 24, 2016 at 11:16 AM, Tomas J Stehlik <tomas(at)stehlik(dot)co(dot)uk>
wrote:

> Please note that I mentioned previously that the database is corrupt.
>
> "pg_class" table can be queried but it is not possible to dump the database
> in question as some of the pages in blocks are missing.
>
> In this case, it is necessary to rely on raw files only. Thanks.
>
> T
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Please note, in cases of this kind, it is always pertinent to provide the
PostgreSQL version and O/S, especially if the possibility exists
it might be related to an existing bug.

>The raw files were backed up separately outside of the original data
folder upon discovery of the issue.

That's not going to help you, as the files you need are already corrupted
at that point.

If, however, you have a backup of the raw files _prior_ to the crash, you
might be in luck.
So, since you verified it is only one database that is the problem, but the
PostgreSQL server can access the others, do the following:

SELECT oid, datname FROM pg_database WHERE datname = 'your_bad_db_name';

The oid is the directory file under the base directory that needs to be
restored.
That is the directory (and all sub files) that needs to be restored.

First, stop the PostgreSQL server.
Back up that current (but bad) directory and all sub files.
Then restore the good backup of that directory only!
Restart the PostgreSQL server and hopefully you will then have access to an
old version of the corrupted database.
If successful, immediately take a SQL dump of that database.

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.