Running SchemaSpy for Postgres

24 May 2016

This was harder than it should have been so I’m documenting it.

  1. Install Java®️
  2. Download SchemaSpy it’s a .jar file
  3. Download PostgreSQL JDBC Driver also a .jar
  4. Run java -jar <schemaspy jar> -t pgsql -db <database name> -u <user> -host localhost -o <output directory> -s "public" -dp <jdbc driver>

for a remote database (say on Amazon RDS) try

java -jar <schemaspy jar> -t pgsql -db <database name> -u <user> -p <password> -host foo.bar.region.rds.amazonaws.com -o <output directory> -s "public" -dp <jdbc driver>

If you get a complaint about the schema, it defaults to the username, pass in the correct schema name after -s.

now you can open <output directory>/index.html to get amazing info on your postgres database.

Dellstore2 DataModel

Above is the output for the dellstore2 datamodel. You can drill down to focus on a single table and its relationships. Let’s isolate ‘Orders’.

Dellstore2 Orders

You can test it out yourself dellstore SchemaSpy.

Hopefully someday I’ll be able to get a postgres schema to load in wwwsqldesigner but I couldn’t figure it out. This way I could send my friends a link to my data model.

For other applications, like weka you would want to place the jdbc driver in the classpath.



comments powered by Disqus