How to use SchemaSpy to generate the db schema diagram for a PostgreSQL database

SchemaSpy-HOWTO.md

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:

1. Download the jar file from here (the current version is v6.1.0)

2. Get the PostgreSQL JDBC driver (unless your installed version of java is really old, use the latest JDBC4 jar file)

3. 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.

@shrikantbhende the problem is clear, if you know how to read java stack traces.

In this particular case, the reason is:

 Caused by: org.schemaspy.output.diagram.DiagramException: Dot missing or invalid version

I.e., as the SchemaSpy documents note, you need to either install Graphviz or use vizjs in your command line.

Thank you!

I am getting below error, not sure what I am doing wrong.

D:>java -jar schemaSpy_5.0.0.jar -t pgsql -s public -host localhost -db postgres -u postgres -p "ratnagiri_2013" -o "C:\Users\ktawde\Documents" -dp postgresql-42.2.0.jar -gv "C:\Program Files (x86)\Graphviz2.38"
Using database properties:
[schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
11:48:11.118 WARNING: ConnectionFactoryImpl.log - SQLException occurred while connecting to localhost:5432
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:475)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:452)
at org.postgresql.Driver.connect(Driver.java:254)
at net.sourceforge.schemaspy.SchemaAnalyzer.getConnection(SchemaAnalyzer.java:582)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:157)
at net.sourceforge.schemaspy.Main.main(Main.java:42)

If this happens to you, try to explicitly define the port on the host argument (e.g., -host localhost:5432)
password authentication failed for user "postgres" can be misleading.
If this does not work, make sure you have postgres defined (you can do this with \du inside the psql console).
You should have at least one user with Superuser privileges (to create the postgres user -> if it doesn't exist).

@GuilhermeFernandes95 and @kalpeshtawde I also wonder if the quotes around the password could be the problem

@GuilhermeFernandes95 and @kalpeshtawde I also wonder if the quotes around the password could be the problem

@dpapathanasiou, haven't seen that :D It could easily be that.

My SchemaSpy output includes information about the tables and views in my PostGRES database, but the relationship diagram has only the tables and not the views. Can I make it include the views as well? Thanks.

Can someone please direct me to where I can find the difference between -t pgsql and -t pgsql11 DBTypes?
Thank you in advance.

Thank you so much!

Thank you so much! Super helpful.

Thank you! Is there a way to generate documentation on a database spanning multiple schemas?