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
>
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