Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Guys I need to compare 2 different databases running in PostgreSQL. I would like to use some tool to extract the diagram in a automatic way, Ive tried pgadmin3 and pgdesigner but they seem incompatible with the postgre I'm using. Which tool can I use to get this done?

Edit 1: By incompatible I mean they can't even open the databases I have as they are too new.

Edit 2: I can open both dumps, old and new version, in fact I do have they in .sql form I think both tools you provided will do the trick. I will test them at work tommorow and update here. Thanks a lot.

share|improve this question
    
Instead of asking for some tools that do something, please describe the actual problems you have using the tools you already know do the job. How exactly are they "incompatible"? What have you tried and where did you run into issues specifically? –  slhck Aug 21 '13 at 22:01
    
@slhck sorry I gtg I will update asap –  vfbsilva Aug 21 '13 at 22:07
    
So much missing. Pg versions concerned? What kind of "database" do you have - dumps? datadir copies? or a running DB? "seem incompatible" how - what does "can't open" the database even mean? exact error messages, versions, and step-by-step descriptions will be necessary to help you. –  Craig Ringer Aug 22 '13 at 0:04
    
the term you are looking for is reverse engineering. Turning a ERD into a database is reverse engineering. –  Journeyman Geek Aug 22 '13 at 6:21

1 Answer 1

up vote 1 down vote accepted

If you can't even "open" the databases you pretty much cannot compare them. There are tools that work on dumps but they need the dumps to be in the expected format, and the easiest way to do that is load the server and do a dump.

You must first be able to start a database server for each database. The only way to read a PostgreSQL database is via a PostgreSQL database server.

It isn't clear what form the databases are in, you didn't specify that.

If they're dumps, just install a new enough PostgreSQL for the dumps and restore them to the new install. Use a separate machine or just install the new Pg alongside the old one. You didn't mention your platform, OS/version, etc, so I can't help further there.

If they're copies of the PostgreSQL data directory its self and were taken correctly from a stopped server, you must get the same version (not newer, not older) on the same OS and architecture and start a server from the database. So if the database is from (say) 32-bit Windows PostgreSQL 9.2, you need 32-bit PostgreSQL 9.2 on Windows to load the database, nothing else will do. Start the server with pg_ctl.

Once you can connect to both servers you're in the position of being able to use existing tools to compare the two DBs. When it comes to mapping out DB structure I find SchemaSpy quite useful. For comparison consider apgdiff; this will work on dumps, but requires dumps to be in the right format.

share|improve this answer
    
seems the tools are not compatible with the last postgresql I did manage to conect to both databases. Schemaspy returns some kind of error: E=0I=0 I still did not manage to debug it. But yes it claims to do what I need. –  vfbsilva Aug 22 '13 at 18:49
    
@vfbsilva Well, if you're still stuck consider following this up on superuser.com with a detailed report that contains exact error messages and versions. –  Craig Ringer Aug 23 '13 at 0:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.