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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
