Thread: Is it possible to recover the schema from the raw files?
Hello, There was a corruption to the file system due to sudden shutdown of Windows 7. The attempt to connect to one of the databases results in multiple errors, like the following one: ERROR: invalid page in block 58 of relation base/161326/233745 Several raw files were completely lost (zeroed), thus utterly disabling the functionality of this particural database. The raw files were backed up separately outside of the original data folder upon discovery of the issue. I would like to ask whether it is possible to recover the database schema from the raw files? The data itself is less important in this case. Thank you in advance for any insight into such a topic. Kind regards, Tomas
On Sun, Apr 24, 2016 at 2:30 AM, Tomas J Stehlik <tomas@stehlik.co.uk> wrote:
Hello,
There was a corruption to the file system due to sudden shutdown of Windows
7.
The attempt to connect to one of the databases results in multiple errors,
like the following one:
ERROR: invalid page in block 58 of relation base/161326/233745
Several raw files were completely lost (zeroed), thus utterly disabling the
functionality of this particural database.
The raw files were backed up separately outside of the original data folder
upon discovery of the issue.
I would like to ask whether it is possible to recover the database schema
from the raw files?
The data itself is less important in this case.
You will have to restore the data-directory from the backup and perform recovery (if required). This will get you the database schema including the data (or whatever is part of the backup). If you are not worried about the data, then, you will need to manually rebuild the database schema, recovery process will not help you much here.
If you cannot re-build the schema manually (meaning: if you do not have scripts available to build the schema), then you will need to restore the database from the backup and manually cleanup all the data, which is quite a bit of work.
Regards,
Venkata B N
Fujitsu Australia
Hello Venkata, Thank you for your reply. You are stating the obvious though. If those conditions were met, I would have formulated my question differently. Kind regards, Tomas
On 04/24/2016 03:54 AM, Tomas J Stehlik wrote: > Hello Venkata, > > Thank you for your reply. > > You are stating the obvious though. If those conditions were met, I would have formulated my question differently. So to be clear, all that remains of the database cluster are the files you copied out from the data directory, correct? In your original post you said: "The attempt to connect to one of the databases results in multiple errors, like the following one: ..." That would imply that the server actually started, is that the case? What happened if you connected to another database in the cluster? > > Kind regards, > > Tomas > > > -- Adrian Klaver adrian.klaver@aklaver.com
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
On Sunday, April 24, 2016, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
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...
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.
David J.
Hello Karsten, Thank you. > 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. This is actually a very interesting idea. Kind regards, Tomas
On 04/24/2016 09:07 AM, Tomas J Stehlik wrote: > 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]. Then I am of no further use to this conversation. > > > Kind regards, > > Tomas > > > -- Adrian Klaver adrian.klaver@aklaver.com
Hello Karsten, > 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. No problem at all. I very much appreciate all your input and ideas. Thank you. Kindest regards, Tomas
Hello Adrian, > Then I am of no further use to this conversation. No problem at all. Thank you for your well considered input and ideas. Have a lovely day. Kindest regards, Tomas
On Sun, Apr 24, 2016 at 05:07:10PM +0100, Tomas J Stehlik wrote: >> 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. :-) > 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. Given the fact that no-one will know "all" approaches to "everything" it may help to bring together which approaches have actually been tried in which exact fashion. Personally, I'd be humble enough to assume (hope ?) someone would know Everything+1. In which case I'd rush to supply Everything in order to most quickly learn of +1. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346