how to dump and restore unicode databases? - Mailing list pgsql-admin

From Andreas Schuldei
Subject how to dump and restore unicode databases?
Date
Msg-id 20021217224818.GA10840@lukas
Whole thread Raw
List pgsql-admin
i have two machines, one with postgres 7.2.X and one with 7.3.
Both their encodings are set to UNICODE.
on the first i have crated a unicode database, with german,
swedish and english texts, more to come. on that machine i dump
the db with pg_dump -c -s ncg and pg_dump -D -a ncg. on that
machine i can restore the structure and data of the database just
fine.

I also edit the dump to use the coziness of my texteditor to add
larger amounts of text. i can restore those edited dumps just fine.

then i try to restore that database on the postgres 7.3. since i
get errors like
ERROR:  Invalid UNICODE character sequence found (0xe4636b)
whenever a german or swedish special char occurs, i insert an
\ecoding LATIN1 before the critical sections.

then i can restore the complete database, with the difference
that the special characters are garbage: ö and similar.

How can i dump unicode databases to asci so that they are
restored as unicode? i would like to edit the dump, as explained
above.

or

How can i restore the database with its special chars in tact?

pgsql-admin by date:

Previous
From: "King_Henree@yahoo.com"
Date:
Subject: How to determine which long running session???
Next
From: "Ing. Gabriel Monsalvo"
Date:
Subject: Slow Executing queries