Re: Change server encoding after the fact

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

> On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan <toolbag(at)gmail(dot)com> wrote:
> > Please see below.
> >
> > On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
> >
> > wrote:
> >>
> >> On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan <toolbag(at)gmail(dot)com>
> wrote:
> >> > 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?
> >>
> >> Well, let's try, shall we? From a freshly created cluster on my
> >> laptop, running 8.4:
> >>
> >> smarlowe=# select datname, pg_encoding_to_char(encoding) from
> pg_database;
> >> datname | pg_encoding_to_char
> >> -----------+---------------------
> >> template1 | SQL_ASCII
> >> template0 | SQL_ASCII
> >> postgres | SQL_ASCII
> >> smarlowe | SQL_ASCII
> >> (4 rows)
> >>
> >> smarlowe=# create database j template template0 encoding 'UTF8';
> >> CREATE DATABASE
> >>
> >> Seems to work.
> >>
> >> P.s. I'm not sure why it works, I just know that it does. :)
> >>
> >
> > Ok, I see what you mean. This would create a new DB with the proper
> > encoding. Which is "fine", and probably what I will do. I guess I see an
> > ideal scenario being one where we permanently convert the template
> encoding
> > to UTF8 so going forward I dont have to worry about forgetting to adding
> the
> > encoding= 'UTF8' for every new DB I create.
>
> Ah ok. The way I fix that is this:
>
> update pg_database set datistemplate = false where datname='template1';
> drop database template1;
> create database template1 template template0 encoding 'UTF8';
>
> But your way would likely work too.
>
> >> I think you got it backwards, the -f should be somthing other than
> >> utf-8 right? That's what the -t should be right? Try iconv without a
> >> -f switch and a -t of utf-8 and see what happens...
> >
> > You're right, I had -f when I needed -t. I tried it again with the same
> > error:
> > $ iconv -t utf-8 foo.sql > utf.sql
> > iconv: illegal input sequence at position 2512661
>
> Any idea waht the actual encoding of your source database is?
> SQL_ASCII is basically not really ascii, more like anything goes.
>

How would I find this? pg_database says my DB is SQL_ASCII.

"show all" says

client_encoding = SQL_ASCII
server_encoding = SQL_ASCII