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 71483D1B23F for ; Sun, 18 Jul 2004 08:26:17 -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 03923-07 for ; Sun, 18 Jul 2004 11:26:21 +0000 (GMT) Received: from fw24.wanadoo.fr (ASte-Genev-Bois-151-2-3-89.w82-121.abo.wanadoo.fr [82.121.13.89]) by svr1.postgresql.org (Postfix) with ESMTP id 6A154D1B1B3 for ; Sun, 18 Jul 2004 08:26:11 -0300 (ADT) Received: from ptrognon by fw24.wanadoo.fr with local (Exim 3.36 #1 (Debian)) id 1Bm9XW-0001pS-00; Sun, 18 Jul 2004 13:09:06 +0200 Content-Type: text/plain; charset="iso-8859-1" Content-Disposition: inline Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Mailer: Manitou v0.9.3 From: "Patrice Trognon" Subject: Re: ADMIN Migrate postgres databases from SQL_ASCII to UNICODE To: "Markus Bertheau" Cc: "Dario V. Fassi" , pgsql-admin@postgresql.org In-Reply-To: <1090146652.2741.1.camel@teetnang> Date: Sun, 18 Jul 2004 13:27:52 +0200 Message-Id: <20040718132752.6475772@devptro.(none)> 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/204 X-Sequence-Number: 14272 Hi, If you have blobs you must split your SQL_ASCII to convert it with iconv. I export my full database. I create a new SQL_ASCII database with only tables that don't contains blobs. (db_without_blobs) I create a new SQL_ASCII database with only tables that contains blobs. (db_with_blobs) Export your db_without_blobs. convert it with iconv Create your UNICODE database. import db_without_blobs. import db_with_blobs. It's done. 1)=A0export=A0your database.=20 /usr/local/postgres/bin/pg_dump=A0-Fc=A0-v=A0-b=A0-d=A0-U=A0YOUR_LOGIN YOUR= _DATABASE=A0> full_database.dump=20 2)=A0create work database, without blobs. /usr/local/postgres/bin/createdb=A0db_without_blobs 3)=A0import all datas /usr/local/postgres/bin/pg_restore=A0-d=A0db_without_blobs -Fc=A0-v=A0-U=A0= YOUR_LOGIN full_database.dump=20 4)=A0create work database, with blobs. /usr/local/postgres/bin/createdb=A0db_with_blobs 5)=A0import all datas. /usr/local/postgres/bin/pg_restore=A0-d=A0db_with_blobs -Fc=A0-v=A0-U=A0YOU= R_LOGIN full_database.dump=20 6)=A0Connect to db_without_blobs and drop all tables with blobs. 6') Connect to db_with_blobs and drop all tables without blobs. 7)=A0export datas in text mode : /usr/local/postgres/bin/pg_dump=A0-Fp=A0-v=A0-i=A0-D=A0-U=A0YOUR_LOGIN db_= without_blobs > db_without_blobs.dump=20 8)=A0convert into=A0UTF-8=20 iconv=A0--from-code=3DISO-8859-1=A0--to-code=3DUTF-8=A0-o=A0db_without_blob= s-utf.dump db_without_blobs.dump=20 9)=A0Create unicode database. /usr/local/postgres/bin/createdb=A0=A0-E=A0UNICODE=A0db_unicode 10)=A0importat UTF-8 datas /usr/local/postgres/bin/psql=A0db_unicode < db_without_blobs.dump=20 11) export datas with blobs in binary format. 12) import this dump info db_unicode . Best Regards, Patrice Trognon. > =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= from=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= that=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 th= en > > > 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, t= hen > > > 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). >=20 > You can dump the data, convert it with for example iconv and then load > the data again. >=20 > --=20 > Markus Bertheau >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >=20