Thread: To recover data corrupted

To recover data corrupted

From
Frederic Massot
Date:
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===

Re: To recover data corrupted

From
"Jim C. Nasby"
Date:
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

Re: To recover data corrupted

From
Christopher Browne
Date:
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

Re: To recover data corrupted

From
Frederic Massot
Date:
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===

Re: To recover data corrupted

From
"Jim C. Nasby"
Date:
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