PostgreSQL Administration - How to compare the schemas ?

Author

How to compare the schemas ?

Milorad Poluga

2005-06-28, 3:23 am

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

KÖPFERL Robert

2005-06-28, 3:24 am

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
|

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

http://archives.postgresql.org

Peter Eisentraut

2005-06-28, 7:23 am

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

Ian FREISLICH

2005-06-28, 9:24 am

Peter Eisentraut wrote:
> Milorad Poluga wrote:
>
> 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,TE
XT) 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 majordomo@postgresql
.org)

Scott Marlowe

2005-06-28, 11:23 am

On Tue, 2005-06-28 at 08:32, Ian FREISLICH wrote:
> Peter Eisentraut wrote:
>
> 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,TE
XT) 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

Ian FREISLICH

2005-06-29, 3:23 am

Scott Marlowe wrote:
,v 1.[color=darkred]
>
> 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

Sponsored Links