Search Postgresql Archives

Re: Change server encoding after the fact

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks Scott. See below:

On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan <toolbag@xxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux