Обсуждение: 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 ---------------------------------------
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 |
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
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
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
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
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
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