Thread: To recover data corrupted
Hi, We have an old waiter Postgresql 6.5.3 which regularly had problem and which crashed. I recovered a large part of the data saved with pg_dump, except that which were corrupted. In the directory "/var/lib/postgres/data/base", I found the files which correspond to the tables corrupted. With a text editor, I can edit these binary files. There are the texts and many nonprintable characters : Ø^@ì^@^@ ^@ <94><9F>Ø^@,<9F>Ð^@Ž<9E>ð^@ì<94>è^Et<93>ð^B<<91>p^Dô<8F><90>^Bä<8C> ^F<9C><83> ^DŽ<88>\<87>°^Bì<85>à^B<80><99>Ð ^A°<98> ^<9E>X^Aà<97> ^AL<9D>x^Ah<9A>p^Dì<80>`^EÌ ^@^@ <9C>X^A4^B^@^@ ^A^@^@^L^T^@^@l^S^@^@Œ^R^@^@^L^R^@^@\^Q^@^@¬^P^@^@ü^O^@^@<8C>^O^@^@^L^O^@^@|^N^@^@Ü^M^@^@,^M^@^@|^L^@^@È ^K^@^@^P^K^@^@T ^@^@<94> ^@^@^@^@^@^@x^G^@^@Œ^F^@^@ü^E^@^@<^E^@^@|^D^@^@Œ^C^@^@ü^B^@^@<^B^@^@<80>^]^@^@^L^A^@^@^@^@^@^@^@^@^@^@^@^@^@ ^@^@^@^@^@^@^@^@^@À^@<9B>^@éŒW^A^@^@^@^@<86>UW^A%TÅ^A^@^@ ^@^K^@^O^@^B<85> ^@<8C>^@^@^@^\^@^@^@^V^@^@^@Mr et Mme Perro nno^@^@x^@^@^@Dans une maison indépendante, située dans un quartier résidentiel, à 800 mètres des commerces et à 13 Km des plages.ª^A^@^@<br>Appréciation de l'Office de Tourisme : *<br><br> 1 chambre en rez-de-chaussée avec 1 lit 2 personnes avec entrée indépendante (possibilité couchage 1 personne supplémentaire ). Salle d'eau et WC<br> Ouvert toute l'année<br> Do you know how I can recover the data without having to make copy/paste from the binary file? Regards. -- ============================================== | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:frederic@juliana-multimedia.com | ===========================Debian=GNU/Linux===
On Tue, May 23, 2006 at 01:03:16PM +0200, Frederic Massot wrote: > Hi, > > We have an old waiter Postgresql 6.5.3 which regularly had problem and > which crashed. 6.5?! Holy cow, you win the prize for oldest version I've seen. I don't know if even Tom Lane has experience with that old a version, so you're probably out of luck on recovering anything you don't have already. You very seriously need to upgrade. There's litterally dozens of data loss bugs that have been fixed since that time. I'd suggest trying to run the most recent 7.4.x version of pg_dump against the old database; if that works, load it into 7.4.x and then you can upgrade from there to 8.1.4 (out this week or next). You can try going directly to 8.1.3/4, but I suspect the newer pg_dump will have issues. You might actually have to go from 6.5 to an early 7.x release, and then up to 8.1 (8.1's pg_dump would probably work back to 7.2 if not 7.1 or 7.0). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
After takin a swig o' Arrakan spice grog, jnasby@pervasive.com ("Jim C. Nasby") belched out:
> On Tue, May 23, 2006 at 01:03:16PM +0200, Frederic Massot wrote:
>> Hi,
>>
>> We have an old waiter Postgresql 6.5.3 which regularly had problem and
>> which crashed.
>
> 6.5?! Holy cow, you win the prize for oldest version I've seen.
>
> I don't know if even Tom Lane has experience with that old a version, so
> you're probably out of luck on recovering anything you don't have
> already.
>
> You very seriously need to upgrade. There's litterally dozens of data
> loss bugs that have been fixed since that time. I'd suggest trying to
> run the most recent 7.4.x version of pg_dump against the old database;
> if that works, load it into 7.4.x and then you can upgrade from there to
> 8.1.4 (out this week or next). You can try going directly to 8.1.3/4,
> but I suspect the newer pg_dump will have issues. You might actually
> have to go from 6.5 to an early 7.x release, and then up to 8.1 (8.1's
> pg_dump would probably work back to 7.2 if not 7.1 or 7.0).
There are fundamental changes to data types between 6.5 and 7.0; when
we did some "mass conversions" of 6.5-based databases to 7.4, a couple
of years ago, it turned out to be necessary to go as far as remapping
names of types, as date types have *massively* changed since then.
It wasn't "awfully hard" to do the conversions, but we couldn't depend
on pg_dump to do everything :-(.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://linuxdatabases.info/info/postgresql.html
"Listen, strange women, lyin' in ponds, distributin' swords, is no
basis for a system of government. Supreme executive power derives
itself from a mandate from the masses, not from some farcical aquatic
ceremony." -- Monty Python and the Holy Grail
Jim C. Nasby wrote: > On Tue, May 23, 2006 at 01:03:16PM +0200, Frederic Massot wrote: > >>Hi, >> >>We have an old waiter Postgresql 6.5.3 which regularly had problem and >>which crashed. > > > 6.5?! Holy cow, you win the prize for oldest version I've seen. > We have of another server with Postgresql 7.4. :o) But, there remained this old server. Regards. -- ============================================== | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:frederic@juliana-multimedia.com | ===========================Debian=GNU/Linux===
On Tue, May 23, 2006 at 06:10:49PM +0200, Frederic Massot wrote: > Jim C. Nasby wrote: > >On Tue, May 23, 2006 at 01:03:16PM +0200, Frederic Massot wrote: > > > >>Hi, > >> > >>We have an old waiter Postgresql 6.5.3 which regularly had problem and > >>which crashed. > > > > > >6.5?! Holy cow, you win the prize for oldest version I've seen. > > > > We have of another server with Postgresql 7.4. :o) > But, there remained this old server. Did it get drywalled-over and forgotten? :) Seriously, you would do well to keep a better eye on when versions get end-of-lifed (though I don't know if that was very formal back then). 7.3 will be E-O-L'd soon (with 8.2's release, I think), and I don't think 7.4 will be far behind. So you actually need to start looking at moving off of 7.4 as well. And of course you should always upgrade to newer point releases when they come out (like they did today). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461