Re: v8.3 + UTF8 errors when restoring DB

["Dean Gibson (DB Administrator)" <postgresql@xxxxxxxxxxxx>]

On 2008-03-09 11:49, Mitchell D. Russell wrote:

Dean:

 

I did the dump as so:  psql –Upostgres databasename > c:\temp\dump.sql

I assume you meant pg_dump, not psql.

 

I think the database was set to SQL_ASCII before I dumped it, because when I did the 2^nd restore last night to a new SQL_ASCII database, it worked fine, so that’s where I am at right now, the database is running fine on 8.3 as SQL_ASCII but I don’t want to keep it like that.

 

So what do you suggest I do now? Make another 8.3 database in UTF8 and dump the 8.3 SQL_ASCII one using –E with UTF8? What will it do to the characters that can’t be interpreted?

 

I don't think your idea will work.

Here's what I would try:  Your 'dump.sql' file is an ASCII file, which you can edit.  Hopefully you have an editor that can handle it (or if on Linux, you can use the 'sed' program to do what I'm about to suggest):

The file should contain a "SET client_encoding = SQL_ASCII" line near the beginning.  Make a copy of that file, and change that line to "SET client_encoding = WIN1252" in the copied file, and then try importing the modified dump file into a UTF-8 database.

If that doesn't work, I have another suggestion if you are running on Linux.

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.