pg_dump --data-only problem with PgSQL 8.0
От | Jani Averbach |
---|---|
Тема | pg_dump --data-only problem with PgSQL 8.0 |
Дата | |
Msg-id | 20050124181656.GF23224@jaa.iki.fi обсуждение исходный текст |
Ответы |
Re: pg_dump --data-only problem with PgSQL 8.0
Re: pg_dump --data-only problem with PgSQL 8.0 |
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: