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

Hello Adrian,

So to be clear, all that remains of the database cluster are the files
you copied out from the data directory, correct?

Yes and no. For the purpose of this discussion, it may be better to say "yes" though.

That would imply that the server actually started, is that the case?

Yes. The database server's files were not damaged.

What happened if you connected to another database in the cluster?

That's irrelevant. That said, some were damaged and some not.

Kind regards,

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 03:00:09PM +0100, Tomas J Stehlik wrote:

What happened if you connected to another database in the cluster?

That's irrelevant.

I dare assume Adrian asked for a reason :-)

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/24/2016 07:09 AM, Karsten Hilbert wrote:

On Sun, Apr 24, 2016 at 03:00:09PM +0100, Tomas J Stehlik wrote:

What happened if you connected to another database in the cluster?

That's irrelevant.

I dare assume Adrian asked for a reason :-)

Yes, the reason being that the OP was asking about the possibility of
recovering schema information, not data. Given a server that starts and
a database to connect to in the cluster then it might be possible to get
that information without resorting to mining the raw files(something I
do not know how to do anyway). This of course assumes that the system
tables where not corrupted. As a test, if you can connect to a database
in the cluster what happens if you do?:

select * from pg_class;

or if you want to cut to the chase:

pg_dump -s -d database_in_question -h some_host -U some_user

Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 11:16 AM, Tomas J Stehlik <tomas@stehlik.co.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@postgresql.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.

On 04/24/2016 08:16 AM, Tomas J Stehlik 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.

So did you do a schema only dump or a complete dump?

In this case, it is necessary to rely on raw files only. Thanks.

Well if the corrupted raw files include the system information then I
think you are out of luck.

T

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 04:16:10PM +0100, Tomas J Stehlik wrote:

Please note that I mentioned previously that the database is corrupt.

Given the facts that Adrian attempted to engage in a
solution-bound conversation all the while mentioning that he
doesn't know how to recover the schema from the raw files I
feel inclined to consider it a fair assumption that he did,
indeed, note that you mentioned 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.

If I recall correctly, you stated that the data isn't
important in this case.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Melvin,

Thank you.

Your reply is similar to the first one in that it makes assumptions to the contrary of my original e-mail.

Therefore I shall tackle only the relevant bits:
1. This is not related to any existing bug. It was a corruption of the file system.
2. OID of the corrupt database was known previously.
3. If I had raw files from before the corruption, I wouldn’t have written to this mailing list.
4. There is nothing to restore, as we are not talking about backups.

The original question was whether is it possible to *recover* the schema from the raw files?

Kind regards,

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Adrian,

Thank you.

So did you do a schema only dump or a complete dump?

There is no dump. There are just raw files.

Well if the corrupted raw files include the system information
then I think you are out of luck.

Well, this topic is not about "luck".
The question potentially targets someone who could tell whether something
like this is possible.

Kind regards,

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Karsten,

If I recall correctly, you stated that the data isn't
important in this

Yes, exactly. However, pages in blocks apparently store also the
representations of the database schemas. And those are also corrupt.

A tiny bit only but still corrupt.

Therefore the overall information is *largely* intact - yet the small
missing amount is causing PostgreSQL not being able to work with this
particular database.

Kind regards,

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote:

So did you do a schema only dump or a complete dump?

There is no dump. There are just raw files.

I believe Adrian wanted to know whether you attempted a
schema or complete dump *after* the fact, like what he
suggested a mail ago or so.

In case the FS corruption "only" affects raw files related to
user data (as opposed to also affecting data in pg_* tables)
a schema-only dump does have a slight chance of success.

That chance might potentially be increased by judicious use
of zero_damaged_pages and related low-level techniques the
prerequisite conditions of which people seem to have been
trying to inquire about upthread.

The question potentially targets someone who could tell whether something
like this is possible.

"possible" depends no the exact circumstances, the details of
which people have been trying to tease out.

Regards,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote:

Well if the corrupted raw files include the system information
then I think you are out of luck.

Well, this topic is not about "luck".

Surely, English isn't my vernacular language - but "out of
luck" has seemed to be an euphemism for "no, it
ain't possible" to me unto now.

Adrian cautiously added "I think" (as in 'he thinks').

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/24/2016 08:35 AM, Tomas J Stehlik wrote:

Hello Adrian,

Thank you.

So did you do a schema only dump or a complete dump?

There is no dump. There are just raw files.

Have you even tried a schema only dump from the original instance?

Well if the corrupted raw files include the system information
then I think you are out of luck.

