pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Brusser <michael@synchronicity.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Lubratt <mark.lubratt@indeq.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] Schema comparisons
Date: Sat, 28 Feb 2004 12:58:26 -0500
Message-ID: <DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com> (raw)
In-Reply-To: <27965.1077982780@sss.pgh.pa.us>

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 <mark.lubratt@indeq.com> writes:
> > On Feb 27, 2004, at 10:28 PM, Tom Lane wrote:
> >> Mark Lubratt <mark.lubratt@indeq.com> 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
>





view thread (12+ messages)  latest in thread

Message-ID: <DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com>
Permalink:  ../DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com/
Also on:    postgresql.org/message-id/DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: michael@synchronicity.com, tgl@sss.pgh.pa.us, mark.lubratt@indeq.com, pgsql-hackers@postgresql.org
  Subject: Re: [ADMIN] Schema comparisons
  In-Reply-To: <DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com>
  References: <27965.1077982780@sss.pgh.pa.us> <DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox