Обсуждение: db-recovery after update 7.1 -> 7.4 failed

Поиск
Список
Период
Сортировка

db-recovery after update 7.1 -> 7.4 failed

От
Dominique Fenenr
Дата:
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

От
Oliver Elphick
Дата:
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

От
"Bryan Encina"
Дата:
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