On Thu, Jul 1, 2010 at 9:31 AM, Mike Christensen <mike@xxxxxxxxxxxxx> wrote: > On Thu, Jul 1, 2010 at 10:22 AM, Justin Graf <justin@xxxxxxxxxxxx> 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". -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
