Thread: Database recovery after fs crash
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
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
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"
> 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 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
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
>> 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: >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) > > >
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) > > > > > >
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 | +------------------------------+---------------------------------------------+