SchemaSpy is a neat tool to produce visual diagrams for most relational databases.
Here's how to use it to generate schema relationship diagrams for PostgreSQL databases:
-
Download the jar file from here (the current version is v6.1.0)
-
Get the PostgreSQL JDBC driver (unless your installed version of java is really old, use the latest JDBC4 jar file)
-
Run the command against an existing database. For most databases, the schema (-s option) we are interested in is the public one:
java -jar schemaspy-6.1.0.jar -t pgsql \
-s public -db [db name] -u [db user] -p [password] \
-host localhost -o /tmp \
-dp /path/to/JDBC/driver/postgresql-42.2.12.jar
The above example sends all the output to the /tmp folder, but that can be changed as desired.
SchemaSpy produces several different diagrams, but the main summary diagram is in:
/tmp/diagrams/summary/relationships.real.large.png.
Hello,
I am getting below error on mac while running schemaspy,
1.0.jar started by root in /Users/shrikant/Downloads)
INFO - The following profiles are active: default
INFO - Started Main in 1.513 seconds (JVM running for 2.792)
INFO - Starting schema analysis
WARN - Connection Failure
Failed to connect to database URL [jdbc:postgresql://localhost/5432] FATAL: database "5432" does not exist
INFO - StackTraces have been omitted, use
-debug
when executing SchemaSpy to see themsh-3.2# java -jar schemaspy-6.1.0.jar -t pgsql -s public -db postgres -u postgres -p 5432 -host localhost -o /tmp -dp /Users/shrikant/Downloads/postgresql-42.2.14.jar
INFO - Starting Main v6.1.0 on Shrikants-MacBook-Pro.local with PID 8346 (/Users/shrikant/Downloads/schemaspy-6.1.0.jar started by root in /Users/shrikant/Downloads)
INFO - The following profiles are active: default
INFO - Started Main in 1.043 seconds (JVM running for 1.434)
INFO - Starting schema analysis
INFO - Connected to PostgreSQL - 11.5
INFO - Gathering schema details
Gathering schema details.........WARN - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
ERROR: column p.proisagg does not exist
Hint: Perhaps you meant to reference the column "p.prolang".
Position: 34
(0sec)
Connecting relationships.........(0sec)
Writing/graphing summary.INFO - Gathered schema details in 0 seconds
INFO - Writing/graphing summary
WARN - Failed to query Graphviz version using 'dot -V'
Cannot run program "dot": error=2, No such file or directory
INFO - Graphviz rendered set to ''
..ERROR - RelationShipDiagramError
Failed to produce diagram for: /tmp/diagrams/summary/relationships.implied.compact.dot
ERROR - RelationShipDiagramError
Failed to produce diagram for: /tmp/diagrams/summary/relationships.implied.large.dot
.ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/abc_1.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/onboard_cust.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/table_list.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/test1.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/test2.1degree.dot
Failed to generate Orphan diagram
.....(2sec)
Writing/diagramming detailsINFO - Completed summary in 2 seconds
INFO - Writing/diagramming details
Exception in thread "main" java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
Caused by: org.schemaspy.output.diagram.DiagramException: Failed to generate Table diagram
at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:71)
at org.schemaspy.output.html.mustache.diagrams.MustacheDiagramFactory.generateTableDiagram(MustacheDiagramFactory.java:44)
at org.schemaspy.output.html.mustache.diagrams.MustacheTableDiagramFactory.generateTableDiagrams(MustacheTableDiagramFactory.java:80)
at org.schemaspy.SchemaAnalyzer.generateHtmlDoc(SchemaAnalyzer.java:426)
at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:260)
at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:123)
at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:98)
at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:87)
at org.schemaspy.Main.main(Main.java:55)
... 8 more
Caused by: org.schemaspy.output.diagram.DiagramException: Dot missing or invalid version
at org.schemaspy.output.diagram.graphviz.GraphvizDot.generateDiagram(GraphvizDot.java:193)
at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:68)
... 16 more
Collapse