Re: Change server encoding after the fact

Its a Rails app and I do have:

encoding: utf8

Set in my DB configuration.

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

> On Fri, Sep 30, 2011 at 1:35 PM, Cody Caughlan <toolbag(at)gmail(dot)com> wrote:
> >
> >
> > 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
>
> It would have been set by the application accessing postgresql and
> inserting the data. I.e. was it a windows app using a typical windows
> encoding? etc.
>