Re: Change server encoding after the fact

Thanks Scott. See below:

On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan <toolbag(at)gmail(dot)com> wrote:
> > I would like to change my server_encoding which is currently SQL_ASCII to
> UTF8.
> >
> > I have existing data that I would like to keep.
> >
> > From my understanding of the steps I need to:
> >
> > 1) alter the template1 database encoding via
> >
> > UPDATE pg_database SET encoding = 6 where datname IN ('template0',
> 'template1');
>
> Just create database using template0 as template and you can skip this step
> ^^
>

Wouldn't this only work if my template0 was UTF8 itself?

=> select datname, pg_encoding_to_char(encoding) from pg_database;
datname | pg_encoding_to_char
----------------------+---------------------
template1 | SQL_ASCII
template0 | SQL_ASCII
postgres | SQL_ASCII

So it appears both template0 & template1 are SQL_ASCII, so how would
creating from a new DB from template0 be any different than template1?

> > Are these the correct steps to perform or is there an easier / in-place
> way?
>
> > Also, when I dump my old DB and restore it, will it be converted
> appropriately (e.g. it came from am SQL_ASCII encoding and its going into a
> UTF-8 database)?
>
> You might need to set client encoding when restoring. Or use iconv to
> convert from one encoding to another, which is what I usually do.
> Note that it's VERY likely you'll have data in a SQL_ASCII db that
> won't go into a UTF8 database without some lossiness.
>

Yes, I see this might be the case. From my playing around with iconv I
cannot even properly do the conversion:

$ pg_dump -Fp foo > foo.sql
$ file -i foo.sql
foo.sql: text/plain; charset=us-ascii
$ iconv -f utf-8 foo.sql > utf8.sql
iconv: illegal input sequence at position 2512661

Uh oh... I cannot event convert it?

Whats my next step at this point if I cannot even convert my data? I'd be OK
with some lossiness.

Thanks again
/Cody