202

Are there any free tools to generate table diagrams with PostgreSQL?

BinaryButterfly
  • 18,137
  • 13
  • 50
  • 91
Hannoun Yassir
  • 20,583
  • 23
  • 77
  • 112
  • 8
    Since this question got closed, I asked on the software recommendation SE: [Generate database table diagrams from a PostgreSQL database](http://softwarerecs.stackexchange.com/q/34552/903) – Franck Dernoncourt Jul 09 '16 at 21:46
  • 1
    Would be better and helpful to add a suggestion on what appropriate site under Stack Exchange communities to ask. – Jeb50 Jun 07 '21 at 18:47
  • In one word, [dbeaver](https://dbeaver.io/). Works out of the box. Also, you can refer to https://wiki.postgresql.org/wiki/Design_Tools – ivorynoise Apr 20 '22 at 12:58
  • One great solution would be using intellij idea / datagrip's: Right-click on the database's table name > Diagram > Show Visualization -> Some icon in the top for Export Diagram > Open in Web Editor > (Choose whichever you like more) – forhadmethun Oct 27 '22 at 23:42

7 Answers7

215

I love schemaspy for schema visualisations. Look at the sample output they provide, and drool. Note the tabs!

You'll need to download the JDBC driver here, then your command should look something like:

java -jar schemaspy-6.0.0-rc2.jar -t pgsql -db database_name -host myhost -u username -p password -o ./schemaspy -dp postgresql-9.3-1100.jdbc3.jar -s public -noads

Sometimes using options -port will not working if your database has diferrent port, so you have to add manual port after host parameter, for example:

java -jar schemaspy-6.0.0-rc2.jar -t pgsql -db database_name -host myhost:myport -u username -p password -o ./schemaspy -dp postgresql-9.3-1100.jdbc3.jar -s public -noads

You'll need to install graphviz as well if you want graphics (apt-get install graphviz for debian based distros).

sunapi386
  • 1,167
  • 14
  • 18
Jurgen
  • 2,175
  • 1
  • 11
  • 3
  • 8
    Actually it's **graphviz** :) – visionary Jan 04 '14 at 22:57
  • How do you use this with a service? – ComputerLocus Jul 07 '14 at 15:26
  • 2
    Schemaspy did this trick on first try. Layout of generate graph was great (even on our complex table set). When 100 tables reference the same table there will be overlapping lines; however the overall layout compensates for this and is perfectly legible/traceable. – pretzels1337 Jun 23 '15 at 21:49
  • I did not find this in apt-get. Supported anymore? – Léo Léopold Hertz 준영 Aug 11 '15 at 12:19
  • 5
    for `graphviz` just append the `-gv /path_to/graphviz` to the command – AzizSM Sep 18 '15 at 07:07
  • This tool is pretty good in conjunction with PostgreSQL! But it looks like it is not updated in quite a while. Is the project dead? – Douglas De Rizzo Meneghetti Jul 03 '16 at 01:10
  • On mac I had to add -dbThreads 1000 to avoid crashes due to limit in number of threads created – Tigran Dec 24 '16 at 01:58
  • 4
    On mac I had to add: -renderer :quartz to avoid this error: -Tcmapx' failed with return code 139 – slashdottir Aug 09 '17 at 22:15
  • 8
    To run this using Docker on the mac: ```docker run -v `pwd`/diagram:/output schemaspy/schemaspy:snapshot -t pgsql --port 5432 -u postgres -db -host docker.for.mac.localhost -p ``` – a-h Jan 25 '18 at 14:55
  • @slashdottir @a-h, it is the problem with graphviz dependencies. I just uninstalled graphviz and reinstalled with dependencies and all relationship diagrams were shown appropriately. `brew uninstall graphviz` and then `brew install graphviz --with-librsvg --with-pango` – yogi Jun 07 '18 at 10:14
  • this tool is solid gold, thanks. – Guillermo Tobar Jul 21 '18 at 02:22
  • In Windows doesn't found graphviz, the solution @azi proposed (-gv parameter) didn't work for me either. I had to add full path to graphviz (for me was "C:\Program Files (x86)\Graphviz2.38\bin") to "path" enviroment variable, close and open cmd and it works! – Eugenio Oct 01 '18 at 17:33
  • From version 6.1.0, GraphViz isn't required anymore. Check out: https://schemaspy.readthedocs.io/en/latest/installation.html#optional – FranzHuber23 Feb 24 '20 at 10:48
  • Working for me in MacOs out of the box. The output is very usefull in html format. However Graphviz seems not fully compatible in MacOs homebrew version. – JeanCarlos Chavarria Mar 03 '20 at 16:24
  • Caused by: org.schemaspy.output.diagram.DiagramException: 'dot -Tpng proveedor.1degree.dot -oproveedor.1degree.png -Tcmapx' failed with exception org.schemaspy.output.diagram.DiagramException: 'dot -Tpng proveedor.1degree.dot -oproveedor.1degree.png -Tcmapx' failed with return code 134 at org.schemaspy.output.diagram.graphviz.GraphvizDot.generateDiagram(GraphvizDot.java:237) at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:68) – JeanCarlos Chavarria Mar 03 '20 at 16:25
33

SchemaCrawler for PostgreSQL can generate database diagrams from the command line, with the help of GraphViz. You can use regular expressions to include and exclude tables and columns. It can also infer relationships between tables using common naming conventions, if not foreign keys are defined.

Sualeh Fatehi
  • 4,700
  • 2
  • 24
  • 28
19

Quick solution I found was inside the pgAdmin program for windows. Under Tools menu there is a "Query Tool". Inside the Query Tool there is a Graphical Query Builder that can quickly show the database tables details. Good for a basic view

Zane
  • 532
  • 1
  • 5
  • 15
  • 1
    I can't see how to automatically draw relationships using foreign keys in pgAdmin 1.20.0. Is it supported? – pyb Jul 15 '15 at 16:34
  • 1
    Nice tip, although it seems you have to draw all the FKs yourself using query joins. – Andrew Swan Aug 29 '16 at 01:57
  • 10
    This feature was [removed](https://www.enterprisedb.com/blog/pgadmin-4-elephant-nears-finish-line) in PgAdmin 4 in 2016. It might be reimplemented but has not been as of 2019. – Noumenon Oct 08 '19 at 13:59
  • See [this answer](https://stackoverflow.com/questions/3474389/generate-the-erd-for-an-existing-database#answer-66103248) for how to generate an ERD in pgAdmin 4 version 30 or higher. – Steve Chambers Mar 16 '22 at 11:18
14

Check the wiki: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
9

Just found http://www.sqlpower.ca/page/architect through the Postgres Community Guide mentioned by Frank Heikens. It can easily generate a diagram, and then lets you adjust the connectors!

Taylored Web Sites
  • 1,017
  • 1
  • 9
  • 15
7

PostgreSQL Autodoc has worked well for me. It is a simple command line tool. From the web page:

This is a utility which will run through PostgreSQL system tables and returns HTML, Dot, Dia and DocBook XML which describes the database.

Faheem Mitha
  • 6,096
  • 7
  • 48
  • 83
  • 3
    Link in answer is broken. Googled and found: https://github.com/cbbrowne/autodoc but seems to be unmaintained. – nelsonic Apr 06 '16 at 14:57
  • @nelsonic Indeed. Should I swap the links, or not? It doesn't seem to be by the same person who was maintaining it. Some kind of fork, it looks like. – Faheem Mitha Apr 06 '16 at 15:24
  • I've asked the author on GitHub if the project is being maintained: https://github.com/cbbrowne/autodoc/issues/2 – nelsonic Apr 06 '16 at 16:26
  • @nelsonic Yes, I saw the reply. I also posted https://bugs.debian.org/820208 – Faheem Mitha Apr 06 '16 at 20:15
  • 2
    You can create a png from the dot file as such: `dot -Tpng my_database.dot -o my_database.png`. – Kris Jan 10 '20 at 15:20
2

Inside Eclipse I've used the Clay plugin (ex Clay-Azurri). The free version allows to introspect ("reverse engineer") an existing DB schema (via JDBC) and make a diagram of some selected tables.

leonbloy
  • 73,180
  • 20
  • 142
  • 190