Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 054609FA173 for ; Tue, 7 Nov 2006 13:25:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 43741-10 for ; Tue, 7 Nov 2006 17:25:24 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id E81129FA14D for ; Tue, 7 Nov 2006 13:25:23 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1079853ugf for ; Tue, 07 Nov 2006 09:25:22 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=ob9ebE77Cwg0irvjOngiOBnSBSpXbRZxD86eSiOscMwnDZogWDPScZbsKMU0H0Ow1L5VFV851eEZrIb1lDvQRnl6pWHdAHyy1CNRuTRwojWZv6b8SZ/6ymAsjBcFgpJds7Kxz4wKc9omxAminxmZ1s1BC49MVu8PMEhXDJlGGzk= Received: by 10.78.32.14 with SMTP id f14mr5176046huf.1162920322004; Tue, 07 Nov 2006 09:25:22 -0800 (PST) Received: by 10.78.184.19 with HTTP; Tue, 7 Nov 2006 09:25:21 -0800 (PST) Message-ID: Date: Tue, 7 Nov 2006 17:25:21 +0000 From: "Paolo Negri" To: pgsql-admin@postgresql.org Subject: pg_dump and foreign keys troubles MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/50 X-Sequence-Number: 23577 Hi I have a dump of a large DB executed with pg_dump (schema and data together). The original schema declares some foreign keys constaints. The problem is in the pg_dump all the foreign keys disappear from the tables because thay are rendered instead as CREATE CONSTRAINT TRIGGERS. My problem is that I have to load the dump (from a 7.3) in a brand new database (8.1) and then I need to do quite a few changes to the schema dropping some old foreign constraint and adding some different ones. Since the new datadabase restored from the dump doesn't know about the old foreign keys but has just a bunch of constraint trigger, it becomes really trichy to do this kind of job. How can I tell to pg_dump to dump the schema with foreign keys construct instead of creating constraint trigger? If it's impossible what is the common practice to work arount this problem? thanks Paolo