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

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

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

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

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: database, database diagram, entity, PostgreSQL, visio

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