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 1B295529D6 for ; Tue, 28 Jun 2005 12:41:43 -0300 (ADT) 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 57336-06 for ; Tue, 28 Jun 2005 15:41:39 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 370DA529C6 for ; Tue, 28 Jun 2005 12:41:35 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 28 Jun 2005 10:41:33 -0500 Subject: Re: How to compare the schemas ? From: Scott Marlowe To: Ian FREISLICH Cc: Peter Eisentraut , Milorad Poluga , pgsql-admin@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1119973293.8208.107.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 28 Jun 2005 10:41:33 -0500 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: 200506/264 X-Sequence-Number: 18019 On Tue, 2005-06-28 at 08:32, Ian FREISLICH wrote: > Peter Eisentraut wrote: > > Milorad Poluga wrote: > > > One (certainly not the best options) is to do something like this: > > > > > > pg_dump ... DB1 =A0> PG_SCHEMA1 > > > pg_dump ... DB2 =A0> PG_SCHEMA2 > > > diff =A0PG_SCHEMA1 =A0PG_SCHEMA2 =A0> differences.txt > > > > What is wrong with that? > > All the extra TOC ID and comment stuff that pg_dump introduces. > Also, I think that the dump is sorted by creation order or some > other scheme, so even though the databases might be identical, the > diff output would be significant. > > This is something that I have battled in the past. The only solution > was to always update the database from a set of scripts, one for > each function and one for the tables. These scripts kept in CVS > auto update the comment on each object: > > COMMENT ON FUNCTION namedConfGen(TEXT,TEXT) IS '$Id: sproc.api.namedConfGen,v 1. > 8 2004/05/07 08:02:55 ianf Exp $'; > > Now I can cvs diff using the version numbers. Not ideal, but at > least I know exactly where I am. I just pass a schema backup through grep or sed with this option: grep -Pv "^--" and get a pretty good idea of the differences. Since our databases are created by scripts as well, they generally share creation order and such, so any small difference from missing a script on one or another environment shows up with this.