Database recovery after fs crash

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/

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

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"

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/

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/

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

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.

connections from what?

script languge?
console run 'pgsql'?
ODBC?

Carmen Gloria Sepulveda Dedes wrote:

from ECPG ... Embedded SQL in C
(like pro*C in oracle)

Dennis Gearon wrote:

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   |
+------------------------------+---------------------------------------------+

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.

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   |
+------------------------------+---------------------------------------------+