Re: [ADMIN] what's the efficient/safest way to convert database character set ?

On 10/17/2013 9:49 PM, Huang, Suya wrote:
> Yes John, we probably will use a new database server here to accommodate those converted database.
>
> By saying export/import, do you mean by :
> 1. pg_dump (//should I specify -E UTF 8 to dump the data in UTF-8 encoding?)
> 2. create database xxx -E UTF8
> 3. pg_restore

I don't believe 8.3 supported multiple different encodings on the same
server instance, thats relatively new.

before you can import your SQL_ASCII data, you need to know what charset
the data is actually in. Is it UTF8 data stored in SQL_ASCII or is it
LATIN1 (ISO-8859) ? or some sort of Big5 or euc_cn ? or what? if it is
already UTF8 data, are you sure that there are no invalid encodings
accidentally stored? Postgres with SQL_ASCII does no character
validation... if its all USASCII (0x00 to 0x7F) then you're OK.

I would strongly recommend this new database server be running a
currently supported version, I'd probably use 9.2. configure the old
server to allow the postgres user on the new server to connect and log
on, and while logged onto the new server, run something like...

pg_dump -h oldserver -d olddatabase -u postgres -E oldencoding -s
-f olddatabase.schema.sql
pg_dump -h oldserver -d olddatabase -u postgres -E oldencoding -a
-f olddatabase.data.sql
createuser newuser
createdb -O newuser -l en_US.utf8 newdbname
psql -d newdbname -u newuser -f olddatabase.schema.sql
psql -d newdbname -u newuser -f olddatabase.data.sql

if the data import fails due to a invalid encoding, then you may have to
pass the .data.sql file through iconv (and remove the set
client_encoding sql commands from it)

--
john r pierce 37N 122W
somewhere on the middle of the left coast