Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am moving some databases from Postgres 7.4.8 to 8.4.5, on CentOS 5. In the old database the encoding is UNICODE. So I did a text pg_dump, created my new databases like so:

createdb --template template0 --encoding unicode testdb

and imported the data:

psql testdb < testdb.dmp >& testdb.out

Unfortunately, these errors are sprinkled throughout:

ERROR: invalid byte sequence for encoding "UTF8": 0x8e

I know that iconv is the tool most often recommended to fix this, but I'm not sure what the conversion is. I tried running it from Unicode to UTF8, since that is what Postgres reports for my new database, but that produced a file full of what looked like Chinese characters. I also tried UTF8 to UTF8; that chokes on the first of the offending characters with this:

iconv: illegal input sequence at position 874924

I'd like to know a) why am I having problems when I'm not changing the character set? And b) is there any way of fixing this short of editing the dump file by hand?

Thanks!

share|improve this question
add comment

1 Answer 1

For posterity... it turned out that (I think) I had a mix of several different character sets in the data. So what I ended up doing was first running

iconv -f macintosh -t utf-8

To fix the accented a characters and others that were causing problems on the import, and then doing manual search and replace to get rid of all the Windows smart characters, which got mangled by the above conversion. After that, everything imported cleanly into Postgres and looked ok. Not the best solution, but the only thing I could figure out that worked.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.