Re: [ADMIN] Schema comparisons

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
>