Forum OpenACS Q&A: Upgrading to postgresql 8.2

I just found out the hard way that upgrading to 8.2 (or I think even 8.1) can be a challenge if you are using UTF-8, as versions prior to 8.1 allowed invalid UTF-8 characters.

This will result in errors like:

invalid byte sequence for encoding "UTF8"

To solve this run your dumpfile through iconv:

iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql dumpfile.sql

as desribed at http://www.postgresql.org/docs/current/static/release-8-1.html (right before chapter 7.3).

Furthermore the import might fail because of a bug where tsearch2 is storing invalid UTF8 characters prio to postgresql 8.2 (http://www.dbtalk.net/mailing-database-pgsql-bugs/re-bugs-copy-fails-8-a-341028.html)

If the database contains blobs the plain text dump doesn't work. Actually I use the following commands to dump and restore my databases:

export dbname=mydb
pg_dump $dbname -b -Fc -f /tmp/$dbname.dmp
pg_restore -d $dbname -v -O -Fc /tmp/$dbname.dmp >& /tmp/$dbname.log

and so I don't see how to process the dump file with 'iconv'.