Well, this topic is not about "luck".
The question potentially targets someone who could tell whether something
like this is possible.

If the information is not there, it is not there. In other words if the
zeroed out blocks you alluded to cover the system information then I am
not sure how it would be possible to recover information from 0?

Kind regards,

Tomas

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/24/2016 08:54 AM, Karsten Hilbert wrote:

On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote:

Well if the corrupted raw files include the system information
then I think you are out of luck.

Well, this topic is not about "luck".

Surely, English isn't my vernacular language - but "out of
luck" has seemed to be an euphemism for "no, it
ain't possible" to me unto now.

Adrian cautiously added "I think" (as in 'he thinks').

Yeah, should have been smarter in my word choice.

Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Karsten,

Thank you.

I believe Adrian wanted to know whether you attempted a
schema or complete dump *after* the fact, like what he
suggested a mail ago or so.

In case the FS corruption "only" affects raw files related to
user data (as opposed to also affecting data in pg_* tables)
a schema-only dump does have a slight chance of success.

That chance might potentially be increased by judicious use
of zero_damaged_pages and related low-level techniques the
prerequisite conditions of which people seem to have been
trying to inquire about upthread.

The question potentially targets someone who could tell
whether something like this is possible.

"possible" depends no the exact circumstances, the details of
which people have been trying to tease out.

All this relevant information has already been supplied previously.

Kind regards,

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 04:42:21PM +0100, Tomas J Stehlik wrote:

If I recall correctly, you stated that the data isn't
important in this

Yes, exactly. However, pages in blocks apparently store also the
representations of the database schemas. And those are also corrupt.

In that case _my_ knowledge also goes only so far as to be
able to fear "no it is not possible".

More knowledgeable people may - given more detailed
information - still be able to suggest approaches to
recover most if not all of the schema.

Like replacing (some of) the pg_* containing raw files with
those from an uncorrupted database (having been suggested
earlier this year) which may work if the corrupted blocks in
pg_* only affect data actually describing _that_ database
rather than establishing relationships not unique to this
database (say, encodings, default operators, ...). If those
can be replaced and there is still corruption in some parts
describing the local schema then it may work to apply
zero_damaged_pages, pg_resetxlog, and similar tools in order
to make some of the schema dumpable.

It may help to look into disabling system indexe as well.

Best regards,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Andrian,

Thank you

Have you even tried a schema only dump from the original instance?

That's an odd question. However, I understand that maybe a lot of beginners
come onto this mailing list.

To the contrary, this is a non-trivial situation.

You can safely assume that I wrote the original request because all the
other approaches failed to bring a result. Everything has already been tried
and tested.

The only question remains the one in the subject line.

If the information is not there, it is not there. In other words if the
zeroed out blocks you alluded to cover the system information then I am
not sure how it would be possible to recover information from 0?

This is an incorrect assumption.
Most of the information is available.
Just the PostgreSQL server can't work with it [because of the missing bits].

Kind regards,

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Apr 24, 2016 at 04:58:48PM +0100, Tomas J Stehlik wrote:

The question potentially targets someone who could tell
whether something like this is possible.

"possible" depends no the exact circumstances, the details of
which people have been trying to tease out.

All this relevant information has already been supplied previously.

In that case I must surely have missed it and feel I
can no longer be of any assistance, even if it only
amounted to but contributing leads to be investigated.
Sorry.

Regards,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sunday, April 24, 2016, Karsten Hilbert <Karsten.Hilbert@gmx.net
<javascript:_e(%7B%7D,'cvml','Karsten.Hilbert@gmx.net');>> wrote:

On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote:

Well if the corrupted raw files include the system information
then I think you are out of luck.

Well, this topic is not about "luck".

Surely, English isn't my vernacular language - but "out of
luck" has seemed to be an euphemism for "no, it
ain't possible" to me unto now.

Adrian cautiously added "I think" (as in 'he thinks').

Not quite. It means the outcome does rely on luck to some degree. You got
lucky if the area of corruption doesn't include the area in which the data
you want resides. You were unlucky, or out of luck, if the corruption
includes the desired data. It's still an exaggeration because the luck
being noted is quite specific within this specific context the point of
luck playing a factor is correctly made.

My understanding is the potential exists but the outcome is uncertain.
People are suggesting some of the simple ways to accomplish this goal
before informing you like quite possibly you should consider hiring someone
specializing in this sort of thing. You should be understanding that some
things may have been tried or already deemed insufficient and do it anyway
- it's part of a process which itself i difficult to do over email. You
might trying hooking up with someone on IRC...

David J.