By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,748 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,748 IT Pros & Developers. It's quick & easy.

db schema diff

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies

P: n/a
> 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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

P: n/a
Igor Shevchenko <ig**@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 ma*******@postgresql.org

Nov 23 '05 #4

P: n/a
Jeremiah Jahn <je******@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5

P: n/a
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 <ig**@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 ma*******@postgresql.org

--
Jeremiah Jahn <je******@cs.earlham.edu>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

P: n/a
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 <je******@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 <je******@cs.earlham.edu>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.