Thanks Scott. See below:
On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe <scott.marl...@gmail.com>wrote:
> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan <tool...@gmail.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
