Thread: Database recovery after fs crash

Database recovery after fs crash

From
J.Henning@t-online.de
Date:
Hi,

I've got a serious problem. After a horrible filesystem crash the only thing
left of my database are some files in /lost+found (with changed names!). Is
there a way to recover the data from those files? I managed to identify the
files belonging to PostgreSQL (unfortunately, these are part of more than one
database (think of template0/1...)), but I don't know which file belongs to
which table.
I've searched the web for recovery tools but none of them seems capable of
what I want to do. Is there any documentation on the structure of
PostgreSQL's
database files?
If this is the wrong place to ask, please tell me where I might get help.

Cheers,
  Christian

Re: Database recovery after fs crash

From
Martijn van Oosterhout
Date:
Wow. Take to pull out those backups...

Hmm, presumably you don't have those. There are programs to read postgresql
data files however they need to know the exact structure of the table before
you can even start to decode them. I usually start by browsing files by hand
to see if I can determine from any embedded text which table it was.

Then if you create a new database and recreate the table *exactly* the same
and then copy the datafile over. You might need to create some clog files
but you *might* be able to recover some data.

It's a bit tricky though. Black magic and all that. Backups are absolutly
the best way.

Hope this helps,
On Mon, Aug 25, 2003 at 11:12:20PM +0200, J.Henning@t-online.de wrote:
> Hi,
>
> I've got a serious problem. After a horrible filesystem crash the only thing
> left of my database are some files in /lost+found (with changed names!). Is
> there a way to recover the data from those files? I managed to identify the
> files belonging to PostgreSQL (unfortunately, these are part of more than one
> database (think of template0/1...)), but I don't know which file belongs to
> which table.
> I've searched the web for recovery tools but none of them seems capable of
> what I want to do. Is there any documentation on the structure of
> PostgreSQL's
> database files?
> If this is the wrong place to ask, please tell me where I might get help.
>
> Cheers,
>   Christian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

Attachment

Re: Database recovery after fs crash

From
Alvaro Herrera Munoz
Date:
On Tue, Aug 26, 2003 at 10:17:48AM +1000, Martijn van Oosterhout wrote:
> Wow. Take to pull out those backups...
>
> Hmm, presumably you don't have those. There are programs to read postgresql
> data files however they need to know the exact structure of the table before
> you can even start to decode them. I usually start by browsing files by hand
> to see if I can determine from any embedded text which table it was.

Hm, you fail to mention your own pgfsck tool.  I wonder why.  I have not
tried it myself, but sounds like it can be handy.

IIRC it should be in http://svana.org/kleptog somewhere.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"God is real, unless declared as int"

Re: Database recovery after fs crash

From
J.Henning@t-online.de
Date:
> Hmm, presumably you don't have those.

Unfortunately not. All backup measures I took failed on the exact same
occasion - worst case possible.

> I usually start by browsing files by hand to see if I can determine
> from any embedded text which table it was.

Seems easy enough.

> Then if you create a new database and recreate the table *exactly* the
> same and then copy the datafile over. You might need to create some
> clog files but you *might* be able to recover some data.

How do I do that? I don't know the format of the clog files. The only
information I found was in clog.h and xlog.h.

> It's a bit tricky though. Black magic and all that. Backups are absolutly
> the best way.

Definitely, if only my backup had worked...

  Christian

Re: Database recovery after fs crash

From
Martijn van Oosterhout
Date:
On Tue, Aug 26, 2003 at 09:31:09AM -0400, Alvaro Herrera Munoz wrote:
> On Tue, Aug 26, 2003 at 10:17:48AM +1000, Martijn van Oosterhout wrote:
> > Wow. Take to pull out those backups...
> >
> > Hmm, presumably you don't have those. There are programs to read postgresql
> > data files however they need to know the exact structure of the table before
> > you can even start to decode them. I usually start by browsing files by hand
> > to see if I can determine from any embedded text which table it was.
>
> Hm, you fail to mention your own pgfsck tool.  I wonder why.  I have not
> tried it myself, but sounds like it can be handy.

That's because it's a bit too far for that. pgfsck is good for recovering
data from corrupted files, but it relies on the system tables being intact.

In theory, it should be possible to write a program to point at a bunch of
files, test each file against the known structures of various system tables
and use that to reconstruct the rest. Nice idea, but not something I have
the time to implement (I have daily offsite backups now :) )

In this case it could be used once you have reconstructed the tables in a
new system and use -r to dump the data.

> IIRC it should be in http://svana.org/kleptog somewhere.

Yep.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

Attachment

Re: Database recovery after fs crash

