Обсуждение: db-recovery after update 7.1 -> 7.4 failed
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?
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
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