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