Thread: pg_dump --data-only problem with PgSQL 8.0
Hello, I have a following problem: In short: I can't reload pg_dump --data-only dump (ver. 8.0) back to the database, because the loading will violate ref. integrity. The long story: We have a PgSQL 7.2.5 database, and we like to bring only the data to the PgSQL 8.0 system. So I did following: 1) dump 7.2.5 database with: pg_dump \ "--data-only" \ "--column-inserts" \ "--use-set-session-authorization" \ old_db > old_db.data-only.dump 2) Create a schema on the new PgSQL system: CreateDBSchema.sh new_db 3) Load the old data to the new system: psql new_db < old_db.data-only.dump So far, so good, the loading succeeded. Now If continue, the following won't work: 4) Dump only data from just created 8.0 database: pg_dump \ "--data-only" \ "--column-inserts" \ "--use-set-session-authorization" \ new_db > new_db.data-only.dump 5) Re-Create the new_db: dropdb new_db; createdb new_db CreateDBSchema.sh new_db 6) Try to reload data which was dumped from new ver 8.0 database: psql new_db < new_db.data-only.dump This will fail with lots of these kinds of errors: ERROR: insert or update on table "mytable" violates foreign key constraint "mytable_myattr_fkey" However, If I take a data+schema dump after step #3, 7) Dump data and schema from new database: pg_dump "--use-set-session-authorization" \ new_db > new_db.dump 8) And load that, it will succeed: dropdb new_db; createdb new_db psql new_db < new_db.dump What I am doing wrong? Or have I found an ordering bug with pg_dump when you are doing "--data-only" dumps? These two databases are living in different machines, so there can't be any version mismatch between pg_dump, psql and databases. Thanks for any help, Jani -- Jani Averbach
On Mon, Jan 24, 2005 at 11:16:56AM -0700, Jani Averbach wrote: > What I am doing wrong? Or have I found an ordering bug with pg_dump > when you are doing "--data-only" dumps? In a simple test I see the same ordering (alphabetical?) whether I use --data-only or not. The schema+data dump succeeds because the dump doesn't add foreign key constraints until after all the data is loaded. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Jani Averbach <jaa@jaa.iki.fi> writes: > the following won't work: > 4) Dump only data from just created 8.0 database: > pg_dump \ > "--data-only" \ > "--column-inserts" \ > "--use-set-session-authorization" \ > new_db > new_db.data-only.dump > 5) Re-Create the new_db: > dropdb new_db; createdb new_db > CreateDBSchema.sh new_db > 6) Try to reload data which was dumped from new ver 8.0 database: > psql new_db < new_db.data-only.dump > This will fail with lots of these kinds of errors: > ERROR: insert or update on table "mytable" > violates foreign key constraint "mytable_myattr_fkey" Try it with --disable-triggers. > Or have I found an ordering bug with pg_dump > when you are doing "--data-only" dumps? No. pg_dump can't guarantee a safe order for loading data when there are pre-existing foreign key constraints in place (since the constraints could be circular, and it wouldn't necessarily know what they are anyway). So it doesn't try. You have to use --disable-triggers instead. regards, tom lane
On 2005-01-24 13:46-0500, Tom Lane wrote: > Jani Averbach <jaa@jaa.iki.fi> writes: > > > This will fail with lots of these kinds of errors: > > ERROR: insert or update on table "mytable" > > violates foreign key constraint "mytable_myattr_fkey" > > Try it with --disable-triggers. > Thanks a lot! That fixed it. > > Or have I found an ordering bug with pg_dump > > when you are doing "--data-only" dumps? > > No. pg_dump can't guarantee a safe order for loading data when there > are pre-existing foreign key constraints in place (since the constraints > could be circular, and it wouldn't necessarily know what they are anyway). > So it doesn't try. You have to use --disable-triggers instead. I was wondering because data-only load from a dump generated with 7.2.5 succeeded, but data only load with dump genererated from 8.0.0 didn't. Any way, it works now, thank you for your help! BR, Jani -- Jani Averbach