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 3F2183A55FC for ; Mon, 24 Jan 2005 18:46:47 +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 84119-01 for ; Mon, 24 Jan 2005 18:46:42 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 756A83A5595 for ; Mon, 24 Jan 2005 18:46:43 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j0OIkd9K023558; Mon, 24 Jan 2005 13:46:39 -0500 (EST) To: Jani Averbach Cc: pgsql-admin@postgresql.org Subject: Re: pg_dump --data-only problem with PgSQL 8.0 In-reply-to: <20050124181656.GF23224@jaa.iki.fi> References: <20050124181656.GF23224@jaa.iki.fi> Comments: In-reply-to Jani Averbach message dated "Mon, 24 Jan 2005 11:16:56 -0700" Date: Mon, 24 Jan 2005 13:46:38 -0500 Message-ID: <23557.1106592398@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/256 X-Sequence-Number: 16115 Jani Averbach 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