Thread: Any tools to compare db's
Does anyone know of any good tools that will compare 2 db's and generate a complete listing of any object differences? Ineed both the tables, views, functions, etc. as well as the object permissions. I have tried EMS's PostgreSQL DB Compare, but it does not provide the permission comparison. Looking for any other toolsout there. Thanks for any help. Chris
while you weren't looking, Chris Hoover wrote: > Does anyone know of any good tools that will compare 2 db's and generate a complete listing of any object differences? Perhaps something like: $ pg_dump --schema-only db1 > db1.sql $ pg_dump --schema-only db2 > db2.sql $ diff db1.sql db2.sql would accomplish what you're after? It does specifically list GRANTs, among other things. /rls -- :wq
That really does not work. It will tell you line by line differences, but not what object it is. Also, it does not help to generate the sql to sync the db's. Chris Rosser Schwarz wrote: >while you weren't looking, Chris Hoover wrote: > > >>Does anyone know of any good tools that will compare 2 db's and generate a complete listing of any object differences? >> >> > >Perhaps something like: > >$ pg_dump --schema-only db1 > db1.sql >$ pg_dump --schema-only db2 > db2.sql >$ diff db1.sql db2.sql > >would accomplish what you're after? It does specifically list GRANTs, >among other things. > >/rls > > >
while you weren't looking, Chris Hoover wrote: > That really does not work. It will tell you line by line differences, > but not what object it is. Also, it does not help to generate the sql > to sync the db's. There are graphical diff tools that will show the relationships between the various blocks in two files. gtkdiff, for example, has generally been pretty good in my experience. You should also be able to tell diff how many lines of context you want to see. (Even that might not be sufficient, though. We have a few two hundred-odd column tables; allowing enough context to cover those cases, you might as well be reading the whole schema yourself and writing down the differences.) As for generating the SQL, you're asking for something one might expect from an ER tool; you might similarly expect to pay quite a bit for it. IIRC, DataArchitect (q.v., at thekompany.com) may have something to that effect in a forthcoming release, but I've never had much -- read, any -- success getting the app to work -- read, even connect. It's too bad, too; the tool looks pretty slick otherwise. If nothing else, you might be able to kludge together a fix between what works in EMS' product (which a colleague uses and swears by) to sync the relations, and saying pg_dump --schema-only database | grep GRANT - > grants.sql on the "correct" database. Then sync your permissions by running the grants script against the less correct one. There may also be additional steps; I don't know if EMS' tool will also sync users and groups, as just one quick example. Nothing I'm aware of will do it in a click or three, though. /rls -- :wq
Have you tried this one ? http://www.dbbalance.com/products.htm . I am also looking at database comparison tools and so far i could find only two for postgres one from EMS and this one. I havent tried this one yet though. Chris Hoover wrote: >Does anyone know of any good tools that will compare 2 db's and generate a complete listing of any object differences? I need both the tables, views, functions, etc. as well as the object permissions. > >I have tried EMS's PostgreSQL DB Compare, but it does not provide the permission comparison. Looking for any other toolsout there. > >Thanks for any help. > >Chris > > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > >
pgdiff should compare two PostgreSQL databases.
-Ton
On Tuesday 21 December 2004 12:57, Tony and Bryn Reina wrote: > http://gborg.postgresql.org/project/pgdiff/projdisplay.php > > pgdiff should compare two PostgreSQL databases. > I looked at this a while back and it seemed to compare the data within two identical databases, not compare the schema between two databases. As I recall, if you have different schema it just blows up. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
On Tuesday 21 December 2004 12:57, Tony and Bryn Reina wrote: > http://gborg.postgresql.org/project/pgdiff/projdisplay.php > > pgdiff should compare two PostgreSQL databases. > I looked at this a while back and it seemed to compare the data within two identical databases, not compare the schema between two databases. As I recall, if you have different schema it just blows up. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings