X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 693913A5435 for ; Mon, 24 Jan 2005 18:17:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79712-08 for ; Mon, 24 Jan 2005 18:16:59 +0000 (GMT) Received: from hal9000.jaa.iki.fi (gw.jaa.iki.fi [64.81.100.231]) by svr1.postgresql.org (Postfix) with ESMTP id CF0173A4D8F for ; Mon, 24 Jan 2005 18:16:59 +0000 (GMT) Received: by hal9000.jaa.iki.fi (Postfix, from userid 500) id 107833408F; Mon, 24 Jan 2005 11:16:56 -0700 (MST) Date: Mon, 24 Jan 2005 11:16:56 -0700 From: Jani Averbach To: pgsql-admin@postgresql.org Subject: pg_dump --data-only problem with PgSQL 8.0 Message-ID: <20050124181656.GF23224@jaa.iki.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/251 X-Sequence-Number: 16110 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