How to compare the schemas ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

How to compare the schemas ?

Milorad Poluga
Hi everyone,

I am looking for the best way to compare the schemas of two databases with the very similar structure.
One (certainly not the best options) is to do something like this:

pg_dump ... DB1  > PG_SCHEMA1
pg_dump ... DB2  > PG_SCHEMA2
diff  PG_SCHEMA1  PG_SCHEMA2  > differences.txt

kwrite differences.txt

Any suggestions or ideas on how to overcome this are welcome.

Thanks in advance,

Milorad Poluga                        
HK CORES Beograd, Makenzijeva 31
[hidden email]                      

--
---------------------------------------
Milorad Poluga                        
HK CORES Beograd, Makenzijeva 31
[hidden email]                      
+381-11-30-80-461              
---------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: How to compare the schemas ?

KÖPFERL Robert
diff speaks the wrong language to accomplish that, other elementry parts.

There exists a program named pgdiff on gborg.
On the other hand there's EMS database comparer http://www.sqlmanager.net/
 
|-----Original Message-----
|From: Milorad Poluga [mailto:[hidden email]]
|Sent: Donnerstag, 23. Juni 2005 16:11
|To: [hidden email]
|Subject: [ADMIN] How to compare the schemas ?
|
|
|Hi everyone,
|
|I am looking for the best way to compare the schemas of two
|databases with the very similar structure.
|One (certainly not the best options) is to do something like this:
|
|pg_dump ... DB1  > PG_SCHEMA1
|pg_dump ... DB2  > PG_SCHEMA2
|diff  PG_SCHEMA1  PG_SCHEMA2  > differences.txt
|
|kwrite differences.txt
|
|Any suggestions or ideas on how to overcome this are welcome.
|
|Thanks in advance,
|
|Milorad Poluga                        
|HK CORES Beograd, Makenzijeva 31
|[hidden email]                      
|
|--
|---------------------------------------
|Milorad Poluga                        
|HK CORES Beograd, Makenzijeva 31
|[hidden email]                      
|+381-11-30-80-461              
|---------------------------------------
|
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 4: Don't 'kill -9' the postmaster
|

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: How to compare the schemas ?

Peter Eisentraut-2
In reply to this post by Milorad Poluga
Milorad Poluga wrote:
> One (certainly not the best options) is to do something like this:
>
> pg_dump ... DB1  > PG_SCHEMA1
> pg_dump ... DB2  > PG_SCHEMA2
> diff  PG_SCHEMA1  PG_SCHEMA2  > differences.txt

What is wrong with that?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: How to compare the schemas ?

Ian FREISLICH
Peter Eisentraut wrote:
> Milorad Poluga wrote:
> > One (certainly not the best options) is to do something like this:
> >
> > pg_dump ... DB1 =A0> PG_SCHEMA1
> > pg_dump ... DB2 =A0> PG_SCHEMA2
> > diff =A0PG_SCHEMA1 =A0PG_SCHEMA2 =A0> differences.txt
>
> What is wrong with that?

All the extra TOC ID and comment stuff that pg_dump introduces.
Also, I think that the dump is sorted by creation order or some
other scheme, so even though the databases might be identical, the
diff output would be significant.

This is something that I have battled in the past.  The only solution
was to always update the database from a set of scripts, one for
each function and one for the tables.  These scripts kept in CVS
auto update the comment on each object:

COMMENT ON FUNCTION namedConfGen(TEXT,TEXT) IS '$Id: sproc.api.namedConfGen,v 1.
8 2004/05/07 08:02:55 ianf Exp $';

Now I can cvs diff using the version numbers.  Not ideal, but at
least I know exactly where I am.

Ian

--
Ian Freislich

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: How to compare the schemas ?

Scott Marlowe
On Tue, 2005-06-28 at 08:32, Ian FREISLICH wrote:

> Peter Eisentraut wrote:
> > Milorad Poluga wrote:
> > > One (certainly not the best options) is to do something like this:
> > >
> > > pg_dump ... DB1 =A0> PG_SCHEMA1
> > > pg_dump ... DB2 =A0> PG_SCHEMA2
> > > diff =A0PG_SCHEMA1 =A0PG_SCHEMA2 =A0> differences.txt
> >
> > What is wrong with that?
>
> All the extra TOC ID and comment stuff that pg_dump introduces.
> Also, I think that the dump is sorted by creation order or some
> other scheme, so even though the databases might be identical, the
> diff output would be significant.
>
> This is something that I have battled in the past.  The only solution
> was to always update the database from a set of scripts, one for
> each function and one for the tables.  These scripts kept in CVS
> auto update the comment on each object:
>
> COMMENT ON FUNCTION namedConfGen(TEXT,TEXT) IS '$Id: sproc.api.namedConfGen,v 1.
> 8 2004/05/07 08:02:55 ianf Exp $';
>
> Now I can cvs diff using the version numbers.  Not ideal, but at
> least I know exactly where I am.

I just pass a schema backup through grep or sed with this option:

grep -Pv "^--"

and get a pretty good idea of the differences.

Since our databases are created by scripts as well, they generally share
creation order and such, so any small difference from missing a script
on one or another environment shows up with this.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: How to compare the schemas ?

Ian FREISLICH
Scott Marlowe wrote:
> > COMMENT ON FUNCTION namedConfGen(TEXT,TEXT) IS '$Id: sproc.api.namedConfGen
,v 1.

> > 8 2004/05/07 08:02:55 ianf Exp $';
> >
> > Now I can cvs diff using the version numbers.  Not ideal, but at
> > least I know exactly where I am.
>
> I just pass a schema backup through grep or sed with this option:
>
> grep -Pv "^--"
>
> and get a pretty good idea of the differences.
>
> Since our databases are created by scripts as well, they generally share
> creation order and such, so any small difference from missing a script
> on one or another environment shows up with this.

Yes, I've done that too, but grepping for the comments which have
the CVS ID.  Both can be sorted so you get a reasonable diff.
However, if the creation order is different, then a straight diff
of the full schema dumps is useless.

I see that there is a way to dump a single table 'pg_dump -t table'
so you could dump a table at a time and diff the individual tables.

I see that there is no similar option for functions, triggers, types
and opperators (have I left anything out?).  Then these dumps could
be entirely scripted and usefull diff output could be obtained.

I guess I could write a perl function to dump the relevant bits of
the information schema in a way that will diff nicely.  I'll look
into that next time I need to do this.

Ian

--
Ian Freislich

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster