Postgres Pro
Downloads
Home   >   mailing lists

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

db-recovery after update 7.1 -> 7.4 failed

From
Dominique Fenenr
Date:
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
"artists_pkey" for table "artists"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"albums_pkey" for table "albums"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"songs_pkey" for table "songs"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"stats_pkey" for table "stats"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_auth_access_pkey" for table "phpbb_auth_access"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_groups_pkey" for table "phpbb_groups"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_banlist_pkey" for table "phpbb_banlist"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_categories_pkey" for table "phpbb_categories"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_config_pkey" for table "phpbb_config"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_disallow_pkey" for table "phpbb_disallow"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_forums_pkey" for table "phpbb_forums"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_forum_prune_pkey" for table "phpbb_forum_prune"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_posts_pkey" for table "phpbb_posts"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_posts_text_pkey" for table "phpbb_posts_text"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_privmsgs_pkey" for table "phpbb_privmsgs"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_privmsgs_text_pkey" for table "phpbb_privmsgs_text"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_ranks_pkey" for table "phpbb_ranks"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_search_results_pkey" for table "phpbb_search_results"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_search_wordlist_pkey" for table "phpbb_search_wordlist"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_session_pkey" for table "phpbb_sessions"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_smilies_pkey" for table "phpbb_smilies"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_themes_pkey" for table "phpbb_themes"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_themes_name_pkey" for table "phpbb_themes_name"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_topics_pkey" for table "phpbb_topics"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_users_pkey" for table "phpbb_users"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"phpbb_vote_dsc_pkey" for table "phpbb_vote_desc"
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_artists_pkey" for table "netjuke_artists"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_albums_pkey" for table "netjuke_albums"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_genres_pkey" for table "netjuke_genres"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_tracks_pkey" for table "netjuke_tracks"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_groups_pkey" for table "netjuke_groups"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_users_pkey" for table "netjuke_users"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_userprefs_pkey" for table "netjuke_userprefs"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_sessions_pkey" for table "netjuke_sessions"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_plists_pkey" for table "netjuke_plists"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"netjuke_plists_tracks_pkey" for table "netjuke_plists_tracks"
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?

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

From
Oliver Elphick
Date:
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

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

From
"Bryan Encina"
Date:
Hi Dominique,

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

Sounds like a problem I had a week or two ago that I posted to the novice
list.  Here's a link to how I got it resolved.
http://archives.postgresql.org/pgsql-novice/2003-11/msg00158.php
HTH,
-b