Thread: Migrating from 7.1 to 7.2 using pg_dump/pg_restore fails - UNICODE error

Hi All,

I am upgrading my server 7.1 (a debian potato) to a new server 7.2 (a
debian woody).

On the 7.1 server I had a script that took a backup of my databases
each day using pg_dump:

/usr/local/pgsql/bin/vacuumdb -z -h localhost -U postgres $i
>/dev/null 2>&1
/usr/local/pgsql/bin/pg_dump -b -Fc -Z9 $i -h 127.0.0.1 >
"$backup_dir/$katalog/$mappe/postgresql-$i-$timeslot-database.db"

I can restore the *.db files without any problems on the 7.1. But
when I move the *.db files to the 7.2 I get the following error:

pg_restore: ERROR:  copy: line 1, Invalid UNICODE character sequence
found (0xf87665)

It is the same even if I use INSERT instead of COPY. I have no clue
that went wrong?

During a search on the internet I have seen some suggestions where I
can change the *.db file to include an
\encoding UNICODE command but this fails because I use the customer
format I guess.

No need to say that the new database is created with UNICODE
encoding?!

Regards,

Søren Laursen

В Вск, 06.07.2003, в 19:57, Soeren Laursen пишет:

> pg_restore: ERROR:  copy: line 1, Invalid UNICODE character sequence
> found (0xf87665)

> No need to say that the new database is created with UNICODE
> encoding?!

use

\encoding latin1

once when you import the data. That'll convert the data from latin1 to
utf-8 encoded unicode on the fly. Then again I don't know what charset
denmark uses usually, probably some other latin variant.

--
Markus Bertheau.
Berlin, Berlin.
Germany.

Hi,

I am not sure how and where I should use the \encoding latin1

Something like,

psql test01
\encodinng latin1
\q

pg_restore .....

Regards

Søren,

> В Вск, 06.07.2003, в 19:57, Soeren Laursen пишет:
>
> > pg_restore: ERROR:  copy: line 1, Invalid UNICODE character sequence
> > found (0xf87665)
>
> > No need to say that the new database is created with UNICODE
> > encoding?!
>
> use
>
> \encoding latin1
>
> once when you import the data. That'll convert the data from latin1 to
> utf-8 encoded unicode on the fly. Then again I don't know what charset
> denmark uses usually, probably some other latin variant.
>
> --
> Markus Bertheau.
> Berlin, Berlin.
> Germany.

В Пнд, 07.07.2003, в 00:01, Soeren Laursen пишет:
> Hi,
>
> I am not sure how and where I should use the \encoding latin1
>
> Something like,
>
> psql test01
> \encodinng latin1
> \q
>
> pg_restore .....

Ah. Load the backup in a non-unicode database, dump it using the text
format (i.e. not the custom format), put the encoding thing in front of
the file and load it into your unicode database.

--
Markus Bertheau.
Berlin, Berlin.
Germany.

It looked like that worked,

How about large objects (blobs). That is the reason I use custom
format or am I missing a point here?

I guess I might could use a lot of switches and only dump the
blobs in a single file?

Regards,

Søren,
> В Пнд, 07.07.2003, в 00:01, Soeren Laursen пишет:
> > Hi,
> >
> > I am not sure how and where I should use the \encoding latin1
> >
> > Something like,
> >
> > psql test01
> > \encodinng latin1
> > \q
> >
> > pg_restore .....
>
> Ah. Load the backup in a non-unicode database, dump it using the text
> format (i.e. not the custom format), put the encoding thing in front of
> the file and load it into your unicode database.
>
> --
> Markus Bertheau.
> Berlin, Berlin.
> Germany.

В Пнд, 07.07.2003, в 10:12, Soeren Laursen пишет:

> It looked like that worked,
>
> How about large objects (blobs). That is the reason I use custom
> format or am I missing a point here?
>
> I guess I might could use a lot of switches and only dump the
> blobs in a single file?

I'm sorry, someone else will have to follow up on blobs, I have never
used them :/

--
Markus Bertheau.
Berlin, Berlin.
Germany.