X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BACC3D1E3EB for ; Sat, 28 Feb 2004 17:57:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 87539-02 for ; Sat, 28 Feb 2004 13:57:54 -0400 (AST) Received: from mailgw1.syncinc.com (unknown [63.126.54.34]) by svr1.postgresql.org (Postfix) with ESMTP id 624BDD1E2E2 for ; Sat, 28 Feb 2004 13:57:53 -0400 (AST) Received: from ascwf14yqi3m2t (h000ae69b8247.ne.client2.attbi.com [24.62.105.235]) by mailgw1.syncinc.com (iPlanet Messaging Server 5.1 (built May 7 2001)) with ESMTPA id <0HTT00BCG370EC@mailgw1.syncinc.com> for pgsql-hackers@postgresql.org; Sat, 28 Feb 2004 12:57:01 -0500 (EST) Date: Sat, 28 Feb 2004 12:58:26 -0500 From: Michael Brusser Subject: Re: [ADMIN] Schema comparisons In-reply-to: <27965.1077982780@sss.pgh.pa.us> To: Tom Lane , Mark Lubratt Cc: pgsql-hackers@postgresql.org Reply-To: michael@synchronicity.com Message-id: MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7BIT Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/939 X-Sequence-Number: 50528 Interestingly I tried to address the same problem few days ago. I used pg_dump, grep, etc - in the end I got what I needed, but it was a cumbersome ordeal. I think ideally it would be great to have a utility that would give me a clean diff. between the schemas. Perhaps pg_dump could have a new arg to produce the output most suitable for this utility. Mike. > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tom Lane > Sent: Saturday, February 28, 2004 10:40 AM > To: Mark Lubratt > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] [ADMIN] Schema comparisons > > > Mark Lubratt writes: > > On Feb 27, 2004, at 10:28 PM, Tom Lane wrote: > >> Mark Lubratt writes: > >>> I've been trying to be careful, but I've gotten out of synch with > >>> whether or not I've applied the changes I've made to the development > >>> system to the production system. Is there a utility that will compare > >>> the tables, functions, trigger, views, etc. between two systems and > >>> flag the schema elements that aren't in synch between the two? > >> > >> Have you tried diffing pg_dump output? It's not the greatest tool but > >> it's helpful. > > > Yes, I did. It was quite cumbersome. Especially since the OIDs and > > TOC entry numbers didn't matchup; and, since those didn't always match, > > the order of objects wasn't quite the same either. So, diff was > > throwing a lot of false positives at me. > > Yeah. CVS-tip pg_dump doesn't show OIDs by default, to make it easier > to use for purposes like this. The ordering issue is the bigger problem > though. I presume that the object creation history is different in the > two databases and so pg_dump's habit of sorting by OID isn't helpful. > > It occurs to me that this could be solved now that we have > dependency-driven ordering in pg_dump. The ordering algorithm is > presently > * Order by object type, and by OID within types; > * Move objects as needed to honor dependencies. > Ordering by OID should no longer be needed for correctness, because > the second phase will take care of any dependency problems. We > could instead make the initial sort be by object name (within types). > This should ensure that the schema output is identical for logically > equivalent databases, even if their history is different. > > (When dumping from a pre-7.3 database, we'd have to stick to the OID > algorithm for lack of dependency info, but of course that case is > getting less interesting as time wears on.) > > Comments? Anyone see a reason not to do this? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >