Thread: comparing database schema's

so i have two databases 'A' and 'B'. B is alot like A except i've
added alot of stuff to B like cascade on delete. Now 'A' is populated
with data and i want to add the triggers to it. I tried inserting the
contents of A into the schema of B, but it gave me alot of errors
because it was trying to insert values which have primary keys in
other tables before they had been inserted into the tables they are
referencing. so i decided that i would modify the columns of A
directly, and i was wondering if there was a way to compare the schema
of A to B once i had finished. just to make sure i hadnt missed
anything.

can anyone offer a way to compare db schema? or alternatively a better
way to do what i'm trying to do.

--
--------------------------------------------------------------------------
                                               | /"\
 john harrold                                  | \ / ASCII ribbon campaign
      jmh at member.fsf.org                    |  X  against HTML mail
           the most useful idiot               | / \
--------------------------------------------------------------------------
 What difference does it make to the dead, the orphans, and the homeless,
 whether the mad destruction is brought under the name of totalitarianism or
 the holy name of liberty and democracy?
 --Gandhi
--------------------------------------------------------------------------
gpg --keyserver keys.indymedia.org --recv-key F65A739E
--------------------------------------------------------------------------

--- John Harrold <jmh17@pitt.edu> wrote:
>
> so i have two databases 'A' and 'B'. B is alot like
> A except i've
> added alot of stuff to B like cascade on delete. Now
> 'A' is populated
> with data and i want to add the triggers to it. I
> tried inserting the
> contents of A into the schema of B, but it gave me
> alot of errors
> because it was trying to insert values which have
> primary keys in
> other tables before they had been inserted into the
> tables they are
> referencing. so i decided that i would modify the
> columns of A
> directly, and i was wondering if there was a way to
> compare the schema
> of A to B once i had finished. just to make sure i
> hadnt missed
> anything.
>
> can anyone offer a way to compare db schema? or
> alternatively a better
> way to do what i'm trying to do.
>
>  john harrold                                  | \ /

You can dump the schemas into separate files for
comparison. Assuming tables A and B are both in
database C:

pg_dump -s -t A C > schema_a
pg_dump -s -t B C > schema_b

I hope this helps,

Andrew

I wrote a utility once at a company where I worked previously that did this.
It did compared the meta data tables of the the two schemas. This was in
Oracle though. I think it took me less than a week to do. It might be a nice
utility to build for postgres.

It started with code as

SELECT TABLE_NAME
     FROM USER_TABLES
MINUS
SELECT TABLE_NAME
    FROM USER_TABLES@XYZ
/
And vice versa.

It then compared columns of common tables, the characteristics of the
columns, the indexes etc and used PL/SQL.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "John Harrold" <jmh17@pitt.edu>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, July 22, 2003 11:46 AM
Subject: [GENERAL] comparing database schema's

On Tue, 22 Jul 2003, John Harrold wrote:

> can anyone offer a way to compare db schema?

If you have access to a windows machine you could check PostgreSQL
Database Comparer at
http://www.ems-hitech.com/pgsqlutils/


I just started to test it a couple of days ago, but it does seem to show
differences.

If you do try it, be watchfull of the direction of the changes. I think
there is a bug and the changes are not done in the right direction (ie you
tell it to make database A the same as B, but instead it gives you the
code on how to make B the same as A).