Re: upgrade to 8.4 with new character set

Kevin Duffy wrote:
>
>
>
> We originally built our database with a character set of SQL_ASCII.
> We now know the error of our foolish ways. We are trying to upgrade
> to 8.4 and the windows binaries would like us to use UTF8. I would
> like to use a backup to restore onto the new installation, however I
> am getting warnings and errors about how I am trying to restore a
> SQL_ASCII character set to a UTF8 Database. I need to have confidence
> that my data is intact before I can continue with the upgrade. I was
> wondering what would be the best course of action. I currently have
> the 8.4 database installed on a separate machine for testing.
>
>
>
> Thank you for all your help
>
>
>
> Jonathan Schindler
>
This is what I did:

Download & install iconv for windows from gnuwin32.sourceforge.net

Backup the 8.3 database using the plain format
e.g. pg_dump -F p -f backup.ascii -U postgres mydb

Run Iconv on the backup to convert to your new dataset (you may need to
use a different source encoding - but ISO-8859-1 works for me!)
e.g. c:\program files\gnuwin32\bin\iconv.exe -f ISO-8859-1 -t UTF-8
backup.ascii > backup.utf-8

Create a new db in UTF-8 encoding (newdb)

Now restore to your 8.4 database
e.g. psql -U postgres -f backup.utf-8 newdb

The only problem I've had so far is that some of my data was a streamed
xml file into a text variable, which stupidly had some byte order marks
at the beginning which became nonsense after converting to UTF-8. These
were removed with a SQL query but hopefully this shouldn't happen to
most people.

Hope this helps.

Howard Cole
www.selestial.com