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

On Thu, Jul 1, 2010 at 9:31 AM, Mike Christensen <mike@kitchenpc.com> wrote:
> On Thu, Jul 1, 2010 at 10:22 AM, Justin Graf <justin@magwerks.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
>

Ok first off, my bad it seems the db_dump scripts are smart enough to
add constraints last..

Here's the actual errors I get while restoring, which seem to result
in several tables being empty.  Does this mean that the data in my
source database has somehow lost integrity, even though I have
constraints in place?

ERROR:  character 0x9d of encoding "WIN1252" has no equivalent in "UTF8"
CONTEXT:  COPY notifications, line 170
ERROR:  character 0x9d of encoding "WIN1252" has no equivalent in "UTF8"
CONTEXT:  COPY recipes, line 2
ERROR:  character 0x9d of encoding "WIN1252" has no equivalent in "UTF8"
CONTEXT:  COPY wallposts, line 13
ERROR:  insert or update on table "reciperatings" violates foreign key constrain
t "fk2f0492774b4d795a"
DETAIL:  Key (recipeid)=(1202a454-ca32-49e0-9f38-0d973d80b2a9) is not present in
 table "recipes".
ERROR:  insert or update on table "recipeingredients" violates foreign key const
raint "fk673433f4b4d795a"
DETAIL:  Key (recipeid)=(139ff23b-bead-48ac-b606-fc12467ff940) is not present in
 table "recipes".
ERROR:  insert or update on table "calendars" violates foreign key constraint "f
kb1f25cf24b4d795a"
DETAIL:  Key (recipeid)=(2f944c26-efd1-406c-bd27-0be760e09f19) is not present in
 table "recipes".
ERROR:  insert or update on table "recipetags" violates foreign key constraint "
fkeabd5f754b4d795a"
DETAIL:  Key (recipeid)=(f9ebec00-6ebd-459d-a093-060bc3a755af) is not present in
 table "recipes".
ERROR:  insert or update on table "recipecomments" violates foreign key constrai
nt "fkec92d3c74b4d795a"
DETAIL:  Key (recipeid)=(1af68f09-0cba-4fae-8a08-9e56a3e72676) is not present in
 table "recipes".
ERROR:  insert or update on table "favorites" violates foreign key constraint "f
kf92d78404b4d795a"
DETAIL:  Key (recipeid)=(86c9e722-3508-4910-8a3e-10a705271cbb) is not present in
 table "recipes".