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

From: Bianchi Quota Leonardo <leonardo(dot)bianchiquota(at)insiel(dot)it>
To: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Chars problem restoring to ps 8.4 (utf8) a dumped db from ps 8.1 (latin9)
Date: 2015-08-12 13:46:49
Message-ID: D2711A0677B74649872C6AB7603979E980DA718F@FVG-EMB-01V-TS.UCFVG.hosted
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)
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!

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-08-12 14:00:07 Re: Chars problem restoring to ps 8.4 (utf8) a dumped db from ps 8.1 (latin9)
Previous Message Shashank 2015-08-12 03:43:11 I am unable to install PostgreSql