Creating a PostgreSQL Database Diagram

Not for the first time, I was trying this morning to create a database diagram from a PostgreSQL database on my Windows laptop. Unfortunately I was unable to find anything useful online, so after succeeding in what I wanted to do, I decided to share it with the world on this blog😉 Especially since it was remarkably easy to achieve.
So below you can find a description on how to create a database diagram from an existing PostgreSQL database in MS Visio 2007 in 3 simple steps.

1) Installing the PostgreSQL ODBC driver : psqlODBC

Before you can connect to your PostgreSQL database from Visio, you need to install the PostgreSQL ODBC-driver. Simply download the latest psqlODBC driver from http://www.postgresql.org/ftp/odbc/versions/msi/ and run the installer.

2) Creating a User DSN

Then you will need to create a new User DSN (Data Source Name) in Windows to refer to in Visio. Go to Control Panel -> Administrative tools -> Data sources (ODBC) and add a new User DSN.

Creating a PostgreSQL User DSN

Creating a PostgreSQL User DSN


User DSN parameters

User DSN parameters


After filling in the correct parameters I suggest you click the test-button to check everything is OK. If so, you can proceed to the last step.

3) Configuring MS Visio

Open Visio2007, create a new ‘Database Model Diagram’ and choose “Reverse Engineer …” from the ‘Database menu’. Select the ODBC Generic Driver …

Visio Generic ODBC Driver

Visio Generic ODBC Driver



Then click “Setup …” to get the dialog in which you need to select ‘PostgreSQL Unicode’ again and of course the User DSN you just created :
Visio ODBC Setup

Visio ODBC Setup



Click OK and Next to continue and after filling in the correct username and password, you will be given a few straightforward options for the diagram creation. Your diagram should then appear after only a few moments.

Diagram example

Diagram example

That’s it, you can add some Visio markup as you like, for instance by colouring some important tables, like I did in the example. Good luck !

Explore posts in the same categories: Development

Tags: , , , ,

You can comment below, or link to this permanent URL from your own site.

12 Comments on “Creating a PostgreSQL Database Diagram”

  1. Bonnie Says:

    Thanks for sharing, Michael, can one achieve this in Visio 2003? for PostgreSQL 8.3?

  2. sjarel Says:

    I never tried it, but I suppose it should work.

  3. Bonnie Says:

    Hi Michael, to answer you and everyone else, yes, one can use Visio 2003 for PostgreSQL 8.3. Thanks for sharing again!

  4. sjarel Says:

    I’m glad I could help. My name is not Michael though😉

  5. Bonnie Says:

    Oh sorry, sjarel, I was looking at the blog theme author when I typed the comments. My apologies!

  6. steve Says:

    excellent! Thanks!

  7. B Says:

    Visio 2003 lost foreign key infomraton when asked to reverse engineer a PostgreSQL 8.3 database. See

    http://archives.postgresql.org/pgsql-odbc/2008-03/msg00027.php

    Bonnie, please tell us how you got Visio 2003 working on PostgreSQL 8.3.

  8. Orlando Says:

    Thank you for the great information. One issue I had was how to connect to an instance of Postgresql from a 64 bits system on another machine. Just in case anyone else might need the info:
    Information;
    http://forum.manifold.net/forum/t68807.16

    64-bit PostgreSQL ODBC Drivers;
    http://69.17.46.171/forum/t65322

    http://forum.manifold.net/Attachments/2B/65323/PostgreSQL%208.3.1%2064-bit%20ODBC%20drivers.zip

    ftp://forum.manifold.net/attachments/2B/65323/PostgreSQL%208.3.1%2064-bit%20ODBC%20drivers.zip

    Hope this might help someone.

    Thank again, just tried it on a 64-bit Vista machine and now I have an ER diagram in few seconds😉

    Orlando

  9. dzieber Says:

    Thank you! This just saved me a whole lotta pain.

  10. easy Says:

    Great tutorial!!!! Thanks!

  11. renz Says:

    Confirming that this works in Windows 7 Visio 2007

  12. Wrbhhh Says:

    Thank you so very very much. You’ve saved me a lot of time.🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: