Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4C7366502EA for ; Wed, 27 Aug 2008 10:56:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 88469-05-4 for ; Wed, 27 Aug 2008 10:56:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id E344A650078 for ; Wed, 27 Aug 2008 10:54:06 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m7RDs3Id022709; Wed, 27 Aug 2008 09:54:03 -0400 (EDT) To: "Phoenix Kiula" cc: "PG-General Mailing List" Subject: Re: Dumping/Restoring with constraints? In-reply-to: References: Comments: In-reply-to "Phoenix Kiula" message dated "Wed, 27 Aug 2008 21:19:24 +0800" Date: Wed, 27 Aug 2008 09:54:02 -0400 Message-ID: <22708.1219845242@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/943 X-Sequence-Number: 136773 "Phoenix Kiula" writes: > Hello. I have a database dump. With data and schema, which includes > all the constraints and rules. But it seems the pg_dumpall command > does not copy data in such a way that foreign keys are satisfied upon > restoring. Because tables are inter-related, importing them keep > giving errors and eventually no data is imported. This shouldn't be possible in a regular dump, at least not with any remotely modern version of PG. However, if you are trying to restore a data-only dump into an already-created set of tables, it is possible because pg_dump doesn't know how to order the data loads in that case. (The problem may in fact be insoluble, since circular foreign key relationships are allowed.) The recommended solution is to use a regular schema-and-data dump. If you really have to separate schema and data, the --disable-triggers switch might help, though you open yourself to the possibility of loading inconsistent data that way. regards, tom lane