From
Martijn van Oosterhout
Date:
On Tue, Aug 26, 2003 at 02:18:28PM +0200, J.Henning@t-online.de wrote:
> > Hmm, presumably you don't have those.
>
> Unfortunately not. All backup measures I took failed on the exact same
> occasion - worst case possible.

Ouch!

> > I usually start by browsing files by hand to see if I can determine
> > from any embedded text which table it was.
>
> Seems easy enough.

Can be tricky depending on the data.

> > Then if you create a new database and recreate the table *exactly* the
> > same and then copy the datafile over. You might need to create some
> > clog files but you *might* be able to recover some data.
>
> How do I do that? I don't know the format of the clog files. The only
> information I found was in clog.h and xlog.h.

xlog is fairly easy, I think resetxlog or something can work there. For clog
I just noted from the logs which clog files it was missing and created them
filled with either 0x00 or 0x55 (I forget if it make a difference) and kept
going until done.

Good luck.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

Attachment

Re: Database recovery after fs crash

From
J.Henning@t-online.de
Date:
>> How do I do that? I don't know the format of the clog files. The only
>> information I found was in clog.h and xlog.h.
>
> xlog is fairly easy, I think resetxlog or something can work there. For
> clog I just noted from the logs which clog files it was missing and
> created them filled with either 0x00 or 0x55 (I forget if it make a
> difference) and kept going until done.

Thanks a lot for your help. Whatever table I was able to identify I was
able to recover. I just filled every clog file missing with 0x00 and used
pg_resetxlog for the xlog files, just like you suggested. It worked :)
Now, I have at least part of my data back.

  Christian

Multiple Connections

From
Carmen Gloria Sepulveda Dedes
Date:
Hi!

I'm working with ECPG.
Somebody know how can I establish multiple connections to the database??
(I have used ENABLE THREADS and CONTEXT ALLOCATE in
oracle ... I'm looking for something similar to this ... if exists) ....

Thanks.


Re: Multiple Connections

From
Dennis Gearon
Date:
connections from what?

script languge?
console run 'pgsql'?
ODBC?

Carmen Gloria Sepulveda Dedes wrote:

>Hi!
>
>I'm working with ECPG.
>Somebody know how can I establish multiple connections to the database??
>(I have used ENABLE THREADS and CONTEXT ALLOCATE in
>oracle ... I'm looking for something similar to this ... if exists) ....
>
>Thanks.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>


Re: Multiple Connections

From
Carmen Gloria Sepulveda Dedes
Date:
from ECPG ... Embedded SQL in C
(like pro*C in oracle)

Dennis Gearon wrote:

> connections from what?
>
> script languge?
> console run 'pgsql'?
> ODBC?
>
> Carmen Gloria Sepulveda Dedes wrote:
>
> >Hi!
> >
> >I'm working with ECPG.
> >Somebody know how can I establish multiple connections to the database??
> >(I have used ENABLE THREADS and CONTEXT ALLOCATE in
> >oracle ... I'm looking for something similar to this ... if exists) ....
> >
> >Thanks.
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
> >


Re: Multiple Connections

From
Paul Thomas
Date:
On 29/08/2003 21:59 Carmen Gloria Sepulveda Dedes wrote:
> Hi!
>
> I'm working with ECPG.
> Somebody know how can I establish multiple connections to the database??
> (I have used ENABLE THREADS and CONTEXT ALLOCATE in
> oracle ... I'm looking for something similar to this ... if exists) ....


Have you tried using AS connection-name in your EXEC SQL CONNECT ...
statement?

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: Multiple Connections

From
Carmen Gloria Sepulveda Dedes
Date:
> > I'm working with ECPG.
> > Somebody know how can I establish multiple connections to the database??
> > (I have used ENABLE THREADS and CONTEXT ALLOCATE in
> > oracle ... I'm looking for something similar to this ... if exists) ....
>
> Have you tried using AS connection-name in your EXEC SQL CONNECT ...
> statement?
>

Hi! ... I don't know thar statement ... Can you explain me more??? Thanks.


Re: Multiple Connections

From
Paul Thomas
Date:
On 01/09/2003 20:10 Carmen Gloria Sepulveda Dedes wrote:
> > > I'm working with ECPG.
> > > Somebody know how can I establish multiple connections to the
> database??
> > > (I have used ENABLE THREADS and CONTEXT ALLOCATE in
> > > oracle ... I'm looking for something similar to this ... if exists)
> ....
> >
> > Have you tried using AS connection-name in your EXEC SQL CONNECT ...
> > statement?
> >
>
> Hi! ... I don't know thar statement ... Can you explain me more???
> Thanks.


It's in section 4.2 of ECPG docs.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+