When I do migration from Mysql to PostgreSQL:
firstly dump data from mysql in cmd(encoding is GBK) is WIN8:
mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql --default-character-set=utf8 --skip-add-locks --compact --no-create-info --skip-quote-names -uroot -p test >dbdata.sql
then load data to postgresql in cmd(encoding is GBK) is WIN8:
psql -h localhost -d test -U postgres < dbdata.sql
I got the error:
ERROR: invalid byte sequence for encoding "UTF8": 0xff
I checked in dbdata.sql using UltraEdit, data "0xff" really exists as followings:
蛾4?4抿\0xfF???±??x¤?
and I use UltraEdit menu-> view -> encoding page to get: 936 GBK.
DDL in Mysql 5.5 is:
CREATE TABLE `personpicture` (
`ID` char(32) NOT NULL,
`Picture` mediumblob,
...
KEY `personId` (`PersonID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DDL in PostgreSQL 9.1 is:
create database test encoding 'utf8';
CREATE TABLE personpicture (
ID char(32) NOT NULL,
Picture BYTEA,
....
PRIMARY KEY (ID)
);
The error is related to Picture mediumblob and BYTEA, which is stored pic binary data.
My Operationg system is WIN8 64bit.
I've set postgresql.conf before starting everything, but error still occurs:
backslash_quote = on
escape_string_warning = off
standard_conforming_strings = off
How to resolve it?
Thanks!
peng