Обсуждение: How to compare the schemas ?

Поиск
Список
Период
Сортировка

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
milorad.poluga@cores.co.yu                      

--
---------------------------------------
Milorad Poluga                        
HK CORES Beograd, Makenzijeva 31
milorad.poluga@cores.co.yu                      
+381-11-30-80-461              
---------------------------------------


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:milorad.poluga@cores.co.yu]
|Sent: Donnerstag, 23. Juni 2005 16:11
|To: pgsql-admin@postgresql.org
|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
|milorad.poluga@cores.co.yu                      
|
|--
|---------------------------------------
|Milorad Poluga                        
|HK CORES Beograd, Makenzijeva 31
|milorad.poluga@cores.co.yu                      
|+381-11-30-80-461              
|---------------------------------------
|
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 4: Don't 'kill -9' the postmaster
|

Re: How to compare the schemas ?

От
Peter Eisentraut
Дата:
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


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

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 majordomo@postgresql.org


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

Re: How to compare the schemas ?

От
Peter Eisentraut
Дата:
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


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 majordomo@postgresql.org