Re: db-recovery after update 7.1 -> 7.4 failed

On Mon, 2003-12-01 at 09:35, Dominique Fenenr wrote:
> Hi all,
>
> I recently upgraded postgresql from 7.1 to 7.4. I did a
>
> pg_dumpall > backup
>
> then installed the new version and tried to recover my data with
>
> psql template1 <backup
>
> But something went wrong. The log shows the following:
...
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "phpbb_words_pkey" for table "phpbb_words"
> ERROR:  invalid input syntax for integer: "
>         "
> CONTEXT:  COPY phpbb_posts_text, line 2, column post_id: "
>         "
...
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "netjuke_plists_fav_pkey" for table "netjuke_plists_fav"
> ERROR:  literal carriage return found in data
> HINT:  Use "\r" to represent carriage return.
> CONTEXT:  COPY netjuke_tracks, line 3312: "3196 940     335     4
> 21 Things I Want In A Lover 4991999
> 207     1       2002    2003-05-16 21:24:08+02  192     44100
> MPEG ..."
>
> "phpbb_posts_text" and "netjuke_tracks" don't have no rows anymore which
> is pretty bad. I had a look at the backup-file, the data is still
> there. Do I have any chance to recover the data? Perhaps by
> manipulating the backup file?

Eidt the backup file first (since you have the line numbers in it) to
correct the errors.  Then extract the two sets of data for those two
files and the associated COPY commands to a separate file and import it
in just the same way as you did the whole backup.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Who is like unto thee, O LORD, among the gods? who is
      like thee, glorious in holiness, fearful in praises,
      doing wonders?"             Exodus 15:11