Re: Easy way to convert a database from WIN1252 to UTF8?

On Thu, Jul 1, 2010 at 10:22 AM, Justin Graf <justin(at)magwerks(dot)com> wrote:
>
>
> On 7/1/2010 11:08 AM, Mike Christensen wrote:
>> I'd like to convert a small database to UTF8 before it becomes too
>> large.  I'm running on 8.3.x on Windows.  It doesn't seem that pgAdmin
>> has any native way of doing this, what's the easiest way to go about
>> doing this?  Thanks!
>>
>> Mike
>>
>
> Dump/Backup the database , then create a new database using utf-8
> then restore the database.
>

This is what I'm trying to do, but it's a total nightmare..

First, I did a:

pg_dump -U root MyDB > c:\DB.dbs.out

which appears to have worked.. Then, I edited the file in Notepad and
saved it as UTF8 which also appears to have worked. Next, I created
the new DB using UTF8, and ran:

psql -U root MyDB2 < c:\DB.dbs.out

I get pages and pages of errors about foreign key restraint violations
and other stuff. Looking at the DB after, almost all tables are
empty. When I look at the DB.dbs.out file more carefully, the problem
is fairly obvious. It attempts to create all the tables in
alphabetical order. First, it inserts data into the "A" table which
has a FK restraint on the B table which isn't populated yet.
Obviously, this is going to cause problems. Perhaps when restoring a
DB you're supposed to drop all restraints first? I suppose I could
hack this into working eventually, but I was hoping there was an
easier way.. Thanks..

Mike