Well here’s a curly issue I blundered into today. I was trying to replicate a Postgres database from an old server to a new one using pg_dump/pg_restore and found some errors like this:
invalid byte sequence for encoding "UTF8": 0x92
After some investigation it turns out the older version of Postgres defaults to ASCII encoding of the data wheras the new version of Postgres defaults to UTF-8. Of course I would much prefer to use UTF-8 but shouldn’t those two encodings be compatible anyway? UTF-8 is supposed to be backward-compatible with ASCII.
What happened is a few non-ASCII characters got into the database. 0×92 is not a valid ASCII code. It is a code from Latin-1 encoding. This was in the database thanks to my business partner who was entering some crap into the database using a Macintosh which wasn’t following the ASCII standard so much.
So how to solve it?
First I did a plain-text export of the database on the old server
pg_dump -Fp my_database > mydatabase.sql
Then I converted the encoding to UTF-8
iconv -f 8859_1 -t UTF-8 mydatabase.sql> mydatabase.utf8.sql
Finally I imported the converted data into the database on the new server
createdb my_database psql my_database -f mydatabase.utf8.sql
And then I wrote a blog post so I’d remember all this next time the issue comes up.