ADMIN Migrate postgres databases from SQL_ASCII to UNICODE
Published on 08/05,2004
from : http://www.mail-archive.com/pgsql-admin@postgresql.org/msg14324.html
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) export your database. /usr/local/postgres/bin/pg_dump -Fc -v -b -d -U YOUR_LOGIN YOUR_DATABASE > full_database.dump
2) create work database, without blobs.
/usr/local/postgres/bin/createdb db_without_blobs
3) import all datas
/usr/local/postgres/bin/pg_restore -d db_without_blobs -Fc -v -U YOUR_LOGIN full_database.dump
4) create work database, with blobs.
/usr/local/postgres/bin/createdb db_with_blobs
5) import all datas.
/usr/local/postgres/bin/pg_restore -d db_with_blobs -Fc -v -U YOUR_LOGIN full_database.dump
6) Connect to db_without_blobs and drop all tables with blobs.
6') Connect to db_with_blobs and drop all tables without blobs.
7) export datas in text mode :
/usr/local/postgres/bin/pg_dump -Fp -v -i -D -U YOUR_LOGIN db_without_blobs > db_without_blobs.dump
8) convert into UTF-8 iconv --from-code=ISO-8859-1 --to-code=UTF-8 -o db_without_blobs-utf.dump db_without_blobs.dump
9) Create unicode database.
/usr/local/postgres/bin/createdb -E UNICODE db_unicode
10) importat UTF-8 datas
/usr/local/postgres/bin/psql db_unicode < db_without_blobs.dump
11) export datas with blobs in binary format.
12) import this dump info db_unicode .
Best Regards,
Patrice Trognon.