X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 37465D1B170 for ; Sun, 18 Jul 2004 07:30:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90174-01 for ; Sun, 18 Jul 2004 10:31:03 +0000 (GMT) Received: from web11.manitu.net (web11.manitu.net [217.11.48.111]) by svr1.postgresql.org (Postfix) with ESMTP id 71EF8D1B178 for ; Sun, 18 Jul 2004 07:30:53 -0300 (ADT) Received: from [192.168.0.1] (dsl-082-082-213-253.arcor-ip.net [82.82.213.253]) (authenticated) by web11.manitu.net (8.10.2-SOL3/8.10.2) with ESMTP id i6IAUla30481; Sun, 18 Jul 2004 12:30:48 +0200 Subject: Re: Migrate postgres databases from SQL_ASCII to UNICODE From: Markus Bertheau To: "Dario V. Fassi" Cc: pgsql-admin@postgresql.org In-Reply-To: <40F9B776.9050601@sistemat.com.ar> References: <40F9B776.9050601@sistemat.com.ar> Content-Type: text/plain; charset=UTF-8 Message-Id: <1090146652.2741.1.camel@teetnang> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Sun, 18 Jul 2004 12:30:52 +0200 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/203 X-Sequence-Number: 14271 =D0=92 =D0=92=D1=81=D0=BA, 18.07.2004, =D0=B2 01:34, Dario V. Fassi =D0=BF= =D0=B8=D1=88=D0=B5=D1=82: > Tom Lane wrote: > > "Dario V. Fassi" writes: > >=20=20=20 > > > A simple question, we need to migrate many (>20) postgres databases f= rom=20 > > > SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server. > > >=20=20=20=20=20 > > SQL_ASCII is not an encoding (it's more like the absence of knowledge > > about an encoding). What is the data actually stored as? > >=20=20=20 > > > With Dump/Restore , we get an error (Invalid Unicode) in any field t= hat=20 > > > has a 8 bits character coming from the SQL_ASCII , even setting the= =20 > > > client_encoding to WIN, ISO-8859-1, and others encodings. > > >=20=20=20=20=20 > > It might work to just UPDATE pg_database to set datencoding to the > > correct value reflecting what you have actually stored. You might then > > need to REINDEX any indexes on textual columns, but I don't think > > anything else would go wrong. > >=20 > > If you have a mishmash of different encodings in a single database, then > > of course there is no simple solution; you are in for some pain while > > you try to fix the data. > >=20=20=20 >=20 > Yes you are right , the original data come from a DB2 with CodePage > IBM-850 and was inserted without complains in a Postgres 7.3.6 with > SQL_ASCII. >=20 > Now we are in a Jail , because IBM-850 , isn't WIN, isn't ISO-xx , > isn't no one postgresql's encoding.=20 > So when in change via pg_databases the encoding , 8 bits characters > become garbage. > More even if we accept this garbage chars and we set encoding to e.g. > ISO-8859-1 it's impossible go to a UNICODE because this garbage > chars are invalid in client's encoding , so they are reject (in > translation process as invalid unicode chars). You can dump the data, convert it with for example iconv and then load the data again. --=20 Markus Bertheau