Skip site navigation (1) Skip section navigation (2)

Re: [ADMIN] Schema comparisons

From: Michael Brusser <michael(at)synchronicity(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Mark Lubratt <mark(dot)lubratt(at)indeq(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [ADMIN] Schema comparisons
Date: 2004-02-28 17:58:26
Message-ID: DEEIJKLFNJGBEMBLBAHCIEBOEFAA.michael@synchronicity.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackers
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(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Saturday, February 28, 2004 10:40 AM
> To: Mark Lubratt
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] [ADMIN] Schema comparisons
>
>
> Mark Lubratt <mark(dot)lubratt(at)indeq(dot)com> writes:
> > On Feb 27, 2004, at 10:28 PM, Tom Lane wrote:
> >> Mark Lubratt <mark(dot)lubratt(at)indeq(dot)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
>



In response to

pgsql-hackers by date

Next:From: Richard HuxtonDate: 2004-02-28 17:59:50
Subject: Re: [ADMIN] Schema comparisons
Previous:From: Greg Sabino MullaneDate: 2004-02-28 17:05:16
Subject: Re: [HACKERS] Collaboration Tool Proposal

pgsql-admin by date

Next:From: Richard HuxtonDate: 2004-02-28 17:59:50
Subject: Re: [ADMIN] Schema comparisons
Previous:From: Tom LaneDate: 2004-02-28 15:39:40
Subject: Re: [ADMIN] Schema comparisons

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group