Postgres Pro
Downloads
Home   >   mailing lists

Thread: db schema diff

db schema diff

From
Igor Shevchenko
Date:
Hi!

Suppose I have "pg_dump -s" of two pg installs, one is "dev", another is
"production". Their schemas don't differ too much, and I want to get a "diff
-u"-like schema diff so I can quickly add missing/remove old
tables/sequences/etc to one or another (manually). Is there some quick tool
for doing this ?

There was a thread about it sometime in aug, 2002, but it ended without
producing anything useful.

--
Best regards,
Igor Shevchenko

Re: db schema diff

From
Date:
> Suppose I have "pg_dump -s" of two pg installs, one is "dev", another
> is  "production". Their schemas don't differ too much, and I want to
> get a "diff  -u"-like schema diff so I can quickly add missing/remove
> old
> tables/sequences/etc to one or another (manually). Is there some quick
> tool  for doing this ?
>
> There was a thread about it sometime in aug, 2002, but it ended without
>  producing anything useful.

This is the closest I get, but it is only marginally useful:

--File: pg_compare --------------------------------------
#!/bin/bash
# Script to dump a PostgreSQL database schema for two databases
# and compare them.
# Author: Berend M. Tober <btober@computer dot org>
# Date:   August 25, 2003

PG_DUMP=/usr/bin/pg_dump
DIFF=/usr/bin/diff
GREP=/bin/grep
CAT=/bin/cat

-- 5434 is the port on which DEV runs
-- 5433 is the port on which QAT runs

${PG_DUMP} -s -p 5432 mydb|${GREP} -v '^--'|${CAT} -s >5432.sql
${PG_DUMP} -s -p 5433 mydb|${GREP} -v '^--'|${CAT} -s >5433.sql
${PG_DUMP} -s -p 5434 mydb|${GREP} -v '^--'|${CAT} -s >5434.sql

${DIFF} 5432.sql 5433.sql > 5432-5433.diff
${DIFF} 5433.sql 5434.sql > 5433-5434.diff


~Berend Tober

Re: db schema diff

From
Igor Shevchenko
Date:
On Monday 12 April 2004 22:38, you wrote:
> ${PG_DUMP} -s -p 5432 mydb|${GREP} -v '^--'|${CAT} -s >5432.sql
> ${PG_DUMP} -s -p 5433 mydb|${GREP} -v '^--'|${CAT} -s >5433.sql
> ${PG_DUMP} -s -p 5434 mydb|${GREP} -v '^--'|${CAT} -s >5434.sql
>
> ${DIFF} 5432.sql 5433.sql > 5432-5433.diff
> ${DIFF} 5433.sql 5434.sql > 5433-5434.diff

I used to do exactly this but the pg_dump order is different on my two
servers, and this generates lots of false diffs.

Here's a link to my really simple script which does what I want:
http://carcass.ath.cx/diffpg.pl

It doesn't honor schema and object ownership.

--
Best regards,
Igor Shevchenko

Re: db schema diff

From
Tom Lane
Date:
Igor Shevchenko <igor@carcass.ath.cx> writes:
> On Monday 12 April 2004 22:38, you wrote:
>> ${PG_DUMP} -s -p 5432 mydb|${GREP} -v '^--'|${CAT} -s >5432.sql
>> ${PG_DUMP} -s -p 5433 mydb|${GREP} -v '^--'|${CAT} -s >5433.sql
>> ${PG_DUMP} -s -p 5434 mydb|${GREP} -v '^--'|${CAT} -s >5434.sql
>>
>> ${DIFF} 5432.sql 5433.sql > 5432-5433.diff
>> ${DIFF} 5433.sql 5434.sql > 5433-5434.diff

> I used to do exactly this but the pg_dump order is different on my two
> servers, and this generates lots of false diffs.

FWIW, CVS tip pg_dump has been modified to produce a consistent (and
safe) dump order, so its output should be a lot more useful for schema
diff'ing purposes than previous releases were.

It should work to build a current snapshot and use its pg_dump against
older servers, if you need a solution now.

            regards, tom lane

Re: db schema diff

From
Jeremiah Jahn
Date:
Although now consistent, is there a way to strip out the comments? since
they differ between dbs? Right now, I use grep, but I have this need to
live in a perfect world were everything is just a command line
option..:)



On Mon, 2004-04-12 at 16:57, Tom Lane wrote:
> Igor Shevchenko <igor@carcass.ath.cx> writes:
> > On Monday 12 April 2004 22:38, you wrote:
> >> ${PG_DUMP} -s -p 5432 mydb|${GREP} -v '^--'|${CAT} -s >5432.sql
> >> ${PG_DUMP} -s -p 5433 mydb|${GREP} -v '^--'|${CAT} -s >5433.sql
> >> ${PG_DUMP} -s -p 5434 mydb|${GREP} -v '^--'|${CAT} -s >5434.sql
> >>
> >> ${DIFF} 5432.sql 5433.sql > 5432-5433.diff
> >> ${DIFF} 5433.sql 5434.sql > 5433-5434.diff
>
> > I used to do exactly this but the pg_dump order is different on my two
> > servers, and this generates lots of false diffs.
>
> FWIW, CVS tip pg_dump has been modified to produce a consistent (and
> safe) dump order, so its output should be a lot more useful for schema
> diff'ing purposes than previous releases were.
>
> It should work to build a current snapshot and use its pg_dump against
> older servers, if you need a solution now.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Jeremiah Jahn <jeremiah@cs.earlham.edu>

Re: db schema diff

From
Tom Lane
Date:
Jeremiah Jahn <jeremiah@cs.earlham.edu> writes:
> Although now consistent, is there a way to strip out the comments? since
> they differ between dbs?

Not any more they don't ... unless you use the --verbose option,
pg_dump's comments should look the same too.

            regards, tom lane

Re: db schema diff

From
Jeremiah Jahn
Date:
by comments, I mean these:

--
-- TOC entry 16 (OID 166152808)
-- Name: user_credit_card; Type: TABLE; Schema: public; Owner: copa
--

these have really gone away in a new version, and if so, which one..?

On Wed, 2004-04-14 at 10:26, Tom Lane wrote:
> Jeremiah Jahn <jeremiah@cs.earlham.edu> writes:
> > Although now consistent, is there a way to strip out the comments? since
> > they differ between dbs?
>
> Not any more they don't ... unless you use the --verbose option,
> pg_dump's comments should look the same too.
>
>             regards, tom lane
--
Jeremiah Jahn <jeremiah@cs.earlham.edu>