Re: [ADMIN] Schema comparisons - Mailing list pgsql-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@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
>