Re: Change server encoding after the fact

Thanks y'all for your help on this.

I took this opportunity to upgrade to 9.1.1 which is UTF8 by default and I
ended up manually cleaning up the borked data by hand (there wasn't that
much).

So all is well now.

Thanks again.

/Cody

On Fri, Sep 30, 2011 at 3:37 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Fri, Sep 30, 2011 at 2:57 PM, Cody Caughlan <toolbag(at)gmail(dot)com> wrote:
> > Please see below.
> >
> > On Fri, Sep 30, 2011 at 1:12 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> > wrote:
> >>
> >> On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan <toolbag(at)gmail(dot)com>
> wrote:
> >> > That worked, but "file" shows no difference:
> >> > $ iconv -f utf-8 -t utf-8 -c foo.sql > utf.sql
> >> > $ file -i foo.sql
> >> > foo.sql: text/plain; charset=us-ascii
> >> > $file -i utf.sql
> >> > utf.sql: text/plain; charset=us-ascii
> >> > So iconv didnt actually convert the file OR does is the "file" command
> >> > just
> >> > ignorant?
> >>
> >> Not sure. try loading the dump into the UTF-8 DB in postgres and see
> >> what happens I guess?
> >
> >
> > Uh oh.
> > On the remote machine:
> > $ pg_dump -Fc -E UTF8 foo > foo.sql
> > Then I've created a new local DB with UTF8 encoding and I try to restore
> > this dump into it:
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 2342; 0 17086 TABLE DATA
> > wine_books vinosmith
> > pg_restore: [archiver (db)] COPY failed for table "wine_books": ERROR:
> > invalid byte sequence for encoding "UTF8": 0xc309
> > CONTEXT: COPY wine_books, line 1147
> > WARNING: errors ignored on restore: 1
> > And sure enough the table "wine_books" is empty. Not good.
>
> You may have to hunt down that one bad line (1147) and chop it out /
> edit it so it works.
>