pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to compare the schemas ?
6+ messages / 5 participants
[nested] [flat]
* How to compare the schemas ?
@ 2005-06-23 14:11 Milorad Poluga <milorad.poluga@cores.co.yu>
2005-06-28 09:20 ` Re: How to compare the schemas ? Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Milorad Poluga @ 2005-06-23 14:11 UTC (permalink / raw)
To: pgsql-admin
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
---------------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to compare the schemas ?
2005-06-23 14:11 How to compare the schemas ? Milorad Poluga <milorad.poluga@cores.co.yu>
@ 2005-06-28 09:20 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-28 13:32 ` Re: How to compare the schemas ? Ian FREISLICH <if@hetzner.co.za>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2005-06-28 09:20 UTC (permalink / raw)
To: Milorad Poluga <milorad.poluga@cores.co.yu>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to compare the schemas ?
2005-06-23 14:11 How to compare the schemas ? Milorad Poluga <milorad.poluga@cores.co.yu>
2005-06-28 09:20 ` Re: How to compare the schemas ? Peter Eisentraut <peter_e@gmx.net>
@ 2005-06-28 13:32 ` Ian FREISLICH <if@hetzner.co.za>
2005-06-28 15:41 ` Re: How to compare the schemas ? Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ian FREISLICH @ 2005-06-28 13:32 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Milorad Poluga <milorad.poluga@cores.co.yu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to compare the schemas ?
2005-06-23 14:11 How to compare the schemas ? Milorad Poluga <milorad.poluga@cores.co.yu>
2005-06-28 09:20 ` Re: How to compare the schemas ? Peter Eisentraut <peter_e@gmx.net>
2005-06-28 13:32 ` Re: How to compare the schemas ? Ian FREISLICH <if@hetzner.co.za>
@ 2005-06-28 15:41 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-06-29 08:43 ` Re: How to compare the schemas ? Ian FREISLICH <if@hetzner.co.za>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2005-06-28 15:41 UTC (permalink / raw)
To: Ian FREISLICH <if@hetzner.co.za>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Milorad Poluga <milorad.poluga@cores.co.yu>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to compare the schemas ?
2005-06-23 14:11 How to compare the schemas ? Milorad Poluga <milorad.poluga@cores.co.yu>
2005-06-28 09:20 ` Re: How to compare the schemas ? Peter Eisentraut <peter_e@gmx.net>
2005-06-28 13:32 ` Re: How to compare the schemas ? Ian FREISLICH <if@hetzner.co.za>
2005-06-28 15:41 ` Re: How to compare the schemas ? Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-06-29 08:43 ` Ian FREISLICH <if@hetzner.co.za>
0 siblings, 0 replies; 6+ messages in thread
From: Ian FREISLICH @ 2005-06-29 08:43 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to compare the schemas ?
@ 2005-06-28 08:24 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 6+ messages in thread
From: KÖPFERL Robert @ 2005-06-28 08:24 UTC (permalink / raw)
To: pgsql-admin
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
|
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-29 08:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 14:11 How to compare the schemas ? Milorad Poluga <milorad.poluga@cores.co.yu>
2005-06-28 09:20 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-28 13:32 ` Ian FREISLICH <if@hetzner.co.za>
2005-06-28 15:41 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-06-29 08:43 ` Ian FREISLICH <if@hetzner.co.za>
2005-06-28 08:24 Re: How to compare the schemas ? KÖPFERL Robert <robert.koepferl@sonorys.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox