Re: Chars problem restoring to ps 8.4 (utf8) a dumped db from ps 8.1 (latin9)

On 08/12/2015 06:46 AM, Bianchi Quota Leonardo wrote:
> Hi, I'm trying to move a db from postgres 8.1 encoded LATIN9 from a debian 4.0 box to postgres 8.4 encoded UTF8 on a rh6.6 (the whole job is to dismiss the old server, migrate and upgrade bugzilla application)

FYI, 8.4 is no longer community supported. The oldest supported version
is 9.0 and its support will in September. See here for more details:

http://www.postgresql.org/support/versioning/

> I would like to restore dumped data in the new utf8 db solving the problem of chars like "è,à,é,ò,ù" which are not seen by the application correctly. About the application I can say it is set for using utf8.
> About my skills, I have very little experience with db and postgres.
>
> I "SOLVED" it doing this way but don't know what I did and I don't know which consequences would have in future, then I need to know if it's ok...
>
> Starting on BOX1
> $pg_dump --no-privileges --no-owner -h localhost -U bugs -f DB.sql (dump in latin9)
>
> $vi DB.sql and changed the first string with the last.
>> SET client_encoding = 'LATIN9';
> <SET client_encoding = 'utf8';
>
> moved via scp to box2
> $scp DB.sql 172.25.98.141:/home/deploy
>
> on BOX2
> via psql create new db "bugzilla-database"
> $psql bugzilla-database < DB.sql
>
> Then browsing the restored data via the web application everything is ok...
> -
> Here below I put few notes, it may can help:
>
> DB is not big (bugzilla bugs table contains ~5000records) and dump file not compressed (no -Fc) is about 450MB
> BOX1 debian variables LANG=it_IT(at)euro LC_CTYPE="it_IT(at)euro" (which means LATIN9)
> BOX2 redhat variables LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8"
> ---------------------------------------------
> example of words seen via "vi" on debian (the words are: attività = activity ; è = è ; l'attuale = the current)
> LATIN9 dump file: attivitÃ| è lâ~(at)~Yattuale
> UTF8 dump file: attivitÃ~CÂ| Ã~CÅ¡ lâÂ~(at)Â~Yattuale
> UTF8 ...and on RH ie "è" is ÃÅ¡
> ----------------------------------------------
> Other notes:
> - I tryed pg_dump using --encoding UTF8 and -Fc and -f (to avoid redirect) but it wasn't successful.
> - I successfully dumped in Latin9, create a db in latin9 and restore dumped file (meaning I could see correctly the chars via the application)
>
> I tried to be concise (hope not too much).
>
> Thank you in advance for any help!

The reason this:

SET client_encoding = 'LATIN9';

worked can be found here:

http://www.postgresql.org/docs/8.4/interactive/multibyte.html

Postgres can convert latin9 to UTF8, if it knows that is what it getting.

>
> Bye,
> Leonardo
>
> AVVISO DI RISERVATEZZA Informazioni riservate possono essere contenute nel messaggio o nei suoi allegati. Se non siete i destinatari indicati nel messaggio, o responsabili per la sua consegna alla persona, o se avete ricevuto il messaggio per errore, siete pregati di non trascriverlo, copiarlo o inviarlo a nessuno. In tal caso vi invitiamo a cancellare il messaggio ed i suoi allegati. Grazie. CONFIDENTIALITY NOTICE Confidential information may be contained in this message or in its attachments. If you are not the addressee indicated in this message, or responsible for message delivering to that person, or if you have received this message in error, you may not transcribe, copy or deliver this message to anyone. In that case, you should delete this message and its attachments. Thank you.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com