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

Hi John,

" 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."

Question: How can I pull out the real character set information from the database? does it rely on the understanding of business knowledge?

so, the real data stored in database decides if we need to use iconv to convert them to UTF-8. If data is USASCII, then the pg_dump/restore process you provided should be sufficient and complete. Do I understand this correctly?

Besides, we do have different encoding database on same server instance, see below:

Welcome to psql 8.3.11, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# \l
List of databases
Name | Owner | Encoding
-----------------------------+----------+-----------
admin | dba | UTF8
pgdb_1_sky | pgdb_1 | SQL_ASCII
pgdb_1_sky_utf8 | pgdb_1 | UTF8
pgdb_1_ca | pgdb_1 | SQL_ASCII
pgdb_1_us | pgdb_1 | SQL_ASCII
pgdb_sky | pgdb | SQL_ASCII
pgdb_sky_users | pgdb | SQL_ASCII
pgdb_sky_users_utf8 | pgdb | UTF8
pgdb_sky_utf8 | pgdb | UTF8
pgdb_sky_utf8_86465_old | pgdb | UTF8

Thanks,
Suya
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of John R Pierce
Sent: Friday, October 18, 2013 4:12 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [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

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general